Resolving lock timeout problems

After diagnosing a lock timeout problem, the next step is to attempt to resolve the issue resulting from an application or applications waiting for locks until the lock timeout period has elapsed. The guidelines provided here can help you to resolve the lock timeout problem you are experiencing and help you to prevent such future incidents.

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

1. Obtain information from the lock event monitor or administration notification log about all tables where agents are experiencing lock timeouts.
2. Use the information in the administration notification log to decide how to resolve the lock timeout problem. There are a number of guidelines that help to reduce lock contention and lock wait time that can result in a reduced number of lock timeouts. Consider the following options:
   • Tune the locktimeout database configuration parameter to a number of seconds appropriate for your database environment.
   • If possible, avoid very long transactions and WITH HOLD cursors. The longer locks are held, the more chance that they cause contention with other applications.
   • It is best practice to commit the following actions as soon as possible:
     • Write actions such as delete, insert, and update
     • Data definition language (DDL) statements, such as ALTER, CREATE, and DROP
     • BIND and REBIND commands
   • Avoid fetching result sets that are larger than necessary, especially under the repeatable read (RR) isolation level. The more that rows are touched, the more locks are held, and the greater the opportunity to run into a lock that is held by someone else. In practical terms, this often means pushing down row selection criteria into a WHERE clause of the SELECT statement, rather than bringing back more rows and filtering them at the application. For example:

     exec sql declare curs for
       select c1,c2 from t 
       where c1 not null;
     exec sql open curs;
     do {
       exec sql fetch curs 
         into :c1, :c2;
     } while( P(c1) != someVar );
     
     ==>
     
     exec sql declare curs for
       select c1,c2 from t 
       where c1 not null
       and myUdfP(c1) = :someVar;
     exec sql open curs;
     exec sql fetch curs 
         into :c1, :c2;

   • Avoid using higher isolation levels than necessary. Repeatable read might be necessary to preserve result set integrity in your application; however, it does incur extra cost in terms of locks held and potential lock conflicts.
   • If appropriate for the business logic in the application, consider modifying locking behavior through the DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED registry variables. These registry variables enable DB2® database manager to delay or avoid taking locks in some circumstances, thereby reducing contention and potentially improving throughput.