Scenario: Using optimistic locking in an application program

This scenario demonstrates how optimistic locking is implemented in an application program, covering six different scenarios.

Consider the following sequence of events in an application designed and enabled for optimistic locking: 
  SELECT QUANTITY, row change token FOR STOCK, RID_BIT(STOCK)
		 INTO :h_quantity, :h_rct, :h_rid 
		 FROM STOCK WHERE PARTNUM = 3500

In this scenario, the application logic reads each row. Since this application is enabled for optimistic locking as described in Enabling optimistic locking in applications, the select list includes the RID_BIT() value saved in the :h_rid host variable and the row change token value saved in the :h_rct host variable.

With optimistic locking enabled, the application optimistically assumes any rows targeted for update or delete will remain unchanged, even if they are unprotected by locks. To improve database concurrency, the application removes the row lock(s) using one of the following methods:
  • Committing the unit of work, in which case the row locks are removed
  • Closing the cursor using the WITH RELEASE clause, in which case the row locks are removed
  • Using a lower isolation level:
    • CURSOR STABILITY (CS) in which case the row is not locked after the cursor fetches to the next row, or to the end of the result table.
    • UNCOMMITED READ (UR) in which case any uncommitted data has a new (uncommitted) row change token value. If the uncommitted data is rolled back, then the old committed row change token will be a different value.
      Note: Assuming updates are not normally rolled back, using UR allows the most concurrency.
  • Disconnecting from the database, thus releasing all Db2® server resources for the application. (.NET applications often use this mode).
The application processes the rows and decides it wants to optimistically update one of them: 
  UPDATE STOCK SET QUANTITY = QUANTITY - 1 
   WHERE row change token FOR STOCK = :h_rct AND
	  RID_BIT(STOCK) = :h_rid
The UPDATE statement updates the row identified in the SELECT statement shown previously.
The searched UPDATE predicate is planned as a direct fetch to the table: 
   RID_BIT(STOCK) = :h_rid
Direct fetch is a very efficient access plan, that is simple for the Db2 optimizer to cost. If the RID_BIT() predicate does not find a row, the row was deleted and the update fails with row not found.

Assuming that the RID_BIT() predicate finds a row, the predicate row change token FOR STOCK = :h_rct will find the row if the row change token has not changed. If the row change token has changed since the SELECT, the searched UPDATE fails with row not found.

Table 1 lists the possible scenarios that could occur when optimistic locking is enabled.
Table 1. Scenarios that could occur when optimistic locking is enabled
Scenario ID Action Result
Scenario 1 There is a row change timestamp column defined on the table and no other application has changed the row. The update succeeds as the row change token predicate succeeds for the row identified by :h_rid.
Scenario 2 There is a ROW CHANGE TIMESTAMP defined on the table. Another application updates the row after the select and before the update (and commits), updating the row change timestamp column. The row change token predicate fails comparing the token generated from the timestamp in the row at the time of the select and the token value of the timestamp currently in the row. So the UPDATE statement fails to find a row.
Scenario 3 There is a ROW CHANGE TIMESTAMP defined on the table. Another application updates the row and so the row has a new row change token. This application selects the row at isolation UR and gets the new uncommitted row change token. This application runs the UPDATE, which will lock wait until the other application releases its row lock. The row change token predicate will succeed if the other application commits the change with the new token, so the UPDATE succeeds. The row change token predicate will fail if the other application rolls back to the old token, so the UPDATE fails to find a row.
Scenario 4 There is no row change timestamp column defined on the table. Another row is updated, deleted or inserted on the same page, after the select and before the update. The row change token predicate fails comparing the token because the row change token value for all rows on the page has changed, so the UPDATE statement fails to find a row even though our row has not actually changed.

This false negative scenario would not result in an UPDATE failure if a row change timestamp column was added.
Scenario 5 The table has been altered to contain a row change timestamp column, and the row returned in the select has not been modified since the time of the alter. Another application updates the row, adding the row change timestamp column to that row in the process with the current timestamp. The row change token predicate fails comparing the token generated from before with the token value created from the row change timestamp column so the UPDATE statement fails to find a row. Since the row of interest has actually been changed this is not a false negative scenario.
Scenario 6 The table is reorganized after the select and before the update. The row ID identified by :h_rid does not find a row, or contains a row with a different token so the update fails. This is the form of false negative that cannot be avoided even with the existence of a row change timestamp column in the row. The row itself is not updated by the reorganization but the RID_BIT portion of the predicate cannot identify the original row after the reorganization.