SQL Server – Lock Timeout
Lock Timeout event indicates that a request for a lock on a resource, such as a page, has timed out because another transaction is holding a blocking lock on the required resource. Time-out is determined by the @@LOCK_TIMEOUT system function.
We also can be set timeout time using SET LOCK_TIMEOUT statement there duration milliseconds and 1000 milliseconds means 1 second.
For example I have set timeout as a1000 milliseconds (1 seconds) and create 2 session 1 update saleshead tables(so there exclusive lock create) and saleshead have lotof data so it will take time and same time session 2 selecting data from the same table so it will wait for 1 seconds after that will timeout like that :
Lock Timeout events with a duration of 0 are commonly the result of internal lock probes and are not necessarily an indication of a problem. The Lock Timeout (timeout > 0) event can be used to ignore time-outs with duration of 0.
Timeout ModeID
| 0=NULL – Compatible with all other lock modes (LCK_M_NL) |
| 1=Schema Stability lock (LCK_M_SCH_S) |
| 10=Shared with Intent Exclusive (LCK_M_SIX) |
| 11=Update with Intent Exclusive (LCK_M_UIX) |
| 12=Bulk Update Lock (LCK_M_BU) |
| 13=Key range Shared/Shared (LCK_M_RS_S) |
| 14=Key range Shared/Update (LCK_M_RS_U) |
| 15=Key Range Insert NULL (LCK_M_RI_NL) |
| 16=Key Range Insert Shared (LCK_M_RI_S) |
| 17=Key Range Insert Update (LCK_M_RI_U) |
| 18=Key Range Insert Exclusive (LCK_M_RI_X) |
| 19=Key Range Exclusive Shared (LCK_M_RX_S) |
| 2=Schema Modification Lock (LCK_M_SCH_M) |
| 20=Key Range Exclusive Update (LCK_M_RX_U) |
| 21=Key Range Exclusive Exclusive (LCK_M_RX_X) |
| 3=Shared Lock (LCK_M_S) |
| 4=Update Lock (LCK_M_U) |
| 5=Exclusive Lock (LCK_M_X) |
| 6=Intent Shared Lock (LCK_M_IS) |
| 7=Intent Update Lock (LCK_M_IU) |
| 8=Intent Exclusive Lock (LCK_M_IX) |
| 9=Shared with intent to Update (LCK_M_SIU) |
OwnerID
| 1=TRANSACTION |
| 2=CURSOR |
| 3=SESSION |
| 4=SHARED_TRANSACTION_WORKSPACE |
| 5=EXCLUSIVE_TRANSACTION_WORKSPACE |
TypeID
| 1=NULL_RESOURCE |
| 2=DATABASE |
| 3=FILE |
| 5=OBJECT |
| 6=PAGE |
| 7=KEY |
| 8=EXTENT |
| 9=RID |
| 10=APPLICATION |
| 11=METADATA |
| 12=AUTONAMEDB |
| 13=HOBT |
| 14=ALLOCATION_UNIT |
How to check/ trace Timeout using profiler:
- login in profiler and select tsql_locks or also you can use blank template.
- Now Select Lock Timeout Events form “Events Selection” Tab there events lists come according to your selection template In that case I have selected Blank template like
Select column according to your required and click on OK/Run but like
Recent Comments