July 7, 2011, 1:35 p.m.
posted by franni
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.
// Begin the transaction by setting autocommit to false connection.setAutocommit(false); // First insert the new Employee's address PreparedStatement insert = connection.createPreparedStatement("INSERT INTO ADDRESS (id, 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"); insert.executeUpdate(); // 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, address) VALUES (? ? ?)"); insert.setString(1, "12345"); insert.setString(2, "Karl Johnson"); insert.setInteger(3, 23450); insert.setString(4, "22333"); insert.executeUpdate(); // finally, commit the transaction connection.commit();
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:
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.