Java Enterprise in a Nutshell (In a Nutshell (OReilly))

8.9. Transactions

A transaction is a group of several operations that must behave atomically (i.e., as if they are a single, indivisible operation). With regard to databases, transactions allow you to combine one or more database actions into a single atomic unit. If you have an application that needs to execute multiple SQL statements to fulfill one goal (say an inventory management system that needs to move items from an INVENTORY table to a SHIPPING table), you probably want to use JDBC's transaction services to accomplish the goal.

This section gives a brief overview of transactions with respect to relational databases and JDBC. For broader coverage of transactions in the general J2EE environment, see Chapter 16.

Working with a transaction involves the following steps: start the transaction, perform its component operations, and then either commit the transaction if all the component operations succeed or roll it back if one of the operations fails. The ability to roll back a transaction is the key feature. This means that if any one SQL statement fails, the entire operation fails, and it is as though none of the component operations took place. Therefore, it is impossible to end up with a situation in which, for example, the INVENTORY table has been debited, but the SHIPPING table has not been credited.

Another issue with transactions and databases concerns changes to the database becoming visible to the rest of the system. Transactions can operate at varying levels of isolation from the rest of the database. At the most isolated level, the results of all the component SQL statements become visible to the rest of the system only when the transaction is committed. In other words, nobody sees the reduced inventory before the shipping data is updated.

The Connection object in JDBC is responsible for transaction management. With JDBC, you are always using transactions in some form. By default, a new connection starts out in transaction autocommit mode, which means that every SQL statement is executed as an individual transaction that is immediately committed to the database.

To perform a transaction that uses multiple statements, you have to call the setAutoCommit( ) method with a false argument. (You can check the status of autocommit with the getAutoCommit( ) method.) Now you can execute the SQL statements that comprise your transaction. When you are done, call the commit( ) method to commit the transaction or the rollback( ) method to undo it. Here's an example:

try { con.setAutoCommit(false); // Run some SQL stmt.executeUpdate("UPDATE INVENTORY SET ONHAND = 10 WHERE ID = 5"); stmt.executeUpdate( "INSERT INTO SHIPPING (QTY) VALUES (5)"); con.commit( ); } catch (SQLException e) { con.rollback( ); //Undo the results of the transaction }

When autocommit is set to false, you must remember to call commit( ) (or rollback( )) at the end of each transaction, or your changes will be lost.

JDBC supports a number of transaction isolation modes that allow you to control how the database deals with transaction conflictsin other words, who sees what when. JDBC defines five modes, some of which may not be supported by all databases. The default mode varies depending on the underlying database and driver. Higher isolation levels generally yield poorer performance. Here are the five standard options, which are defined as integer constants in the Connection interface:

TRANSACTION_NONE

Transactions are either disabled or not supported.

TRANSACTION_READ_UNCOMMITTED

Minimal transaction support that allows dirty reads. In other words, other transactions can see the results of a transaction's SQL statements before the transaction commits itself. If you roll back your transaction, other transactions may be left with invalid data.

TRANSACTION_READ_COMMITTED

Transactions are prevented from reading rows with uncommitted changes; in other words, dirty reads aren't allowed.

TRANSACTION_REPEATABLE_READ

Protects against repeatable reads as well as dirty reads. Say one transaction reads a row that is subsequently altered (and committed) by another transaction. If the first transaction reads the row again, the first transaction doesn't get a different value the second time around. The new data is visible to the first transaction only after it calls commit( ) and performs another read.

TRANSACTION_SERIALIZABLE

Provides all the support of trANSACTION_REAPEATABLE_READ and guards against row insertions as well. Say one transaction reads a set of rows and then another transaction adds a row to the set. If the first transaction reads the set again, it doesn't see the newly added row. Put another way, this level of isolation forces the database to treat transactions as if they occurred one at a time.

Transaction isolation modes are set by the setTransactionIsolation( ) method. For example:

con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);

You can use the DatabaseMetaData class to determine the transaction support of the underlying database. The most useful methods are getdefaultTransaction Isolation( ), supportsTransactions( ), supportsTransactionIsolationLevel( ), and supportsDataDefinitionAndDataManipulationTransactions( ) (which may well be the longest method name in the Java API).

An application that uses transactions is a prime candidate for also using a connection pool (available as of JDBC 2.0). Since each database transaction requires its own Connection object, an application that performs multiple simultaneous transactions (for instance, spawning threads that perform database updates) needs multiple connections available. Maintaining a pool of connections is much more efficient than creating a new one whenever you need a new transaction.

8.9.1. Distributed Transactions

With appropriate driver support, the XADataSource interface can also be used to create connections supporting distributed transactions. A distributed transaction is spread across more than one database and is coordinated by an external transaction monitor. Like pooled connections, the DataSource must be configured by the administrator of the J2EE environment.

While connections supporting distributed transactions are nearly indistinguishable from regular connections, there is a functional difference: autocommit mode defaults to off, and when a connection is used within a distributed transaction, the rollback( ), commit( ), and setAutoCommit( ) methods should not be called.

Connections received from an XADataSource may be used for nondistributed transactions as well. All of the usual transaction management commands may be used in a nondistributed transaction.

8.9.2. Savepoints

Sometimes it's helpful to be able to roll back part of a transaction but not the whole thing. JDBC 3.0 comes to the rescue with support for an advanced database feature called a savepoint. A savepoint is a named point within a transaction. If the underlying database and driver support the functionality, you can use the setSavepoint(String name) method of Connection to create a named savepoint in the current transaction and get an object implementing the Savepoint interface. This object can be passed to the rollback( ) method of Connection to roll back all components of the current transaction that took place after the setSavepoint( ) method was called:

Statement stmt = con.createStatement( ); stmt.executeUpdate("delete from clients"); stmt.executeUpdate( "insert into clients (NAME, ID) values ('Charles Babbage', 1)"); Savepoint save = con.setSavepoint("INSERT_POINT"); stmt.executeUpdate( "update clients set NAME = 'Ada Lovelace' where ID = 1"); con.rollback(save); con.commit( );

This example will leave the clients table with a single row, with a value of one in the ID column and Charles Babbage in the NAME column.

Savepoints can't be used in distributed transactions, at least with any of the databases we've tried.