This table in our DB2 (DB2/AIX64 8.2.3) has a large amount of rows (>10 million). During a back-end program developing, I found a lock-wait situation that will potentially affect the performance of our application. Could somebody here help me find a solution to solve this issue? Thanks in advance!

The issue is: after process A has locked one row by an update statement, process B isn't able to read some other rows by a select statement until process A commits.

The simulation from 2 command line connections is like the following:

connection A:
>db2 +c "UPDATE TABLE_E SET COL_E = CURRENT TIMESTAMP WHERE COL_D = 'AN04' AND COL_C = 2"
DB20000I The SQL command completed successfully.

connection B:
>db2 "SELECT * FROM TABLE_E WHERE COL_A='22222' AND COL_C = 4"
hangs here!!!

connection A:
>db2 commit
DB20000I The SQL command completed successfully.

connection B:
query result gets displayed immediately!!!

Note:
1) Uncommitted Read (UR) for process B is not an option in our application.
2) The row that process A trying to update does have COL_A value '22222'. But apparently it wont be queried out by process B because of different values of COL_C;
3) If I change process B the where clause COL_A a different value say '33333', the lock-wait situation is gone;
4) The TABLE_E has 2 indexes: one is a clustering UNIQUE INDEX ON (COL_A,COL_B DESC), the other is a UNIQUE INDEX ON (COL_C,COL_D);
5) The TABLE_E has PRIMARY KEY (COL_A,COL_B), there are other 2 tables REFERENCES TABLE_E.
6) Snapshot on connection B shows:

Application handle = 565
Application status = Lock-wait
......
ID of agent holding lock = 566
......
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Next Key Share (NS)
Name of tablespace holding lock = XXXXXXX
Schema of table holding lock = XXXX
Name of table holding lock = TABLE_E

What about ReadStability for process B? Can you run
db2set DB2_RR_TO_RS=YES
and repeat your selects??

After I ran
db2set DB2_RR_TO_RS=YES
and tested the scenario again, the outcome was the same.

I read somewhere that I needed to restart the db2 instance after I reset DB2_RR_TO_RS. If this is true, I'll discuss this with our DBA. But first, what's the downside of setting DB2_RR_TO_RS to be YES?

Thanks.

You are changing the isolation level from RepeatableRead to ReadStability at the server level. Most of the applications require the less restrictive RS.

Try also db2set DB2_EVALUNCOMMITTED=ON

DB2_RR_TO_RS=YES has no effect in version 8 if you have type 2 indexes (the only indexes in V8 that are type 1 are those converted from version 7 and never reorged).

n_i has the correct solution for V8.2+. DB2_EVALUNCOMMITTED=ON will allow the second query to evaluate the uncommited update and bypass the row if it does not match the predicate of the second query. If the predicate does match, then it will wait for the lock to be released as it did before. There are some unusual applications where using this registery value could be a problem, but not many.

The DB2_SKIPINSERTED registery value should also be used and should be the default for virtually every application (unless the application does deletes and inserts instead of an update).

DB2_SKIPDELETED can also be used to improve concurrency.

Thank you all!
I'll discuss with our DBA about the upside/downside of resetting some of the registery values mentioned here. I'll run the tests again and keep you posted.

Our DBA says that DB2_EVALUNCOMMITTED is originally ON in all our db2 instances. He turned on the other 3 mentioned here, restarted the instance, and I did the test again. The lock-wait situation remains.

This is the result of command 'db2set' on our test database where we run all the tests.

> db2set
DB2_SKIPINSERTED=ON
DB2_EVALUNCOMMITTED=ON
DB2ROUTINE_DEBUG=ON
DB2_SKIPDELETED=ON
DB2BIDI=no
DB2_RR_TO_RS=YES
DB2COMM=tcpip


I am thinking of adding a new index on (COL_A, COL_C). This would avoid process B doing a table scan and process B would not run into row lock!???

What do you think? Any other suggestions? Thanks!

If you have a small table, then DB2 may not use any of the indexes anyway because it is faster to do a table scan. Table scans can definitely lead to lock contention (as you suggested).

The solution is to alter the tables to volatile, which will tell DB2 to use indexes even on small tables, which will usually minimize lock contention. If volatile does not help, then you might try to add the new indexes.

Try change isolation level for session B with such way:
SELECT * FROM TABLE_E WHERE COL_A='22222' AND COL_C = 4 WITH UR

Thanks gardenman,
Although our application doesn't allow Uncommitted Read (UR) originally, I'll review the design and think of the possibility again for some queries.

I did created an identical table with all identical indexes and inserted less than 10 rows (the table where my original tests based on has more than 10 million rows.) Identical test on the small table doesn't give me the lock-wait issue. Can somebody explain this to me please? Thanks!

I think EXPLAIN will explain it better than anybody else because it really depends on your system configuration.

If you never ran runstats on the table, then DB2 would use the default stats which would likely use an index. Alter the table to volatile to ensure that it always uses default stats even when you do a runstats.

Please report back your results of the alter table set volatile.

I am asking our DBA to alter the table set volatile for a test. Mean while, below is the EXPLAIN on locks. "db2pd -locks" is the command that I can come up with.
There are existing applications using these two existing indexes: the clustering UNIQUE INDEX ON (COL_A,COL_B DESC), and the UNIQUE INDEX ON (COL_C,COL_D). These two indexes will be there forever. This question may be silly. Did process B (where clause on COL_A and COL_C) use these two indexes? If the answer is YES, may I say "table scan (i.e. the test on the small size table) didn't cause the lock-wait, index scan (i.e. the original test) did cause the lock-wait"? Hope I am wrong.

After process A did the update
> db2pd -db XXXXXXX -locks
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
0x07800000202F9340 18 0039000403DB311B0000000052 Row ..X G 18 1 0 0x0000 0x40000000
0x07800000202F2080 18 53514C4445464C540763DD2841 Internal P ..S G 18 1 0 0x0000 0x40000000
0x07800000202FB200 18 53514C4332453036C8324ABC41 Internal P ..S G 18 1 0 0x0000 0x40000000
0x07800000202F4DC0 18 00390004000000000000000054 Table .IX G 18 1 0 0x0000 0x40000000

After process B hung on the select
> db2pd -db XXXXXXX -locks
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
0x07800000202F81C0 20 00000003000000010001550056 Internal V ..S G 20 1 0 0x0000 0x40000000
0x07800000202F9340 18 0039000403DB311B0000000052 Row ..X G 18 1 0 0x0000 0x40000000
0x07800000202FAA40 20 0039000403DB311B0000000052 Row .NS W 18 1 0 0x0000 0x00000008
0x07800000202F2080 18 53514C4445464C540763DD2841 Internal P ..S G 18 1 0 0x0000 0x40000000
0x07800000202F7480 20 53514C4445464C540763DD2841 Internal P ..S G 20 1 0 0x0000 0x40000000
0x07800000202FB200 18 53514C4332453036C8324ABC41 Internal P ..S G 18 1 0 0x0000 0x40000000
0x07800000202F3080 20 53514C4332453036C8324ABC41 Internal P ..S G 20 1 0 0x0000 0x40000000
0x07800000202F4DC0 18 00390004000000000000000054 Table .IX G 18 1 0 0x0000 0x40000000
0x07800000202FA4C0 20 00390004000000000000000054 Table .IS G 20 1 0 0x0000 0x00000008

After process A did the commit, all locks were gone.

This part shows that process B attempts to read the row locked by process A:As to why is this happening you'll need to come up with some other command to explain the execution plans, something like