I am presently working on solutions to the locking problems encountered by our application (db2 ver 8.1 for solaris).
I looked into the different scenarios in which it's waiting for locks. The most common problem was
- user A performing task s1 - locks rows r10, r11,r12 in table t1
- user B performing task s2 - wants to select data from table t1, but does not care for r10, r11, r12 (since it's work in progress for task s1).
SELECT.... WITH UR is not an option because in doing that your application has to determine what constitues valid data. Second UR in applications involving transactions processing makes me nervous (risk of data integrity issues).
I looked into the following options
DB2_SKIPINSERTED and DB2_SKIPDELETED
and I am very tempted to use them right away. I believe that these options will give my application the ability to select commited data (exclude rows that are locked by some transaction), and thereby drastically improve concurrency.
Since DB2 registry variables impact the entire database, I like to be cautious before I pick a particular feature influenced by updating the registry variables.
I searched the web to read articles on DB2_SKIPINSERTED and DB2_SKIPDELETED. But I did not find too much info.
I read articles that talk about locking and concurrency, and somehow none mention this option.
Could someone tell me the pros and cons of turning on these options ? Is there any reason for not having these options ON for all db instances ? Any good articles that discuss these options ?
When DB2 performs an index or table scan, DB2 will block on a row lock when it comes across an uncommitted row which another transaction has exclusively (X) locked. If this row lock is protecting an uncommitted UPDATE or DELETE action, DB2 cannot process or ignore the row until the outcome of the change is known. Therefore, the CS and RS isolation levels must only process committed data and are not allowed to skip uncommitted data (unless some registry variable settings allow them to, such as DB2_SKIPDELETED or DB2_EVALUNCOMMITTED: the committed version of an uncommitted DELETE is the row in its pre-deleted state, while the committed version of an uncommitted UPDATE is the pre-updated version of the row, both of which are unknown unless a rollback occurs).
While this behavior is correct, when a row is locked due to an uncommitted INSERT -- there are situations where application owners would like DB2 to skip the inserted row that is awaiting a commit as if it where not there (this is possible since the committed pre-version of an uncommitted INSERT is no row at all). For example, with the advent of Business Activity Monitoring or FOSH score carding, C-level executives are looking for more and more up-to-date information. While the Sales component of a FOSH scorecard will return a red/yellow/green light based on its aggregate value, it may be acceptable to leave out an order that is currently in progress when looking at sales activity at the macro level (for example, global sales).
DB2 V8.2.2 introduces the ability for read-only scanners, from applications using the CR and RS isolation levels, to skip inserted records that have not yet been committed by other transactions. This new capability adds more flexibility to concurrency which will help the scalability of some applications. To enable this new feature in DB2 V8.2.2, you use the DB2_SKIPINSERTED registry variable. Although DB2 has the ability to change the isolation level on a per statement basis, the ability to skip uncommitted INSERTs is not available on a per statement level and this setting is considered at compile/bind time.
In DB2 V8.2.2, DB2_SKIPINSERTED=OFF is the default setting. This makes DB2 behave as one would expect: the scanner waits until the INSERT transaction commits or rolls back and then return the data - business as usual. Depending on your applications and the data integrity characteristics associated with the business function, this may or may not be appropriate. For example, consider a business process that involves two applications that use a common table to exchange business information between them -- for example, a credit application and credit vulnerability scoring engine. Application A inserts data into the database based on a Web form and application B reads that data. To speed up credit approvals, as the candidate moves through the credit application form, blocks of information are sent to application B (via the common table) through 'Steps' on the form. As the candidate completes each step in the application process, that information is sent. In this environment, the data must be processed by the second application in the order presented in the table such that if the next row to be read is being inserted by application A, then application B must wait until the INSERT is committed.
If you set DB2_SKIPINSERTED=ON, DB2 will treat the uncommitted INSERTs (for CS and RS isolation levels only) as if they had not yet been inserted. This feature provides increased concurrency without sacrificing isolation semantics. DB2 implements the ability for scanners to skip uncommitted inserted rows versus waiting when in conflict through lock attributes and feedback on lock requests. You can see the INSERT lock attribute in the snapshot output.
Enhanced evaluate uncommitted
DB2 V8.1.4 introduced the DB2_EVALUNCOMMITTED DB2 registry variable. When enabled (=TRUE | ON | YES | 1), it would modify the behavior of read-only queries in DB2 such that it would allow locking to be deferred on an index scan (must be a Type 2 index, this feature is not supported with Type 1 indexes) or table access (this feature is not supported on Range Clustered Tables) until all the predicates that qualified the statement where known. This new registry variable was introduced to optionally improve the concurrency of some applications by essentially allowing read scans to defer or avoid row locking until a data record is known to qualify a particular query. In particular, this feature was driven by the SAP application, but the work is generally applicable to a lock avoidance schemes for a much greater customer base.
Before DB2 V8.1.4 (and without setting this registry variable), DB2 would perform pessimistic locking: it would lock each row visited before validating if the row satisfied the exclusion predicates of the query. This locking was done for both index scans and table access whether or not the row of data was committed or whether it would have been excluded based on the statement's predicates. Consider the simple example of two statements targeted for table T1 - CREATE TABLE T1 (C1 INT). The first statement blocks all other scanners because it holds a lock on the row - DB2 +C INSERT INTO TABLE T1 VALUES (1) - while the second statement is blocked - DB2 SELECT * FROM T1 - until transaction 1 commits or rolls back. But let's assume that the second statement was DB2 SELECT * FROM T1 WHERE C1=2. In this case, transaction 2 would still be blocked even though it isn't interested in any values in column C1=1 (which had not even committed). This series of events happens by default in DB2 since the default isolation level is Cursor Stability (CS) and this isolation level dictates that any row accessed by a query must be locked while the cursor is positioned on that row. Statement 2 cannot obtain a lock on the first row of table T1 until statement 1 releases it's lock that it is using to update the table. It stands to reason that if DB2 knew that the value C1=1 was not part of statement 2's data request (in other words, it evaluated the predicate before locking the row), this blocking could be avoided, as statement 2 would not attempt to lock the first row in the table.
When the evaluate uncommitted behavior is enabled for your DB2 environment, you should be aware that predicate evaluation may occur on uncommitted data. Furthermore, deleted rows are skipped unconditionally on table scan access, while deleted keys are not skipped for type-2 index scans (unless you also set the DB2_SKIPDELETED registry variable). If you were to solely set the DB2_SKIPDELETED registry variable in your environment, DB2 would allow unconditional skipping of deleted records on table scan access and skipping of pseudo-deleted index keys for type-2 index scan access.