Using savepoints
Savepoints offer a mechanism to roll back portions of transactions. Within SQL Server, you can create a savepoint by using the SAVE TRANSACTION savepoint_name statement. Later, you run a ROLLBACK TRANSACTION savepoint_name statement to roll back to the savepoint instead of rolling back to the start of the transaction.
Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.
The Microsoft JDBC Driver for SQL Server supports the use of savepoints through the setSavepoint method of the SQLServerConnection class. By using the setSavepoint method, you can create a named or unnamed savepoint within the current transaction, and the method will return a SQLServerSavepoint object. Multiple savepoints can be created within a transaction. To roll back a transaction to a given savepoint, you can pass the SQLServerSavepoint object to the rollback (java.sql.Savepoint) method.
In the following example, a savepoint is used while performing a local transaction consisting of two separate statements in the try block. The statements are run against the Production.ScrapReason table in the AdventureWorks2022 sample database, and a savepoint is used to roll back the second statement. This results in only the first statement being committed to the database.
public static void executeTransaction(Connection con) {
try(Statement stmt = con.createStatement();) {
con.setAutoCommit(false);
stmt.executeUpdate("INSERT INTO Production.ScrapReason(Name) VALUES('Correct width')");
Savepoint save = con.setSavepoint();
stmt.executeUpdate("INSERT INTO Production.ScrapReason(Name) VALUES('Wrong width')");
con.rollback(save);
con.commit();
System.out.println("Transaction succeeded.");
}
catch (SQLException ex) {
ex.printStackTrace();
try {
System.out.println("Transaction failed.");
con.rollback();
}
catch (SQLException se) {
se.printStackTrace();
}
}
}