A deadlock is created when two applications lock data that
is needed by the other, resulting in a situation in which neither
application can continue executing without the intervention of the
deadlock detector. The deadlock slows down the participant transaction
while it waits for deadlock detection, wastes system resources by
rolling back the victim transaction, and causes extra system work
and transaction log access during the whole process. You likely have
a deadlock problem if you are experiencing an increased number of
deadlocks than the baseline number and transactions are being re-executed.
Before you begin
In
general, any observed deadlock is considered abnormal. To be able
to objectively assess that your system is demonstrating abnormal behavior
which can include processing delays and poor performance, you must
have information that describes the typical behavior (baseline) of
your system. A comparison can then be made between your observations
of suspected abnormal behavior and the baseline. Collecting baseline
data, by scheduling periodic operational monitoring tasks, is a key
component of the troubleshooting process. For
more detailed information about establishing the baseline operation
of your system, see: "Operational monitoring of system performance".
For
instructions about how to monitor deadlock locking events, see: Monitoring locking events.
About this task
- Diagnosis
- A deadlock is created when two applications lock data that is
needed by the other, resulting in a situation in which neither application
can continue executing without the intervention of the deadlock detector.
The victim application has to re-execute the transaction from the
beginning after the system automatically rolls back the previous deadlocked
transaction. Monitoring the rate at which this happens helps avoid
the case where many deadlocks drive significant extra load on the
system without the DBA being aware.
- Indicative signs
- Look for the following indicative signs of deadlocks:
- One or more applications are occasionally re-executing transactions
- Deadlock message entries in the administration notification log
- Increased number of deadlocks displayed for the deadlocks monitor
element
- Increased number of roll backs displayed for the int_deadlock_rollbacks monitor
element
- Increased amount of time an agent spends waiting for log records
to be flushed to disk which is displayed for the log_disk_wait_time monitor
element
- What to monitor
- The cost of a deadlock varies, and is directly proportional to
the length of the rolled-back transaction. All the same, any deadlock
generally indicates a problem.
There are essentially three approaches
to detecting deadlock events:
- Set a locking event monitor and set the mon_deadlock database
configuration parameter to capture details on all deadlock events
that occur database-wide
- Monitor the administration notification log for deadlock messages
and basic information that accompanies them
Note: To enable deadlock
messages to be written to the administration notification log file,
set the mon_lck_msg_lvl database configuration
parameter to a value of 2.
- Monitor the indicator monitoring elements by way of a table function
Most users adopt the first approach. By monitoring the key indicator
monitor elements to detect when a deadlock occurs, users can then
obtain detailed information by checking the information collected
by the event monitor.
- These are the key indicator monitoring elements:
- deadlocks value is non-zero
- int_deadlock_rollbacks shows increase in
number of roll backs due to deadlock events
- log_disk_wait_time shows increase in amount
of time agents spend waiting for logs to be flushed to disk
If you have observed one or more of the indicative
signs listed here, then you are likely experiencing a problem with
deadlocks. Follow the link in the "What to do next" section to
resolve this issue.
What to do next
After
having diagnosed that deadlocks are likely causing the problem you
are experiencing, take steps to resolve the issue: Resolving deadlock problems
DB2 Version 10.1 for Linux, UNIX, and Windows