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.
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.
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!!!!