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.
About this task
The
guidelines provided here can help you to resolve the lock timeout
problem you are experiencing and help you to prevent such future incidents.
Procedure
Use the following steps to diagnose the cause of the
unacceptable lock timeout problem and to apply a remedy:
- Obtain information from the lock event monitor
or administration notification log about all tables where agents are
experiencing lock timeouts.
- 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 that 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.
What to do next
Rerun the application or applications to ensure that
the locking problem has been eliminated by checking the administration
notification log for lock-related entries or checking the lock wait
and lock wait time metrics for the appropriate workload, connection,
service subclass, unit of work, and activity levels.