Diagnosing a lock timeout problem
A lock timeout occurs when a transaction, waiting for a resource lock, waits long enough to have surpassed the wait time value specified by the locktimeout database configuration parameter. This consumes time which causes a slow down in SQL query performance. You likely have a lock timeout problem if you are experiencing an increased number of lock timeouts and the locktimeout database configuration parameter is set to a nonzero time value.
Before you begin
general, 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 lock timeout locking events, see: Monitoring locking events.
About this task
- Sometimes, lock wait situations lead to lock timeouts that cause
transactions to be rolled back. The period of time until a lock wait
leads to a lock timeout is specified by the database configuration
parameter locktimeout. Lock timeouts, in excessive
numbers, can be as disruptive to a system as deadlocks. Although deadlocks
are comparatively rare in most production systems, lock timeouts can
be more common. The application usually has to handle them in a similar
way: re-executing the transaction from the beginning. Monitoring the
rate at which this happens helps avoid the case where many lock timeouts
drive significant extra load on the system without the DBA being aware.
- Indicative signs
- Look for the following indicative signs of lock timeouts:
- An application is frequently re-executing transactions
- lock_timeouts monitor element value is climbing
- Lock timeout message entries in the administration notification log
- What to monitor
- Due to the relatively transient nature of locking events, lock event data is most valuable if collected periodically over a period of time, so that the evolving picture can be better understood.
- You can monitor the administration notification log for lock timeout
messages.Note: To enable lock timeout messages to be written to the administration notification log file, set the mon_lck_msg_lvl database configuration parameter to a value of 3.
- Create an event monitor to capture lock timeout data for a workload or database.
- These are the key indicator monitoring elements:
- lock_timeouts value is climbing
- int_rollbacks value is climbing
If you have observed one or more of the indicative signs listed here, then you are likely experiencing a problem with lock timeouts. Follow the link in the
What to do nextsection to resolve this issue.
What to do next
After having diagnosed that lock timeouts are likely causing the problem you are experiencing, take steps to resolve the issue: Resolving lock timeout problems