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 JDBC Driver for SQL Server, 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.
Applications should control transactions by either using Transact-SQL statements or the methods provided by the JDBC driver, but not both. Using both Transact-SQL statements and JDBC API methods on the same transaction might lead to problems, such as a transaction cannot be committed when expected, a transaction is committed or rolled back and a new one starts unexpectedly, or "Failed to resume the transaction" exceptions.
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 Java Platform, Enterprise Edition (Java EE) application server.
The following example performs a local transaction that consists of two separate statements in the try block. The statements are run against the Production.ScrapReason table in the AdventureWorks2022 sample database, and they are committed if no exceptions are thrown. The code in the catch block 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 {
System.out.println("Transaction failed.");
con.rollback();
}
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 Java EE application server environment. However, the Microsoft JDBC Driver for SQL Server 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. 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.