Lock avoidance and Currently Committed semantics
Prior to DB2 9.7, if the Cursor Stability isolation level was used and a row was locked on behalf of a transaction, DB2 would block attempts by other concurrently running transactions to modify the locked row. Furthermore, if the locked row was changed in any way by the transaction holding the lock, other SQL statements in concurrent transactions were not allowed to access the row (unless they were running under the Uncommitted Read isolation level) until the transaction was terminated. (In other words, writers would block readers and in some situations readers could block writers.) In either case, concurrent transactions that needed to access a locked row were forced to wait for the lock to be released before they could continue processing. This, in turn would often cause undesired behavior to occur.
With DB2 9.5, a number of lock avoidance techniques were introduced to
help eliminate some of the locking overhead that had been required for the
Cursor Stability isolation level. Essentially, these techniques allow scan
operations to execute without locking rows when the data and/or pages being
accessed are known to have been committed. For example, consider the following
SELECT COUNT(*) FROM sales.
Prior to DB2 9.5, when such a query was executed, the first row in the table specified would be locked, a count would be taken, and the lock would be released. Then the second row in the table would be locked, the count would be updated, and the lock would be released. And this would continue until all of the rows in the table had been counted. With DB2 9.5 and later, the same query will scan the table specified and count the rows, but intermittent locks are no longer acquired and released — provided DB2 can determine that the rows have been committed without having to acquire locks. Essentially, lock avoidance allows DB2 to determine if the data needed has been committed, and if that is indeed the case, locks are not acquired. With DB2 9.7 and 10.1, lock avoidance works for any read-only SQL statement executed under the Cursor Stability isolation level using cursor blocking. (Cursor blocking is a technique that reduces overhead by having DB2 retrieves a block of rows, rather than a single row, in one operation.)
With DB2 9.7, a new implementation of the Cursor Stability isolation level was provided that incorporates Currently Committed (CC) semantics to further prevent writers from blocking readers. The intent is to provide a Cursor Stability isolation level that avoids lock waits without violating ANSI standards for Cursor Stability isolation level semantics. (With earlier versions of DB2 for Linux, UNIX, and Windows, the following registry variables could be used to delay or avoid acquiring locks in some circumstances:
- DB2_SKIPINSERTED — Allow Cursor Stability/Read Stability scans to skip uncommitted inserted rows.
- DB2_SKIPDELETED — Allow Cursor Stability/Read Stability scans to skip uncommitted deleted rows and index keys.
- DB2_EVALUNCOMMITTED — Allow Cursor Stability/Read Stability scans to apply and perform query predicate evaluation on uncommitted data; also allow the scans to skip uncommitted deleted rows. In effect, scans are treated as an Uncommitted Read operation until a qualifying row is found, at which time DB2 may need to acquire a lock to ensure that only committed data is processed or returned.
But the use of these registry variables causes the ANSI standard for Cursor Stability isolation level semantics to be violated.)
Using the lock avoidance techniques introduced in DB2 9.5, a read-only transaction operating under Currently Committed semantics will not acquire a lock as long as DB2 can determine that the data needed has been committed. (Transactions performing read and write operations avoid lock waits on uncommitted inserts, and transactions performing read-only operations end up trading a lock wait for a log read when they encounter uncommitted updates/deletes from concurrent transactions.) If DB2 is unable to make a determination as to whether a row has been committed, it will try to acquire a lock on the row in question on the transaction's behalf — if a lock can be acquired, processing will continue using traditional Cursor Stability isolation level behavior. If a lock cannot be acquired (because another transaction holds an Exclusive lock on the row) DB2 will examine the lock that is held by the other transaction to obtain information about the row that contains the data needed. Each lock can contain one (and only one) of the following:
- No information — Indicates that the row is locked but nothing has been done to it (no uncommitted changes are in-flight).
- An Uncommitted Insert identifier — Indicates that the row is a newly inserted row that has not yet been committed.
- Log information — Indicates that the row contains uncommitted data. In this case, the log information identifies the log record that corresponds to the first time the row was modified by the transaction that currently holds the lock on the row.
If the lock contains no information, the row is treated as if the desired lock was acquired. If the lock contains an Uncommitted Insert identifier, the row is skipped since this identifier represents a row that has not yet been committed. And if the lock contains log information, this information is used to return the Currently Committed version of the row (i.e., the row as it existed before changes were initiated) from a log record stored in the log buffer or a transaction log file. (DB2 uses the Log Sequence Number, or LSN, to directly access the appropriate log record (see Sidebar)).
It is important to note that Currently Committed semantics can apply to SQL statements executed under the Read Stability (RS) and the Cursor Stability isolation levels. Under the Read Stability isolation level, Currently Committed semantics only provides DB2_SKIPINSERTED behavior, which is the capability to no longer incur lock waits for uncommitted inserted rows.
Figure 6 illustrates how a
SELECT statement running the Cursor Stability isolation level with
Currently Committed semantics enabled will retrieve records when another
transaction is making changes to the records simultaneously. In this example,
Transaction 1 executed three DML statements, which caused log information to be
written to the log buffer and an uncommitted insert identifier to be written to
the lock list for the SALES_REP table. When transaction 2 queried the SALES_REP
table, Currently Committed semantics allowed data for locked rows to be read
from log records that contained information about previously committed
transactions; the record for the uncommitted insert was not returned by the
Figure 6. Example of how a query running under the Cursor Stability isolation level with Currently Committed semantics enabled will retrieve records
By default, Currently Committed semantics are turned on for new
databases created with DB2 9.7 and later. Existing databases upgraded to DB2
9.7 or later can take advantage of Currently Committed semantics by assigning
either the value
ON or the value
AVAILABLE to the cur_commit database configuration
parameter of the database that has been converted. If the cur_commit
database configuration parameter is set to
Currently Committed semantics are applied database-wide for both the Read
Stability and Cursor Stability isolation levels. If the cur_commit
database configuration parameter is set to
instead, DB2 will store the appropriate information in locks and perform the
extra logging overhead needed (to ensure that the logged data contains the full
uncommitted version of the row being changed) to support Currently Committed
semantics. Currently Committed semantics behavior will have to be
enabled on an application-by-application basis. This is done by binding
an embedded SQL application to the database using the
CONCURRENTACCESSRESOLUTION USE CURRENTLY COMMITTED option or by
connection attribute with CLI/ODBC and Java applications.
It is important to note that the use of Currently Committed semantics will result
in an increase in the amount of log space needed for update operations to tables
that have been defined as
DATA CAPTURE NONE. This
additional space is used to log the first update of a data row by a
transaction; it is this data that is used to retrieve the currently committed
image of the row.