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.
About this task
The
guidelines provided here can help you to resolve the lock wait 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 wait problem and to apply a remedy:
- Obtain information from the administration notification
log about all tables where agents are spending long periods of time
waiting for locks.
- 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.
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.