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
In
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
- Diagnosis
- 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 next" section
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