JDBC Transactions

JDBC Transactions

In JDBC each javax.sql.Connection object has two ways of starting and ending a transaction. In the default mode (called autocommit), each individual JDBC statement is executed in its own transaction. So, if you created two separate INSERT statements, one for an employee and one for the employee's address within a method, it would be possible to create an address that refers to an employee that does not exist if the INSERT for the address succeeds while the INSERT for the employee fails.

To get around this there is a second mode in JDBC whereby the transaction associated with a connection can encompass the execution of several javax.sql.Statement objects created from the connection. Therefore, you can have code like that in Listing 28.1.

Manually controlled JDBC transaction

// Begin the transaction by setting autocommit to false


// First insert the new Employee's address

PreparedStatement insert = connection.createPreparedStatement("INSERT INTO ADDRESS (id,

graphics/ccc.gif street, city, state, zip) VALUES (? ? ? ? ?)");

insert.setString(1, "22333");

insert.setString(2, "101 Hummingbird Ln");

insert.setString(3, "Cary");

insert.setString(4, "NC");

insert.setString(5, "27502");


// Now insert his information, including a foreign-key reference to

// the address inserted in the previous block

insert = connection.createPreparedStatement("INSERT INTO Employee (id, name, salary,

graphics/ccc.gif address) VALUES (? ? ?)");

insert.setString(1, "12345");

insert.setString(2, "Karl Johnson");

insert.setInteger(3, 23450);

insert.setString(4, "22333");


// finally, commit the transaction


As you see, it's possible to join multiple SQL statements in a single transaction using the features of the javax.sql.Connection, but there are problems with using this feature.

The first is that you must pass the physical connection object between all of the mappers (DAOs) that will operate on it. This can be tricky, and tends to mean that you have to expose the details of the persistence layer to the layers above it. Overall, it's not pretty.

Another issue is how concurrent transactions affect each other. If you run two transactions at the same time, how do you keep data updated in one transaction from being read in the other before it completes? JDBC solves this problem (as does the ANSI SQL-92 standard) using what are called isolation levels. You can set the isolation level of a JDBC connection by using the setTransactionIsolation() method. The isolation levels defined by the JDBC specification (as static fields in java.sql.Connection) are as follows:

  • TRANSACTION_NONE— Transactions are not supported.

  • TRANSACTION_READ_UNCOMMITTED— The transaction can read uncommitted data (data changed by a different transaction still in progress).

  • TRANSACTION_READ_COMMITTED— The transaction is not able to read uncommitted data from other transactions. However, nonrepeatable reads (e.g., the first read within a transaction gets one result, while the second gets a different result due to the data being updated by another transaction or program) can occur. Likewise, phantom records can occur—records can be inserted while the transaction (of which this transaction may be unaware) is in progress.

  • TRANSACTION_REPEATABLE_READ— The transaction is guaranteed to always read back the same data on each successive read. Phantom records can still occur.

  • TRANSACTION_SERIALIZABLE— All transactions are serialized (e.g., fully isolated from one another). All rows touched during the transaction are locked for the duration of the transaction.

So, why is this an issue? Why not just use TRANSACTION_SERIALIZABLE and always assume you are safe from phantom records and nonrepeatable reads? The answer lies in the performance of your transactions. In order to keep transactions fully isolated, even reads have to wait in line at the highest isolation level. We'll return to the subject of isolation levels and associated locking later in the chapter.

Finally, there's a yet more troublesome problem that JDBC transactions do not address. There is no easy way to join resources that are not part of JDBC together with JDBC statements within a single transaction. For instance, a common problem in many applications is the following: You have an application that takes orders for widgets over the Internet. The widget order-processing application is a legacy system that is accessed over WebSphere MQ—it's based on a batch system and it takes in orders from a queue and (eventually) process them so that widgets will be delivered to the customer.

This model doesn't fit well with the expectations of an Internet order-entry application. In that case, users will expect to check the status of their order at any time. However, that may not lend itself to a model where an order may sit in a queue for a long time while in between processing steps. It would be cumbersome to have to check several queues to determine where in the order-processing sequence the order resides.

What is often done is to have two different representations of the order—when the order is first received, a record is created in a relational database to represent the order, and the order information is then placed in the first queue for the order-processing system to be processed. As the orders move from one state to another, the information in the database is updated accordingly.

So, we have the following problem in our order entry application: We'd like to create the database record for the order and place it in the queue for processing at the same time and in the same transaction. It's not acceptable if the database record creation fails and the order processing enqueuing succeeds. That would result in a user having widgets delivered (and billed!) to him, but he would not be able to find out any information about the orders in the meantime.

What we need is to join the two resources—the WebSphere MQ Queue and the JDBC Connection that the statement executes on—in the same transaction. That is the domain of the two-phase commit (2-PC) transaction model, as you'll see in the next section.

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows