Improved performance through concurrency control

Concurrency control relies on the isolation level and current data options of applications to determined how much to isolate different applications that access the same data from each other. Too much isolation might result in contention, whereas too little isolation might result in the return of non-current data to applications.

The ISOLATION option of an application specifies the degree to which operations are isolated from the possible effects of other operations that act concurrently. The ISOLATION options specified how soon Db2 can release S and U locks on rows or pages. Regardless of the isolation level that you specify, outstanding claims on Db2 objects can inhibit the execution of Db2 utilities or commands.

The CURRENTDATA option of an application specifies whether data currency is required for read-only and ambiguous cursors when the ISOLATION(CS) option is used. This option enables a trade-off between the improved ability of multiple applications to access the same data concurrently and the risk that non-current data might be returned to the application.

The basic recommendation is to bind most applications with the ISOLATION(CS) and CURRENTDATA(NO) options. ISOLATION(CS) typically enables Db2 to release acquired locks as soon as possible. The CURRENTDATA(NO) typically enables Db2 to acquire the fewest number of locks, for better lock avoidance.

Db2 provides the following isolation levels:

Cursor stability (CS)

The ISOLATION (CS) or cursor stability option allows maximum concurrency with data integrity. Under the ISOLATION (CS) option, a transaction holds locks only on its uncommitted changes and on the current row of each of its cursors.

Uncommitted read (UR)

The ISOLATION (UR) or uncommitted read option allows an application to read while acquiring few locks, at the risk of reading uncommitted data. UR isolation applies only to the following read-only operations: SELECT, SELECT INTO, or FETCH from a read-only result table.

Read stability (RS)

The ISOLATION (RS) or read stability option enables an application to read the same pages or rows more than once and prevents updates or deletes to qualifying rows by other processes. However, other applications can insert or update rows that did not satisfy the search condition of the original application.

Repeatable read (RR)

The ISOLATION (RR) or repeatable read option allows the application to read the same pages or rows more than once without allowing any update, insert, or delete operations by other processes. All accessed rows or pages are locked, even if they do not satisfy the predicate. Under the ISOLATION (RR) option, the data that an application references cannot be updated by any other applications before the application reaches a commit point.

Db2 uses and depends on locks because of the requirement for data integrity. However, locks are sometimes the cause of problems with contention, such as deadlocks, timeouts, and suspensions. To minimize these problems and promote concurrency, database designers and application designers can take various actions.