Hi,

We have a setup where we are testing 3 concurrent users who are trying to delete some rows from the same table based on some different predicates, however after a few row level locks the same gets escalated to a table level lock in the 'X' exclusive mode and eventually one of the transactions rollsback.

We have set the DB2_RR_TO_RS=Yes. However the same still persists.

We have also set locktimeout to 300 seconds and maxlocks to 75 and lockist to approx 100 MB.

Does any one have any clues to avoid the same.

Regards

nitin.

What version and fixpak are you running?

Hi Marcus,

We are running Fix Pack 3.

I have increased the locklist to 60000 however it still escates the rows

Any pointers??

nitin.

If that is version 8 then you are up to date. Version 7 has some issues with locking that are improved in version 8.

Any chance that you have "allow reverse scans" on indexes? This can cause contention.

I am not sure how these statements are executed, but you might try CS isolation level.

Hi ,

How do we permanently alter the ISOLATION LEVEL for the database.

how does the change isolation level command work, is it at the DBLEVEL or only for that cli connection??

Regards

Nitin

I discussed that in your other thread:

http://www.dbforums.com/t961387.html

You can change the default isolation level for CLI, but it can be overriden by the other factors mentioned in my response.

Hello Everyone

I have now changed the Isolation Level to RS and increased the maxlocks to 77 and locklist to 1250 however I am still facing a lock escalation and table level locks when I have 3 concurrent users. Any other pointers to the parameters to be checked.

Regards

Nitin.

Are you sure it is lock escalation and not a timeout or deadlock (deadly embrace)? Increasing the locktimeout will not help a deadlock.

Can you post table and index DDL and the 3 delete statements that are causing the problem?

Hi Marcus,

I've managed to overcome the lock escalation problem by setting the Isolation to RS and increasing the Maxlocks to 77 and Locklist to 1252. However now it is giving an error which says that there is a deadlock or a timeout, however given the nature of our delete statements we know for sure that that will never encounter a deadlock.

The current value for Locktimeout is -1 and Dlchcktimeout is 30000 ms.

Any clues to what can be further changed.

All your comments have been really helpful. Thanks a ton!!!!

Regards

Nitin.