DB2 SELECT statements acquire share locks on rows or pages while processing, which can sometimes result in contention or concurrency issues when other in-flight transactions have updated the data (either via INSERT, UPDATE, or DELETE statements) that the SELECT statement is trying to read, but have not yet committed their updates. This will cause the reader to go into a lock wait state, and it will remain in that lock wait state until the updater either issues a commit or a rollback (thus releasing its locks), or until it has surpassed a DB2 system defined lock timeout threshold.
Lock avoidance for readers can be attained by performing an uncommitted read (“UR”), in which the read transaction will return uncommitted updates in its result set. While this may be acceptable for some kinds of processing (i.e. rough counts or aggregations, where 100% accuracy or data integrity is not necessarily needed), it may not be acceptable for other processing in which there can be zero tolerance for inaccurate data.
Access to “currently committed” data is an option that is available in DB2 v10. When enabled it will allow for lock waits to be avoided when uncommitted inserts or deletes are encountered during the processing of a select statement. Avoiding a lock wait for uncommitted updates is not currently available.
When access to currently committed data is enabled, any rows that are encountered by the select statement that were inserted by in-flight uncommitted transactions will be ignored. Likewise, any rows that are encountered that have been deleted by in-flight uncommitted transactions will be included. In essence, the select statement is only looking at the rows that existed in the table before the in-flight updating (insert and/or delete) transaction started.
As mentioned previously, access to currently committed updates (i.e. accessing the “old”, or currently committed, version of a row before it was updated) is not yet allowed, and lock waits will continue to occur whenever rows that have been updated by in-flight uncommitted transactions are encountered. However, it is not unreasonable to assume that IBM is working on this restriction, and that it will be allowed in some future release or update to DB2.
Access to currently committed data is enabled by a new BIND parameter for packages and plans, and also by the use of new keywords during the PREPARE of dynamic SQL statements.
