(DBA) Analyze lock contention
The first place to look is the Lockout History display that shows you a list of the last 100 timeouts and deadlocks that have occurred.
The complete details from these events are available to identify both holder and waiter threads as well as the resources involved.
To analyze locking problems for an application, first look at the accounting record (STRAC) for the counts on maximum page locks, suspensions, lock requests, and timeout/deadlocks. This data is already available with a summary trace and should help you identify which applications are having–or causing–problems. For further information, a detail trace with SQL events is often sufficient. You usually will want to include SQL so you can easily determine which statement is causing the problem, but a great deal of information is available with just the basic detail trace.
If you are still searching for the culprit application, you might want to run a basic detail trace with no further events (not even SQL), but covering all the suspected applications. This type of trace causes much less overhead. You could also use exception filters in the trace request to identify threads with lock suspensions, escalations, lock table requests, or those with a high maximum number of locks held.
The following events are available in DTRAC for any detail trace. Analyze this information before deciding to trace LOCK events.
Event | Description |
|---|---|
LOCK-SUMMARY (display LEVEL=2) | This event appears near the end of the thread processing. It shows the maximum number of page locks held and the highest lock state for each page set (table/index space) accessed. |
LOCK-SUSP (display LEVEL=3) | This event is shown for all lock suspensions. It shows the elapsed time suspended, the page set, the type of entity locked (for example, a data page, index tree, and so on), page number if applicable, and lock state. |
Another source of information is the DB2 timeout and deadlock messages in the Journal Log or MVS console. If the DB2 messages are being written to the Journal, go to Option L and issue a FIND DSNT37 to find any deadlock (DSNT375) or timeout (DSNT376) messages. They are followed by DSNT501I to identify the resource and lock owner that caused the contention. If you have MainView AutoOperator Solutions installed, you can use the DB2DLOK solution to summarize and analyze the deadlock/timeout messages and LOCKD contention snapshots. For more information, see the MainView AutoOPERATOR Solutions Guide.
Related topic