Improving concurrency for applications that tolerate incomplete results
You can use the SKIP LOCKED DATA option to skip rows that are locked to increase the concurrency of applications and transactions that can tolerate incomplete results.
Before you begin
Your application must use one of the following isolation levels:
- Cursor stability (CS)
- Read stability (RS)
The SKIP LOCKED DATA clause is ignored for applications that use uncommitted read (UR) or repeatable read (RR) isolation levels.
About this task
The SKIP LOCKED DATA option allows a transaction to skip rows that are incompatibly locked by other transactions when those locks would hinder the progress of the transaction. Because the SKIP LOCKED DATA option skips these rows, the performance of some applications can be improved by eliminating lock wait time. However, you must use the SKIP LOCKED DATA option only for applications that can reasonably tolerate the absence of the skipped rows in the returned data. If your transaction uses the SKIP LOCKED DATA option, it does not read or modify data that is held by locks.
However, keep in mind that your application cannot rely on Db2 to skip all data for which locks are held. Db2 skips only locked data that would block the progress of the transaction that uses the SKIP LOCKED DATA option. If Db2 determines through lock avoidance that the locked data is already committed, the locked data is not skipped. Instead, the data is returned with no wait for the locks.
To improve concurrency for applications that require fast results and can tolerate incomplete results:
- SELECT INTO
Suppose that a table WORKQUEUE exists in a table space with row-level locking and has as part of its definition ELEMENT, PRIORITY and STATUS columns, which contain the following values:
ELEMENT PRIORITY STATUS 1 1 OPEN 2 1 OPEN 3 3 OPEN 4 1 IN-ANALYSIS
Suppose that a transaction has issued an UPDATE against ELEMENT 1 to change its STATUS from OPEN to IN-ANALYSIS, and that the UPDATE has not yet committed:
UPDATE WORKQUEUE SET STATUS = 'IN-ANALYSIS' WHERE ELEMENT = 1;
Suppose that a second transaction issues the following SELECT statement to find the highest priority work item:
SELECT ELEMENT FROM WORKQUEUE WHERE PRIORITY = '1' AND STATUS='OPEN' SKIP LOCKED DATA;
This query locates the row that contains the ELEMENT=2 value, without waiting for the transaction that holds a lock on the row that contains the ELEMENT=1 value to commit or rollback its operation.
However, you cannot always expect Db2 to skip this data. For example, Db2 might use lock avoidance or other techniques to avoid acquiring certain locks.