When type 2 indexes were introduced in DB2 version 4 it became possible to use row level locking. Now type 2 indexes are a must, but row level locking is still an option and IBM recommends to use row level locking with care.

I have had some practical experiences with some of the consequences of using row level locking. It started with a reasoncode 00C90096 which means that your unit of work has used all possible locks (DSNZPARM NUMLKUS). It was a batch program that encountered this problem and the day before there was no problem. I discovered that the LOCKSIZE of the table had changed to ROW and LOCKMAX was set to 0 which prevents LOCK ESCALATION.

The problem is in short that DB2 acquires a lock for every row instead of every page. If you have 20 rows per page then LOCKSIZE ROW will require 20 times the amount of locks compared to page locking. If you are able to control the commit frequency of your programs then it will be very wise to decrease the commit frequency by a factor of 20 in this example. If you cannot control the commit frequency you must either return to page locking or change the program to accept a commit frequency. My conclusion is that you must remember to decrease the commit frequency with a factor that is equal to the number of rows per page on the table that you introduce row level locking on.

Another solution is to increase NUMLKUS, but this parameter is DB2 sybsystem wide, so this approach may not be the best idea. You can also change the parameter LOCKMAX on the tablespace for the table you have changed to LOCKSIZE ROW. Actually LOCKMAX 0 is a good choice, because other values may result in LOCK ESCALATION which means that DB2 tries to change all row or page level locks to one tablespace lock. If the table are used by many tasks in parallel the LOCK ESCALATION will be fruitless and the result is a lot of timeouts and maybe also deadlocks. And the funny thing is that row level locking is typically introduced on tables with many parallel tasks.

The LOCK ESCALATION mechanism can make things worse when you introduce row level locking because you risk to increase the number of timeouts and deadlocks which is possible the opposite result of what you want to acheive. Therefore it is very important to control the value of LOCKMAX. How to change LOCKSIZE and LOCKMAX and the meaning of the related parameters are described in the SQL reference in the chapter about ALTER TABLESPACE. The link is for DB2 version 7.