Resolving lock wait problems

After diagnosing a lock wait problem, the next step is to attempt to resolve the issue resulting from an application having to wait too long for a lock. Guidelines are provided here to help you resolve lock wait problems and assist you in preventing such incidents from occurring in future.

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

1. Obtain information from the administration notification log about all tables where agents are spending long periods of time waiting for locks.
2. Use the information in the administration notification log to decide how to resolve the lock wait problem. There are a number of guidelines that help to reduce lock contention and lock wait time. Consider the following options:
   • 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. This is only an issue if you are using a high isolation level.
   • 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, for example ALTER, CREATE, and DROP statements
     • BIND and REBIND commands
   • After issuing ALTER or DROP DDL statements, run the SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS procedure to revalidate any data objects and the db2rbind command to rebind any packages.
   • 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.
   • Eliminate lock escalations wherever possible.