Transaction Management

JDBC supports database transaction management. Transactions provide a way to group SQL statements so that they are treated as a whole: Either all statements in the group are executed or no statements are executed. All statements within a transaction are treated as a work unit. Transactions are thus useful to guarantee, among other things, data consistency. Transactions’ well-known properties are the ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means that several data updates performed within a transaction will either all be performed as a whole or simply not done. Data is kept consistent by ensuring that if any of the individual data updates fails, all the other updates will fail as well. Isolation means that data being updated won’t be affected by other transactions by means of locks while the update is running. Finally, durability indicates that after a transaction has succeeded, all its updates are persistent and won’t be undone within the scope of that transaction.

Completing a transaction is called committing the transaction, while aborting it is called rolling back the transaction. A rollback undoes the whole transaction. A transaction’s boundaries are the beginning of its block and the commit or rollback. After a commit has been issued, the transaction cannot be rolled back. Note that some DBMSs support nested transactions as well as intermediate markers within a transaction to indicate a point to which it can be rolled back.

Transaction modes

Two transaction modes are usually supported by commercial DBMSs: the unchained mode and the ANSI-compatible chained mode, which are described in the following. Check your DBMS’s documentation to determine which is the default.

• The unchained mode requires explicit statements to identify the beginning of a transaction block and its end, which is always either a commit or rollback statement. The transaction block can be composed of any SQL statements.

• The chained mode doesn’t require explicit statements to delimit the transaction statements because it implicitly begins a transaction before any SQL statement that retrieves or modifies data. The transaction must still be explicitly ended with a transaction commit or rollback.

Caution 

Be aware that stored procedures that use the unchained transaction mode may be incompatible with other chained mode transactions.

Transaction isolation levels

ANSI defines three standard levels of transaction isolation, which are described in the following list. Transaction isolation makes sense when concurrent transactions execute simultaneously. The ANSI specification defines restrictions on the kinds of action permitted in concurrent transactions so as to prevent dirty reads, nonrepeatable reads, and phantoms.

• Level 1: No dirty reads. Dirty reads occur when a transaction updates a row and then a second transaction reads that row before the first transaction commits. If the first transaction rolls back the change, the information read by the second transaction becomes invalid.

• Level 2: No nonrepeatable reads. Nonrepeatable reads occur when a transaction reads a row and then another transaction updates the same row. If the second transaction commits, subsequent reads by the first transaction get different values than the original read.

• Level 3: No phantoms. Phantoms occur when a transaction reads a set of rows that satisfy a search condition and then another transaction updates, inserts, or deletes one or more rows that satisfy the first transaction’s search condition. In this case, if the first transaction performs subsequent reads with the same search condition, it reads a different set of rows.

The higher levels include restrictions imposed by all the lower levels. In practice, compatibility with all the transaction isolation levels is achieved using locking techniques. Check your database documentation for information on these techniques and see how they can affect performances in a multiuser environment. As a general rule, the higher the transaction isolation level, the broader locks are held.

Managing transactions with JDBC

JDBC always opens connections in autocommit mode. This opening mode means that each statement is executed as a separate transaction without needing to supply commit or rollback commands. In this default mode, it isn’t possible to perform rollbacks.

JDBC provides methods to turn off autocommit mode, set the transaction isolation level, and commit or rollback transactions. JDBC transactions begin as soon as the autocommit mode is disabled. In this case, an implicit transaction is associated with the connection, and it is completed or aborted with commit and rollback methods. The commit or rollback starts a new implicit transaction. The commit and rollback make JDBC close all PreparedStatements, CallableStatements, and ResultSets opened during the transaction. Simple statement objects stay open. This is the default behavior, but it can be disabled.

Note 

Note that some database engines prevent deadlocks from occurring when two transactions are blocking each other.

The JDBC methods to manage transactions are the following:

Connection’s Transaction Management Methods

void setTransactionIsolation(int isolationlevel); int getTransactionIsolation(); void setAutoCommit(boolean autocommit); boolean getAutoCommit(); void commit(); void rollback(); void setAutoClose(boolean autoClose); boolean getAutoClose();

The method in the first line of the preceding sets the transaction isolation level. The possible JDBC transaction isolation levels are the following:

• TRANSACTION_READ_UNCOMMITTED: Dirty reads are allowed.

• TRANSACTION_READ_COMMITTED: Reads on a row are blocked until the transaction is committed.

• TRANSACTION_REPEATABLE_READ: Repeated reads on a row return the originally read data, regardless of any updates by other users prior to commitment of the transaction.

• TRANSACTION_SERIALIZABLE: All reads are disallowed until the transaction is committed.

• TRANSACTION_NONE: Transactions aren’t supported. This method cannot be called while in the middle of a transaction.

int getTransactionIsolation(); returns the current transaction isolation levels. A value of zero means that transactions aren’t supported.

In the third line of the preceding, void setAutoCommit(boolean autocommit);, the method setAutoCommit(false) implicitly begins a new transaction. Either commit() or rollback() must be used to terminate the transaction.

The method in the line boolean getAutoCommit(); returns the current autocommit state. false means that user transactions are in use.

The method in the line void commit(); completes the transaction. All changes made since the previous transaction termination (committed or rolled back) are made permanent and all transaction locks are released.

In the line void rollback();, all changes made since the previous transaction termination (committed or rolled back) are dropped. This method undoes the current transaction statements, and all transaction locks are released.

With the line void setAutoClose(boolean autoClose);, when the connection is in autoclose mode, all its PreparedStatements, CallableStatements, and ResultSets are closed when the transaction is committed or rolled back. This is the default behavior, but it can be disabled by passing false as a parameter. Some databases allow these objects to remain open across commits, whereas other databases close them.

The method in the line boolean getAutoClose(); returns the current autoclose state for this connection.

Listing 7-5 provides an example of a transaction where two SQL UPDATE statements are issued. In this example, you want to ensure that both updates are executed and that dirty reads, nonrepeatable reads, and phantom reads aren’t possible for other transactions that may happen at the same time.

Listing 7-5: An Example of SQL DELETEs Grouped in a Transaction

// transactions import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                   "javauser", "hotjava");                           Statement firstStmt =                                   myConnection.createStatement();                           Statement secondStmt =                                   myConnection.createStatement();                           myConnection.setTransactionIsolation(                                   Connection.TRANSACTION_SERIALIZABLE);                           myConnection.setAutoCommit(false);                           firstStmt.executeUpdate(                                   "DELETE emp_messages WHERE id IN                                   (SELECT id FROM employees WHERE name =                                   ‘Jones’)");                           firstStmt.close();                           secondStmt.executeUpdate(                                   "DELETE employees WHERE name = ‘Jones’");                           secondStmt.close();                           myConnection.commit();                           myConnection.setTransactionIsolation(                                   Connection.TRANSACTION_NONE);                           myConnection.close();                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } } 

Transaction savepoints

You can use transaction savepoints to mark intermediate steps within a transaction. Using savepoints, you can roll back small parts of a bigger transaction without affecting the whole transaction. A rollback to a savepoint won’t undo the work done prior to the position of the savepoint. This technique is possible thanks to the three following methods of java.sql.Connection.

Connection’s Methods for Savepoint Support

Savepoint setSavepoint(String savepoint); void rollback(Savepoint savepoint); void releaseSavepoint(Savepoint savepoint);

The first line of the preceding is used to set the savepoint. It can be used more than once if needed, which is why savepoints have a name. You can define several savepoints within a single transaction.

void rollback(Savepoint savepoint) is used to roll back all statements performed after the savepoint that is passed as a parameter.

void releaseSavepoint(Savepoint savepoint) is used to release a previously set savepoint.

Note that a normal rollback performs a rollback of the whole transaction. All savepoints are released when the transaction terminates by either commit or rollback.

The example in Listing 7-6 illustrates the savepoint mechanism.

Listing 7-6: Using Transaction Savepoints

// transactions savepoints import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url,                                   "javauser", "hotjava");                           Statement firstStmt = myConnection.createStatement();                           myConnection.setTransactionIsolation(                                   Connection.TRANSACTION_SERIALIZABLE);                           myConnection.setAutoCommit(false);                           firstStmt.executeUpdate(                                   "DELETE emp_messages WHERE id IN                                   (SELECT id FROM employees WHERE name =                                    ‘Jones’)");                           firstStmt.close();                           Savepoint savepoint =                                    myConnection.setSavepoint("ALL_MSG_DELETED ");                           Statement secondStmt = myConnection.createStatement();                           secondStmt.executeUpdate(                                   "DELETE employees WHERE id NOT IN (SELECT                                    DISTINCT id FROM emp_messages)");                           secondStmt.close();                           // oops... we deleted too many employees!                           myConnection.rollback(savepoint);                           Statement thirdStmt = myConnection.createStatement();                           thirdStmt.executeUpdate(                                   "DELETE employees WHERE name = ‘Jones’");                           thirdStmt.close();                           myConnection.commit();                           myConnection.setTransactionIsolation(                                   Connection.TRANSACTION_NONE);                           myConnection.close();                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } }

Distributed transactions

Sometimes transactions involve database operations on several connections to one or more data sources. In this case, a mechanism called a two-phase commit is needed to ensure the ACID properties of the transaction. This mechanism is provided by a transaction coordinator, or transaction manager. Different connections to data sources identify transaction participants, or resources, to the coordinator. The two- phase commit protocol ensures that a transaction can be either committed or roll backed for every single transaction participant. Before committing a distributed transaction, a transaction manager requests every participant to indicate if the part of the transaction that it must manage can be committed. This is called the Prepare step. If all participants can commit, the whole transaction can therefore commit. If any single resource must roll back, the whole transaction rolls back.

Communication between the transaction manager and the different resources is standardized by the X/Open XA protocol. JDBC supports XA through the javax.sql.XAConnection and javax.sql.XADataSource objects, which are provided in the javax.sql extension package to JDBC. Resources that are participating in the transaction must be registered with the transaction manager with javax.transaction.xa.XAResource interfaces. From a JDBC client application perspective, all participating XADataSource instances will be accessed through the javax.sql.DataSource interface that provides getConnection() methods. Those return the usual java.sql.Connection instances.

Listing 7-7 provides an example of how to deal with distributed transactions from within client code such as a servlet in an application server.

Listing 7-7: Distributed Transactions

// Obtain the context object from the environment we are running in Context ic = new InitialContext("java:comp/env"); // Obtain a handle on a user transaction from the environment we are running in UserTransaction ut = (UserTransaction) ic.lookup("java:comp/UserTransaction"); try {          ut.begin();          DataSource db1 = (DataSource) ic.lookup("OrdersDB");          DataSource db2 = (DataSource) ic.lookup("InventoryDB");          Connection con1 = db1.getConnection();          Connection con2 = db2.getConnection();          // perform transactional work here          // ...          ut.commit(); } catch (RollbackException ex) {          // the transaction has been implicitly or explicitly rolled backed          // ... }