|
The following message is given when the server resolves a deadlock situation:
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 55) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
To see what was running in Process ID 55 (just an example), run immediately after the deadlock before the spid is recycled:
DBCC INPUTBUFFER(55)
Deadlocks can be traced by turning on two specific flags:
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
Deadlocks trace output can be examined in SQL Server log.
In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.
DEADLOCK PREVENTION
Deadlocks can be prevented by one or more of the following methods:
Adding missing indexes to support faster queries
Dropping unnecessary indexes which may slow down INSERTs for example
Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
Adding index hints to queries
Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
Modifying the stored procedures to access tables in a similar pattern
Keeping transactions as short and quick as possible: "mean & lean"
Removing unnecessary extra activity from the transactions like triggers
Removing JOINs to Linked Server (remote) tables
Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables
Setting MAXDOP=1 solves deadlocking in some cases
SQL SERVER DEADLOCK SIMULATION
-- Activate deadlock tracing
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
-- Deadlock simulation - CONNECTION (session) 1
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
BEGIN TRANSACTION
UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
COMMIT TRANSACTION
END
-- Deadlock simulation - CONNECTION (session) 2
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
BEGIN TRANSACTION
UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
COMMIT TRANSACTION
END
/* Messages
(4000 row(s) affected)
Msg 1205, Level 13, State 56, Line 7
Transaction (Process ID 60) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
*/
/* SQL Server ERROR LOG
2019-03-02 05:44:58.59 spid55 DBCC TRACEON 1204, server process ID (SPID) 55. This is an informational message only; no user action is required.
2019-03-02 05:44:58.59 spid55 DBCC TRACEON 3605, server process ID (SPID) 55. This is an informational message only; no user action is required.
2019-03-02 05:45:48.09 spid4s Deadlock encountered .... Printing deadlock information
2019-03-02 05:45:48.09 spid4s Wait-for graph
2019-03-02 05:45:48.09 spid4s
2019-03-02 05:45:48.09 spid4s Node:1
2019-03-02 05:45:48.12 spid4s OBJECT: 6:402100473:0 CleanCnt:3 Mode:X Flags: 0x0
2019-03-02 05:45:48.12 spid4s Grant List 1:
2019-03-02 05:45:48.12 spid4s Owner:0x10195FA0 Mode: X Flg:0x0 Ref:3 Life:02000000 SPID:62 ECID:0 XactLockInfo: 0x2A4CCD0C
2019-03-02 05:45:48.12 spid4s SPID: 62 ECID: 0 Statement Type: UPDATE Line #: 7
2019-03-02 05:45:48.12 spid4s Input Buf: Language Event: -- Deadlock simulation - CONNECTION (session) 2
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
BEGIN TRANSACTION
UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
COMMIT T
2019-03-02 05:45:48.12 spid4s Requested By:
2019-03-02 05:45:48.12 spid4s ResType:LockOwner Stype:'OR'Xdes:0x2A4CC250 Mode: IX SPID:55 BatchID:0 ECID:0 TaskProxy:(0x189CA378) Value:0x16bb9420 Cost:(0/6371044)
2019-03-02 05:45:48.12 spid4s
2019-03-02 05:45:48.12 spid4s Node:2
2019-03-02 05:45:48.12 spid4s OBJECT: 6:722101613:0 CleanCnt:2 Mode:X Flags: 0x0
2019-03-02 05:45:48.12 spid4s Grant List 1:
2019-03-02 05:45:48.12 spid4s Owner:0x101A9940 Mode: X Flg:0x0 Ref:14 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x2A4CC274
2019-03-02 05:45:48.12 spid4s SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
2019-03-02 05:45:48.12 spid4s Input Buf: Language Event: USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
BEGIN TRANSACTION
UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
COMMIT TRANSACTION
END
2019-03-02 05:45:48.12 spid4s Requested By:
2019-03-02 05:45:48.12 spid4s ResType:LockOwner Stype:'OR'Xdes:0x2A4CCCE8 Mode: IX SPID:62 BatchID:0 ECID:0 TaskProxy:(0x19F2A378) Value:0x1019ba20 Cost:(0/800164)
2019-03-02 05:45:48.12 spid4s
2019-03-02 05:45:48.12 spid4s Victim Resource Owner:
2019-03-02 05:45:48.12 spid4s ResType:LockOwner Stype:'OR'Xdes:0x2A4CCCE8 Mode: IX SPID:62 BatchID:0 ECID:0 TaskProxy:(0x19F2A378) Value:0x1019ba20 Cost:(0/800164)
*/
-- Turn off deadlock tracing
dbcc traceoff (1204, 3605)
go
Related links:
http://www.sqlusa.com/articles/query-optimization/
Detecting and Ending Deadlocks
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
Analyzing Deadlocks with SQL Server Profiler
SQL Server Profiler Graphical Deadlock Chain
Using Extended Events to troubleshoot SQL Server issues
|
