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.
Mullins has been covering this and other locking-related topics of late. Check
out “DB2 Locking Part 7: Lock Avoidance, Related Issues, and Stuff.”