July 02, 2013

Avoiding Lock Contention

Having spent many years tuning batch applications and designing systems that could simultaneously run batch and online applications, I've learned quite a bit about locking and how to avoid it. Here's a presentation I delivered to the Midwest Database User Group (MWDUG) back in 2008. On slide 12 I discuss the lock size needed to improve availability. Then on slide 13 I discuss commit frequencies as another way to improve availability. However, even if you take these steps, if you have a very high transaction rate, you still may experience lock contention.

With this in mind, I'm excited about a DB2 10 feature that's designed to improve throughput and reduce timeouts due to locks being held on rows you're trying to read. The best way to take advantage of this capability is to use the BIND/REBIND command and select the CURRENTACCESSRESOLUTION option (USECURRENTLYCOMMITTED).

Alternatively, this capability can be enabled through a new system parameter, SKIPUNCI (skip uncommitted inserts), which allows committed changes to be read while uncommitted inserts occur. This feature only works with statements that are running with row-level locking and read stability or cursor stability isolation levels.

As you probably know, IBM defaults to existing functionality for all new features, so the default value of SKIPUNCI is NO. The NO setting tells DB2 to wait for a commit or rollback before reading the data. By setting SKIPUNCI to YES, DB2 allows data to be read and ignores insertion of rows that haven't been committed.

This table spells it out. With the NO default, you will wait until a commit. With the YES reset, you will skip uncommitted inserts.


SKIPUNCI value              CONCURRENTACCESSRESOLUTION value             Skip or Wait  

YES                  USECURRENTLYCOMMITTED                    Skip uncommitted INSERTs

YES                  WAITFOROUTCOME                           Wait COMMIT or ROLLBACK

YES                  Not specified                            Skip uncommitted INSERTs

NO                   USECURRENTLYCOMMITTED                    Skip uncommitted INSERTs

NO                   WAITFOROUTCOME                           Wait COMMIT or ROLLBACK

NO                   Not specified                            Wait COMMIT or ROLLBACK


As is generally the case, it's advisable to test this feature before changing the system default in your production environment.

Craig Mullins has been covering this and other locking-related topics of late. Check out DB2 Locking Part 7: Lock Avoidance, Related Issues, and Stuff.”

So have you taken advantage of SKIPUNCI? What is your system setting for SKIPUNCI and why is it set to this value? Please share your experiences in comments.