Transaction savepoints (JDBC 3.0)

Transaction savepoints (JDBC 3.0)

18 April 2008

JDBC 2 provides complete transaction rollback control over transaction. But if you want to rollback to a point, you cannot do so. JDBC 3.0 introdices savepoints which makes this possible.

JDBC 3.0 provides more control over transactions using savepoints. The Savepoint interface allows us to partition a transaction into logical breakpoints. This in turn provides control over how much of the transaction we wish to rolled back.

Lets see how to do this. Review the following example:

conn.setAutoCommit(false);
// Set a conservative transaction isolation level.
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate(    "INSERT INTO authors " + 
                "(first_name, last_name) VALUES " +
                "('Lewis', 'Carroll')");
// Set a named savepoint.
Savepoint svpt = conn.setSavepoint("NewAuthor");
// ...
 
rows = stmt.executeUpdate(    "UPDATE authors set type = 'fiction' " +
                "WHERE last_name = 'Carroll'");
 
// ...
conn.rollback(svpt);
// ...
// The author has been added, but not updated.
conn.commit();