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.

It is important to understand how to use the SKIP LOCKED DATA clause in select-statement, SELECT INTO, searched UPDATE or searched DELETE statement. The use of the new clause can significantly affect the query result as all qualified rows may not be returned. However, please note that Db2 might use lock avoidance techniques, like currently committed and eval uncommitted, to avoid taking certain locks and may not skip the data even if there happens to be a lock on it.

When using the new function, careful application design should be considered to closely fit the desired business needs. You may expect less than accurate information in exchange for getting the query result rapidly without getting hung up on locks.

In all cases, with SKIP LOCKED DATA, Db2 will return the most accurate data without waiting for locks. If there is a need to access locked data to answer the query, Db2 will skip locked data and provide partial results (or values derived from partial results). If there is no need to lock data to answer the query, Db2 will not skip the data even if there happens to be a lock on it. Examples below illustrate two such scenarios.

Scenario 1: SKIP LOCKED DATA does not skip rows due to currently committed semantics

Create a table TI as:

CREATE TABLE T1
 (C1 INTEGER,
  C2 VARCHAR(30))

Populate the table T1 with values as:

Table 1.
C1	C2
1	AAAAAAAA
2	BBBBBBBB
3	CCCCCCCC
4	DDDDDDDD
5	EEEEEEEE

Table 2.
Session 1	Session 2
`db2 +c "update T1 set C2='new value from session1' where C1 <= 3"` Session 1 acquires X lock on Rows 1, 2, and 3
	`db2 +c "select * from TQ where C1 >= 3 SKIP LOCKED DATA"` Session 2 (CS isolation) needs to read row 3. Since it is locked on X mode by Session 1, Session 2 avoids taking lock using Currently Committed read semantics. It acquires currently committed data of Ro3 from member 2. Session 2 does not skip Row 3. Resultset returns 3 rows: C1 C2 3 CCCCCCCC 4 DDDDDDDD 5 EEEEEEEE

Note: CUR_COMMIT does not apply to SELECT statements when FOR UPDATE or RS isolation are used. If SKIP LOCKED is used, such queries, as well as searched updates and deletes, will skip rows rather than waiting for a lock when a lock conflict is encountered.

Scenario 2: SKIP LOCKED DATA does not skip rows due to lock avoidance

Create a table TI as:

CREATE TABLE T1
 (C1 INT,
  C2 CHAR );

CREATE INDEX IX1 ON T1(C2);

INSERT INTO T1 VALUES (1, 'AAAA');
INSERT INTO T1 VALUES (2, 'BBBB');
INSERT INTO T1 VALUES (3, 'CCCC');
INSERT INTO T1 VALUES (4, 'DDDD');

Table 3.
Session 1	Session 2
`db2 +c "UPDATE T1 SET C1 = 99 WHERE C1 < 3"` Session 1 acquires X lock on Rows 1 and 2
	`db2 +c "select count(*) from T1 where c2 >= 'AAAA' SKIP LOCKED DATA`" SELECT will use an index only scan All data on the relevant index page are committed and UPDATE in Session 1 will not affect anything on the index page. Hence, Db2 will avoid getting any locks. SELECT will not skip any rows and return a count of 4.