Locking scheme and promotion - changing the HWM and LWM
Posted on 2006-05-26
We have a table that has about 500K rows, with a locking scheme of ALLPAGES. We'd like to change this to DATAROWS and increased the HWM and LWM to 5000. Currently they are set to 200, which is the default. Do you see any problems or issues to consider with this change?
The reason for this change is that we've seen many page locks and an occasional table lock on 1 table. This has blocked other users. There is a program that deletes and inserts to this table. It has to do that for the entire set of rows before doing a COMMIT. According to the user, the number of rows varies greatly from 10 to more than 5000.
Btw, the number of locks is configured at 35000.