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

Begin program-specific programming interface information. 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.

Important: When Db2 skips data because of the SKIP LOCKED DATA option, it does not issue a warning. Even if only a subset of the data that satisfies a query is returned or modified, the transaction completes as if no data was skipped. Use the SKIP LOCKED data option only when the requirements and expectations of the application match this behavior.

Procedure

To improve concurrency for applications that require fast results and can tolerate incomplete results:

Specify the SKIP LOCKED DATA clause in one of the following SQL statements:

SELECT
SELECT INTO
PREPARE
Searched-UPDATE
Searched-DELETE

You can also use the SKIP LOCKED DATA option with the UNLOAD utility.

Lock mode compatibility for transactions that use the SKIP LOCKED DATA option is the same as lock mode compatibility for other page- and row-level locks, except that a transaction that uses the SKIP LOCKED DATA option does not wait for the locks to be released and skips the locked data instead.

Example

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. End program-specific programming interface information.