Hi,

I face some locking problem in production database. I delete all records in a table by issuing 4 different delete sqls in 4 sessions, so that each session delete a subset of the table. However, 2 of the 4 sessions are locktimeout.

It is very likely caused by next key exclusive lock. However, I checked the registry parameter db2_rr_to_rs that it is already set to yes. I know this setting takes effect only after instant restart. I am not sure if we have restarted the instance after setting this parameter to yes, and it is impossible to restart the instance at the moment (as it is a 7 x 24 production database). How can I know if db2_rr_to_rs=yes is taking effect?

Also, I tried getting snapshot for locks report but it doesn't show that an application is waiting for lock which is next key exclusive locked by other application(it doesn't show the hang session in lock-wait status), and the locking application shows only type X lock on deleted row as well as row next to the deleted row. How can I get more information on those locks?

Are that any other case apart from next key exclusive lock which may have outcome similar to the problem I described here?

Thanks!

Can anyone help me? Thanks!

Is this version 7 or 8? I believe that the next key locking problem (on indexes) was fixed with type 2 indexes in Version 8.

Your title does not exactly match the text of your question. I don't believe that isolation level would come into play on delete statements. Isolation level determines how long Read locks are held, not Update locks.

You might be experiencing lock escalation from row to table level. This is usually indicated in the db2dia.log file. To discourage lock escalation, increase the locklist parm (amount of memory for storing locks) by a factor of 10, and increase the maxlocks parameter to at least 50 (percent). Maxlocks determines the percent of locks that can be held by a single application before lock escalation takes place.

It is V7 database. I have already set maxlocks to 100 and locklist to 16384. However, when I get snapshot for locks, I get the following error:

Tue Sep 14 11:37:57 HKG 2004
SQL0973N Not enough storage is available in the "QUERY_HEAP" heap to process
the statement. SQLSTATE=57011

I posted another thread to query on this.

Do you have any idea? Thanks.

I suppose this is db2_rr_to_rs setting problem because when I use another database to simulate the case, I get fail result with db2_rr_to_rs=no, and I solve the problem when I set db2_rr_to_rs=yes.

If that is true, then there is probably some other SQL Select statement besides the deletes that is causing the lock contention.

I checked db2diag.log and not escalation is found.