DB2 & IMS

z/Data Perspectives: Much Ado About DB2 9 Locking

2 Pages

Of the many new features and functionalities offered by DB2 9 for z/OS, some of the more intriguing ones deal with locks and locking.

One of the intriguing DB2 9 locking features enables a transaction to skip over rows that are locked. This can be accomplished by the SKIP LOCKED DATA option within your SQL statement(s). SKIP LOCKED DATA can be specified in SELECT, SELECT INTO, and PREPARE, as well as searched UPDATE and DELETE statements. You also can use the SKIP LOCKED DATA option with the UNLOAD utility.

Of course, if a program skips over locked data, then that data isn’t accessed and the program won’t have it available. When this option is used, DB2 will just skip over any locked data instead of waiting for it to be unlocked. The benefit, of course, is improved performance because you won’t incur any lock wait time. However, it comes at the cost of not accessing the locked data at all. This means you should utilize this clause only when your program can tolerate skipping over some data.

The SKIP LOCKED DATA option is compatible with Cursor Stability (CS) isolation and Read Stability (RS) isolation. But it can’t be used with Uncommitted Read (UR) or Repeatable Read (RR) isolation levels. DB2 will simply ignore the SKIP LOCKED DATA clause under UR and RR isolation levels. Suppose we have a table with four rows such as this:

KEY FNAME LNAME

1 JOE MAMA

2 KIM PORTANT

3 JOE PATERNO

4 DON KNOTTS

Assume row-level locking and an UPDATE that changes FNAME to JIM WHERE FNAME = ‘JOE’, and it’s hanging out there without a COMMIT. Next, we run:

2 Pages