Optimistic locking overview

Enhanced optimistic locking support provides a technique for SQL database applications that does not hold row locks between selecting, and updating or deleting rows.

Applications can be written to optimistically assume that unlocked rows are unlikely to change before the update or delete. If the rows do change, the updates or deletes will fail and the application's logic can handle such failures, for example, by retrying the select.

The advantage of this enhanced optimistic locking is improved concurrency, since other applications can read and write those same rows. In three-tier environments where business transactions have no correlation to database transactions, this optimistic locking technique is used, since locks cannot be maintained across business transactions.

Table 1 lists the relevant topics in each category.

Table 1. Overview to optimistic locking information
Category	Related topics
General information and restrictions	Optimistic locking Granularity of row change tokens and false negatives Optimistic locking restrictions and considerations
Time-based updates	Time-based update detection Time values generated for ROW CHANGE TIMESTAMPs
Enabling	Planning the enablement of optimistic locking Enabling optimistic locking in applications
Usage scenarios	Scenarios: Optimistic locking and time-based detection Scenario: Using optimistic locking in an application program Scenario: Time-based update detection Scenarios: Optimistic locking using implicitly hidden columns
Reference information	RID_BIT() and RID() built-in function ALTER TABLE statement CREATE TABLE statement DELETE statement SELECT statement UPDATE statement

Note: Throughout the optimistic locking topics, whenever a row is referred to as being inserted or updated, this refers to all forms of SQL statements that could cause a row to be inserted into a table or updated in any way. For instance, INSERT, UPDATE, MERGE, or even the DELETE statement (with referential constraints) can all cause the timestamp column to be either created or updated.