Understanding Transactions
Transactions are groups of operations that are combined into logical units of work. They are used to control and maintain the consistency and integrity of each action in a transaction, despite errors that might occur in the system.
With the Microsoft SQL Server 2005 JDBC Driver, transactions can be either local or distributed. Transactions can also use isolation levels. For more information about the isolation levels supported by the JDBC driver, see Understanding Isolation Levels.
Using Local Transactions
A transaction is considered to be local when it is a single-phase transaction, and it is handled by the database directly. The JDBC driver supports local transactions by using various methods of the SQLServerConnection class, including setAutoCommit, commit, and rollback. Local transactions are typically managed explicitly by the application or automatically by the J2EE application server.
The following example performs a local transaction that consists of two separate statements in thetryblock. The statements are run against the Production.ScrapReason table in the AdventureWorks SQL Server 2005 sample database, and they are committed if no exceptions are thrown. The code in thecatchblock rolls back the transaction if an exception is thrown.
public static void executeTransaction(Connection con) {
try {
//Switch to manual transaction mode by setting
//autocommit to false. Note that this starts the first
//manual transaction.
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')");
stmt.executeUpdate("INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')");
con.commit(); //This commits the transaction and starts a new one.
stmt.close(); //This turns off the transaction.
System.out.println("Transaction succeeded. Both records were written to the database.");
}
catch (SQLException ex) {
ex.printStackTrace();
try {
con.rollback();
System.out.println("Transaction failed. No records were written to the database.");
}
catch (SQLException se) {
se.printStackTrace();
}
}
}
Using Distributed Transactions
A distributed transaction updates data on two or more networked databases while retaining the important atomic, consistent, isolated, and durable (ACID) properties of transaction processing. Distributed transaction support was added to the JDBC API in the JDBC 2.0 Optional API specification. The management of distributed transactions is typically performed automatically by the Java Transaction Service (JTS) transaction manager in a J2EE application server environment. However, the Microsoft SQL Server 2005 JDBC Driver supports distributed transactions under any Java Transaction API (JTA) compliant transaction manager.
The JDBC driver seamlessly integrates with Microsoft Distributed Transaction Coordinator (MS DTC) to provide true distributed transaction support with SQL Server 2000 and SQL Server 2005. MS DTC is a distributed transaction facility provided by Microsoft for Microsoft Windows systems. MS DTC uses proven transaction processing technology from Microsoft to support XA features such as the complete two-phase distributed commit protocol and the recovery of distributed transactions.
For more information about how to use distributed transactions, see Understanding XA Transactions.