The Locks event classes can be used to monitor Microsoft® SQL Server™ lock activity.
| Event class | Description |
|---|---|
| Lock:Acquired | Acquisition of a lock on a resource, such as a data page, has been achieved. For more information about resources that can be locked, see Understanding Locking in SQL Server. |
| Lock:Cancel | Acquisition of a lock on a resource has been canceled (for example, due to a deadlock). |
| Lock:Deadlock | Two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns. For more information, see Deadlocking. |
| Lock:Deadlock Chain | Produced for each of the events leading up to the deadlock. |
| Lock:Escalation | A finer-grained lock has been converted to a coarser-grained lock (for example, a row lock that is converted to a page lock). |
| Lock:Released | A lock on a resource, such as a page, has been released. |
| Lock:Timeout | A request for a lock on a resource, such as a page, has timed out due to another transaction holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT system function and can be set with the SET LOCK_TIMEOUT statement. For more information, see Customizing the Lock Time-out. |
By monitoring the Locks event classes, you can investigate contention issues caused by concurrent users and applications using a database. The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated. For example, an application can be acquiring locks on rows in a table, and then waiting for user input. Because the user input can take a long time to occur, the locks can block other users. In this instance, the application should be redesigned to make lock requests only when needed and not require user input when locks have been acquired.
The Lock:Deadlock, Lock:Deadlock Chain, and Lock:Timeout event classes can be used to monitor when deadlocks and time-out conditions occur, and which objects are involved. This information is useful to determine if deadlocks and time-outs are significantly affecting the performance of your application, and which objects are commonly involved. The application code that modifies these objects can then be examined to determine if changes to minimize deadlocks and time-outs can be made. For more information about reducing deadlocks, see Avoiding Deadlocks.
Because lock events are so prolific, capturing the lock event classes can incur significant overhead on the server being traced and result in very large trace files or trace tables.
These are the event-specific data columns for the Locks event category.
| Event class | Data column | Description |
|---|---|---|
| Lock:Acquired | Binary Data | Resource ID. |
| Duration | Wait between the time the lock request was issued and the time the lock was acquired. | |
| Object ID | ID of the object on which the lock was acquired. | |
| Event Sub Class | Lock mode, such as intent exclusive. | |
| Lock:Cancel | Binary Data | Resource ID. |
| Duration | Wait between the time the lock request was issued and the time the lock was canceled. | |
| Object ID | ID of the object on which the lock was canceled. | |
| Event Sub Class | Lock mode, such as intent exclusive. | |
| Lock:Deadlock | Binary Data | Resource ID. |
| Duration | Wait between the time the lock request was issued and the time the deadlock occurred. | |
| Integer Data | Deadlock number. Numbers are assigned beginning with 0 when the server is started and are incremented for each deadlock. | |
| Object ID | ID of the object in contention. | |
| Event Sub Class | Lock mode, such as intent exclusive. | |
| Lock:Deadlock Chain | Binary Data | Resource ID. |
| Integer Data | Deadlock number. Numbers are assigned beginning with 0 when the server is started, and incremented for each deadlock. | |
| Object ID | ID of the object that was locked. | |
| Event Sub Class | Lock mode, such as intent exclusive. | |
| Lock:Escalation | Object ID | ID of the object on which the lock was escalated. |
| Lock:Released | Binary Data | Resource ID. |
| Duration | Wait between the time the lock request was issued and the time the lock was released. | |
| Object ID | ID of the object on which the lock was released. | |
| Event Sub Class | Lock mode, such as intent exclusive. | |
| Lock:Timeout | Binary Data | Resource ID. |
| Duration | Wait time between the time the lock request was issued and the lock was timed out. | |
| Object ID | ID of the object on which the lock was timed out. | |
| Event Sub Class | Lock mode, such as intent exclusive. |