Deadlocks
When a process must wait to acquire a lock, it is a waiter. The other process that already has the lock (and is causing the waiter to wait) is a blocker which holds the lock.
A deadlock occurs when two processes are mutually waiting and blocking each other. For example, process A acquires a lock on object X, and process B acquires a lock on object Y, and then process A wants a lock on object Y, and process B wants a lock on object X.
This collection contains rows for each time a deadlock is automatically resolved by ASE, such as by terminating the query of the victim process. This resolution may allow the other processes involved in the deadlock to continue.
There is a row for each process involved in the deadlock that was resolved, with columns populated from the perspective of the process. From the example above, process A and process B will each have rows, with different object names and different waiter / holder values, but a common victim process.
(DEADLOCKID, HELDSPID, WAITSPID)
| Name | Data Type | Description |
|---|---|---|
| INSTANCEID | long | Instance ID; 0 = non-CE |
| DEADLOCKID | long | Unique ID for the deadlock |
| VICTIMKPID | long | KPID of the deadlock victim process |
| RESOLVETIME | time | Time when deadlock was resolved |
| OBJECTDBID | long | Database ID of the object involved in the deadlock |
| PAGENUMBER | long | Page number involved in the deadlock |
| ROWNUMBER | long | Row number involved in the deadlock |
| HELDFAMILYID | long | SPID of parent process (in parallel query) of the process holding the lock involved in the deadlock |
| HELDSPID | long | SPID of the process holding the lock involved in the deadlock |
| HELDKPID | long | KPID of the process holding the lock involved in the deadlock |
| HELDPROCDBID | long | Database ID of the procedure of the process holding the lock involved in the deadlock |
| HELDPROCEDUREID | long | Object ID of the procedure of the process holding the lock involved in the deadlock |
| HELDBATCHID | long | Batch ID of the process holding the lock involved in the deadlock |
| HELDCONTEXTID | long | Context ID of the process holding the lock involved in the deadlock |
| HELDLINENUMBER | long | Line number of the process holding the lock involved in the deadlock |
| WAITFAMILYID | long | SPID of parent process (in parallel query) of the process waiting for the lock involved in the deadlock |
| WAITSPID | long | SPID of the process waiting for the lock involved in the deadlock |
| WAITKPID | long | KPID of the process waiting for the lock involved in the deadlock |
| WAITTIME | long | Duration of time, in milliseconds, that the process waited before the deadlock was resolved |
| PARTITIONID | long | ID of the partition involved in the deadlock |
| OBJECTNAME | string(32) | Name of the object involved in the deadlock |
| HELDUSERNAME | string(32) | User name of the process holding the lock involved in the deadlock |
| HELDAPPLNAME | string(32) | Application name of the process holding the lock involved in the deadlock |
| HELDTRANNAME | string(256) | Transaction name of the process holding the lock involved in the deadlock |
| HELDLOCKTYPE | string(32) | Type of lock being held by the process holding the lock involved in the deadlock |
| HELDCOMMAND | string(32) | General command type of the process holding the lock involved in the deadlock |
| WAITUSERNAME | string(32) | User name of the process waiting for the lock involved in the deadlock |
| WAITLOCKTYPE | string(32) | Type of lock being requested by the process waiting for the lock involved in the deadlock |
| DBNAME | string(32) | Database name |
| Attributes: | MDA |
|---|---|
| Minimum database version: | 12.5.0.3 |