Resolving lock escalation problems

After diagnosing a lock escalation problem, the next step is to attempt to resolve the issue resulting from the database manager automatically escalating locks from row level to table level. The guidelines provided here can help you to resolve the lock escalation problem you are experiencing and help you to prevent such future incidents.

Use the following steps to diagnose the cause of the unacceptable lock escalation problem and to apply a remedy:

1. Gather information from the administration notification log about all tables whose locks have been escalated and the applications involved.
   This log file includes the following information:

   • The number of locks currently held
   • The number of locks needed before lock escalation is completed
   • The table identifier and table name of each table being escalated
   • The number of non-table locks currently held
   • The new table-level lock to be acquired as part of the escalation. Usually, an S or X lock is acquired.
   • The internal return code that is associated with the acquisition of the new table-level lock
2. Use the administration notification log information about the applications involved in the lock escalations to decide how to resolve the escalation problems.
   Consider the following options:

   • You can enable the DB2_AVOID_LOCK_ESCALATION registry variable to return SQL0912N to the application, instead of performing lock escalation. The application then has an opportunity to either COMMIT or ROLLBACK which will release the locks held by this application.
   • Check and possibly adjust either the maxlocks or locklist database configuration parameters, or both. In a partitioned database system, make this change on all database partitions. The value of the locklist configuration parameter may be too small for your current workload. If multiple applications are experiencing lock escalation, this could be an indication that the lock list size needs to be increased. Growth in workloads or the addition of new applications could cause the lock list to be too small. If only one application is experiencing lock escalations, then adjusting the maxlocks configuration parameter could resolve this. However, you may want to consider increasing locklist at the same time you increase maxlocks - if one application is allowed to use more of the lock list, all the other applications could now exhaust the remaining locks available in the lock list and experience escalations.
   • You might want to consider the isolation level at which the application and the SQL statements are being run, for example RR, RS, CS, or UR. RR and RS isolation levels tend to cause more escalations because locks are held until a COMMIT is issued. CS and UR isolation levels do not hold locks until a COMMIT is issued, and therefore lock escalations are less likely. Use the lowest possible isolation level that can be tolerated by the application.
   • Increase the frequency of commits in the application, if business needs and the design of the application allow this. Increasing the frequency of commits reduces the number of locks that are held at any given time. This helps to prevent the application from reaching the maxlocks value, which triggers a lock escalation, and helps to prevent all the applications from exhausting the lock list.
   • You can modify the application to acquire table locks using the LOCK TABLE statement. This is a good strategy for tables where concurrent access by many applications and users is not critical; for example, when the application uses a permanent work table (for example, not a DGTT) that is uniquely named for this instance of the application. Acquiring table locks would be a good strategy in this case as it will reduce the number of locks being held by the application and increase the performance because row locks no longer need to be acquired and released on the rows that are accessed in the work table.

     If the application does not have work tables and you cannot increase the values for locklist or maxlocks configuration parameters, then you can have the application acquire a table lock. However, care must be taken in choosing the table or tables to lock. Avoid tables that are accessed by many applications and users because locking these tables will lead to concurrency problems which can affect response time, and, in the worst case, can lead to applications experiencing lock timeouts.

   • You also need to pay attention to the number of Db2® connections, mainly idle connections. You can see them using the following command:

     db2pd -application -db dbname

     Each idle connection caches about 23 LRB, or locking memory of about 3000 bytes. This means that there are a large number of connections, in some extreme situations, without lowering the maxlocks parameter or increasing the locklist parameter. This could cause you to reach the database limit on locks (locklist) and as a result cause pervasive lock escalation problems.