DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to identify the deadlock victim process?

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

Detecting and Ending Deadlocks 

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/

http://www.sql-server-performance.com/tips/deadlocks_p1.aspx

Analyzing Deadlocks with SQL Server Profiler

SQL Essentials: Using TRY/CATCH to Resolve Deadlocks in SQL Server 2005

SQL Server Profiler Graphical Deadlock Chain

Using Extended Events to troubleshoot SQL Server issues

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE