Diagnosing a deadlock problem
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 themNote: 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
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