Chapter 2: JDBC

2.10. Transactions

A transaction is a group of several operations that must behave atomically, or as if they are a single, indivisible operation. With regards 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.

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 where, for example, the INVENTORY table has been debited, but the SHIPPING table has not been credited.

Another issue with transactions and databases concerns when changes to the database become 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 auto-commit 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 auto-commit with the getAutoCommit() method.) Now you can execute the SQL statements that comprise your transaction. When you are done, you 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 auto-commit 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 conflicts--in 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 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, or in other words, dirty reads are not 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 does not 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 does not 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 getDefaultTransactionIsolation(), supportsTransactions(), supportsTransactionIsolationLevel(), and supportsDataDefinitionAndDataManipulationTransactions() (which may very 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 in 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.

2.9. Metadata		2.11. Stored Procedures

Copyright © 2001 O'Reilly & Associates. All rights reserved.