Optimistic locking in JDBC applications

You can write JDBC applications to take advantage of optimistic locking on a data source.

Optimistic locking is a technique that applications can use to release locks between SELECT and UPDATE or DELETE operations. If the selected rows change before that application updates or deletes them, the UPDATE or DELETE operation fails. Optimistic locking minimizes the time during which a given resource is unavailable for use by other transactions.

For connections to a DB2® for i data source, use of optimistic locking requires DB2 for i V6R1 or later.

In general, an application performs these steps to use optimistic locking:

1. Select rows from a table.
2. Release locks on the table.
3. Update the selected rows, if they have not changed.

To check whether the row has changed, the application queries the row change token. The row change token is not always a completely accurate indicator of whether the row has changed. If you create a table with a row change timestamp column, the row change token is completely accurate. If you create the table without a row change timestamp column, or alter a table to add a row change timestamp column, the row change token might not accurately reflect updates to a row. This means that the row change token might indicate that a row has changed, even though it has not. This condition is called a false negative condition.

When you write a JDBC application to perform optimistic locking you follow similar steps:

1. Prepare and execute a query.

   Indicate whether you want optimistic locking information, and whether that information can include false negatives.

2. Determine whether the ResultSet has optimistic locking information, and whether that information can produce false negatives.

   Based on the type of optimistic locking information, you can decide whether to continue with optimistic locking.

3. Release locks on the table.
4. Update the selected rows, if the row change token indicates that they have not changed.

The following code demonstrates how a JDBC application can perform optimistic locking. The numbers in the example correspond to the previously listed steps.

com.ibm.db2.jcc.DB2Statement s1 = 
  (com.ibm.db2.jcc.DB2Statement)conn.createStatement();
ResultSet rs = 
 ((com.ibm.db2.jcc.DB2Statement)s1).executeDB2OptimisticLockingQuery 
 ("SELECT EMPNO, SALARY FROM EMP WHERE EMP.LASTNAME = 'HAAS'", 
  com.ibm.db2.jcc.DB2Statement.RETURN_OPTLOCK_COLUMN_NO_FALSE_NEGATIVES);  1 
                                   // Indicate that you plan to do
                                   // optimistic locking, and that you
                                   // want optimistic locking information
                                   // that does not generate
                                   // false negatives
ResultSetMetaData rsmd = rs.getMetaData();
int optColumns =                                                           2 
  ((com.ibm.db2.jcc.DB2ResultSetMetaData)rsmd).getDB2OptimisticLockingColumns();
                                   // Retrieve the optimistic locking 
                                   // information.
boolean optColumnsReturned = false;

if (optColumns == 0);              // If optimistic locking information is not
                                   // returned, do not attempt to do 
                                   // optimistic locking.
else if (optColumns == 1);         // A value of 1 is never returned if  
                                   // RETURN_OPTLOCK_COLUMN_NO_FALSE_NEGATIVES
                                   // is specified, because 1 indicates 
                                   // that there could be false negatives.
else if (optColumns == 2)          // If optimistic locking information is 
   optColumnsReturned = true;      // returned, and false negatives will not
                                   // occur, try optimistic locking.

rs.next();                         // Retrieve the contents of the ResultSet
int emp_id = rs.getInt(1);
double salary = rs.getDouble(2);
 
long rowChangeToken = 0;
Object rid = null;
int type = -1;

if (optColumnsReturned) {          
  rowChangeToken =                 // Get the row change token.
    ((com.ibm.db2.jcc.DB2ResultSet)rs).getDB2RowChangeToken();
  rid = ((com.ibm.db2.jcc.DB2ResultSet)rs).getDB2RID();
                                   // Get the RID, which uniquely identifies
                                   // the row.
  int type = ((com.ibm.db2.jcc.DB2ResultSet)rs).getDB2RIDType ();
                                   // Get the data type of the RID.
}   
// ***************************************************
// Release the locks or disconnect from the database. 
// Perform some work on the retrieved data.
// Reconnect to the data source.
// ***************************************************
…
PreparedStatement s2 = 
  conn.prepareStatement ("UPDATE EMP SET SALARY = ? " +
  "WHERE EMPNO = ? AND ROW CHANGE TOKEN FOR EMP = ? and " +
  "RID_BIT(EMP) = ?");
                                   // Statement for updating the
                                   // previously selected rows that
                                   // have not changed.
s2.setDouble(1, salary+10000);
s2.setInt(2, emp_id);
                                   // Set the new row values.
s2.setLong(3, rowChangeToken);
                                   // Set the row change token of the
                                   // previously retrieved row.
if (type == java.sql.Types.BIGINT)
  s2.setLong (4, ((Long)rid).longValue());
else if (type == java.sql.Types.VARBINARY)
  s2.setBytes (4, (byte[])rid);
                                   // Set the RID of the previously
                                   // retrieved row.
                                   // Use the correct setXXX method
                                   // for the data type of the RID.
int updateCount = s2.executeUpdate();                                       3 
                                   // Perform the update.
if (updateCount == 1);             // Update is successful.
else                               // Update failed.
…