November 26, 2007

DB2 9 Skipped Locked Data Transactions

I remember when using isolation level UR in online CICS transactions seemed crazy. It turns out using it does make sense sometimes. I’m sure the same can be said about the new SKIP LOCKED DATA select feature. The good news that is this feature is safer than the with UR option in online transactions; however, you still need to understand your application and whether it can tolerate missing data. I highly recommend using this feature with caution, at least until you get a good handle on it to minimize your exposure.

So what is SKIP LOCKED DATA anyways?

The SKIP LOCKED DATA clause can be used on SELECT, UPDATE or DELETE statements. It specifies that rows of data are skipped when incompatible locks are held on the row by other transactions. In other words, rows for any table being accessed for the given statement won't be processed.

While I haven't used this feature, I imagine the benefit is that it helps transactions run faster because they're no longer waiting on locks because they don't need to read or modify the skipped data. It's important to remember that DB2 doesn't issue a warning when returning a subset of data or rows that aren't updated or deleted. The transaction completes as if you didn't use the SKIP LOCKED DATA option. Therefore, your transaction must be able to tolerate inconsistent results.

DB2 ignores SKIP LOCKED DATA when isolation repeatable read (RR) or uncommitted read (WITH UR) are used. You must use cursor stability (CS) or read stability (RS). DB2 also ignores SKIP LOCKED DATA when locking a table, partition, LOB, XML or table space. SKIP LOCKED DATA is used when the transaction is using row or page-level locking.

When locks are compatible, normal processing occurs. When incompatible locks are requested, data is skipped. For example, suppose that Application A holds an S lock on a row that Process B also wants to access. The query in Process B specifies SKIP LOCKED DATA. The outcome of Process B depends on the mode of lock that it acquires. If Process B requires a compatible S or U lock, Process B can access the row on which Application A holds an S lock. If Process B requires an incompatible X lock, Process B cannot access the locked row. Because the SKIP LOCKED DATA option is specified, that row is skipped and the results of Process B are returned without that row.

Here's an example of unexpected or inconsistency when using SKIP LOCKED DATA: You have a table called EMPLOYEE with columns EMPNO and FIRSTNAME. The table contains these values:

1000  ADAM
2000  BOB
4000  MARY

The first transaction issues this statement:


A second transaction issues this select statement:


The result is zero because the row with firstname adam is locked.

IBM has integrated this technology with the UNLOAD utility, so now you can run an UNLOAD using SKIP LOCKED DATA. This could be beneficial when you're trying to unload a table that's very busy with update activity. If you use this feature while moving production data from several tables to your target test environment, you may find missing referential integrity (i.e., the parent rows from one table may not be extracted but the child rows in a second table are extracted). Again, you must be willing to tolerate unexpected results when using this feature.

Thanks for reading, and please share your experiences using DB2 9 for z/OS.