Deadlocks
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. Deadlocking is often confused with normal blocking.
When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released.
By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is set. The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock.
Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.
In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. Similarly, transaction T2 has a dependency on transaction T1 for the Supplier table lock resource. Because these dependencies form a cycle, there is a deadlock between transactions T1 and T2.
Deadlock Information Tools
Deadlock Extended Event
Starting with SQL Server 2012 (11.x), the xml_deadlock_report Extended Event (xEvent) should be used instead of the Deadlock graph event class in SQL Trace or SQL Profiler.
Also starting with SQL Server 2012 (11.x), when deadlocks occur, the system_health session captures all xml_deadlock_report xEvents which contain the deadlock graph.
Because the system_health session is enabled by default, it's not required that a separate xEvent session is configured to capture deadlock information. The deadlock graph captured typically has three distinct nodes (https://www.sqlshack.com/understanding-the-xml-description-of-the-deadlock-graph-in-sql-server/):
victim-list. The deadlock victim process identifier.
process-list. Information on all the processes involved in the deadlock.
resource-list. Information about the resources involved in the deadlock.
Opening the system_health session file or ring buffer, if the xml_deadlock_report xEvent is recorded, Management Studio presents a graphical depiction of the tasks and resources involved in a deadlock:
Trace Flag 1204 and Trace Flag 1222
When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.
Important: Avoid using trace flag 1204 and 1222 on workload-intensive systems that are causing deadlocks. Using these trace flags may introduce performance issues. Instead, use the Deadlock Extended Event(#deadlock_xevent).
Trace Flag 1204 Example:
The following example shows the output when trace flag 1204 is turned on. In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index. The index key in Node 2 is being updated when the deadlock occurs.
Deadlock encountered .... Printing deadlock information Wait-for graph Node:1 RID: 6:1:20789:0 CleanCnt:3 Mode:X Flags: 0x2 Grant List 0: Owner:0x0315D6A0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6 Input Buf: Language Event: BEGIN TRANSACTION EXEC usp_p2 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0 Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868) Node:2 KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0 Grant List 0: Owner:0x0315D140 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4 SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6 Input Buf: Language Event: BEGIN TRANSACTION EXEC usp_p1 Requested By: ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380) Victim Resource Owner: ResType:LockOwner Stype:'OR'Xdes:0x04D9E258 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)
Trace Flag 1222 Example:
The following example shows the output when trace flag 1222 is turned on. In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index. In the second table, the index key is being updated when the deadlock occurs.
deadlock-list deadlock victim=process689978 process-list process id=process6891f8 taskpriority=0 logused=868 waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444 transactionname=user_transaction lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0 lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:42.733 lastbatchcompleted=2005-09-05T11:22:42.733 clientapp=Microsoft SQL Server Management Studio - Query hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user isolationlevel=read committed (2) xactid=310444 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 executionStack frame procname=AdventureWorks2016.dbo.usp_p1 line=6 stmtstart=202 sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000 UPDATE T2 SET COL1 = 3 WHERE COL1 = 1; frame procname=adhoc line=3 stmtstart=44 sqlhandle=0x01000600856aa70f503b8104000000000000000000000000 EXEC usp_p1 inputbuf BEGIN TRANSACTION EXEC usp_p1 process id=process689978 taskpriority=0 logused=380 waitresource=KEY: 6:72057594057457664 (350007a4d329) waittime=5015 ownerId=310462 transactionname=user_transaction lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077 lastbatchcompleted=2005-09-05T11:22:44.077 clientapp=Microsoft SQL Server Management Studio - Query hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user isolationlevel=read committed (2) xactid=310462 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 executionStack frame procname=AdventureWorks2016.dbo.usp_p2 line=6 stmtstart=200 sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000 UPDATE T1 SET COL1 = 4 WHERE COL1 = 1; frame procname=adhoc line=3 stmtstart=44 sqlhandle=0x01000600d688e709b85f8904000000000000000000000000 EXEC usp_p2 inputbuf BEGIN TRANSACTION EXEC usp_p2 resource-list ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2016.dbo.T2 id=lock3136940 mode=X associatedObjectId=72057594057392128 owner-list owner id=process689978 mode=X waiter-list waiter id=process6891f8 mode=U requestType=wait keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2016.dbo.T1 indexname=nci_T1_COL1 id=lock3136fc0 mode=X associatedObjectId=72057594057457664 owner-list owner id=process6891f8 mode=X waiter-list waiter id=process689978 mode=U requestType=wait
Profiler Deadlock Graph Event
This is an event in SQL Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock. The following example shows the output from SQL Profiler when the deadlock graph event is turned on.
Example:
You have a database named Database1. Users report that they experience deadlock issues. You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit.
You view the contents of the Resource List section as shown in the Resource List exhibit.
You view deadlock information as shown in the Deadlock List exhibit.
You need to identify the causes of the deadlock.
Answer:
Note: Read committed specifies that statements cannot read data that has been modified but not committed by other transactions. The following three lock types are used for row- and page-level locking:
Shared (S)
Exclusive (X)
Update (U)
An exclusive lock reserves a page or row, but not a whole table, for the exclusive use of a single transaction.
Taskpriority is 0, which is the default priority. The transaction could be chosen as the victim if other transactions have a priority higher than 0.
References:
https://www.sqlservercentral.com/forums/topic/deciphering-deadlock-resource-list https://www.sqlshack.com/what-is-a-sql-server-deadlock/ https://logicalread.com/sql-server-lock-modes-mc03/#.XULCdm8zaUk