At a JDBC level, the basic approach is to set the isolation level of the connection to
TRANSACTION_READ_UNCOMMITTED, and then to lock explicitly only that data that
needs to be locked during the transaction:
Connection c = DriverManager . getConnection (); // Or...get it from a local pool
c . setAutoCommit ( false
c . setTransactionIsolation ( TRANSACTION_READ_UNCOMMITTED );
PreparedStatement ps1 = c . prepareStatement (
"SELECT * FROM employee WHERE e_id = ? FOR UPDATE" );
... process info from ps1 ...
PreparedStatement ps2 = c . prepareStatement (
"SELECT * FROM office WHERE office_id = ?" );
... process info from ps2 ...
c . commit ();
The ps1 statement establishes an explicit lock on the employee data table: no other transac-
tion will be able to access that row for the duration of this transaction. The SQL syntax to ac-
complish that is nonstandard. You must consult your database vendor's documentation to see
how to achieve the desired level of locking, but the common syntax is to include the FOR
UPDATE clause. This kind of locking is called pessimistic locking . It actively prevents other
transactions from accessing the data in question.
Locking performance can often be improved by using optimistic locking—the same way that
the java.util.concurrent.atomic package approaches uncontended atomic operations. If
the data access is uncontended, this will be a significant performance boost. If the data is
even slightly contended, however, the programming becomes more difficult.
In a database, optimistic concurrency is implemented with a version column. When data is
selected from a row, the selection must include the desired data plus a version column. To se-
lect information about me, I could issue the following SQL:
SELECT first_name, last_name, version FROM employee WHERE e_id = 5058;
This query will return my names (Scott and Oaks) plus whatever the current version number
is (say, 1012). When it comes time to complete the transaction, the transaction updates the
UPDATE employee SET version = 1013 WHERE e_id = 5058 AND version = 1012;