Four ways to track SQL Server deadlock

Recently write a program often encounter deadlock victim, every time a face confused force. Study how to track down, write down the record.

Build test data

CREATE DATABASE testdb;
 
GO
 
USE testdb;
 
CREATE TABLE table1
(
id INT IDENTITY PRIMARY KEY,
student_name NVARCHAR(50)
 
)
 
INSERT INTO table1 values ('James')
INSERT INTO table1 values ('Andy')
INSERT INTO table1 values ('Sal')
INSERT INTO table1 values ('Helen')
INSERT INTO table1 values ('Jo')
INSERT INTO table1 values ('Wik')
 
 
CREATE TABLE table2
(
id INT IDENTITY PRIMARY KEY,
student_name NVARCHAR(50)
 
)
 
INSERT INTO table2 values ('Alan')
INSERT INTO table2 values ('Rik')
INSERT INTO table2 values ('Jack')
INSERT INTO table2 values ('Mark')
INSERT INTO table2 values ('Josh')
INSERT INTO table2 values ('Fred') 

First SQL, first run update only table1 section

USE testdb;
 
-- Transaction1
BEGIN TRAN
 
UPDATE table1
SET student_name = student_name + 'Transaction1'
WHERE id IN (1,2,3,4,5)
 
UPDATE table2
SET student_name = student_name + 'Transaction1'
WHERE id = 1
 
COMMIT TRANSACTION 

Second paragraph of SQL, only the update Table2 section is run

  

	

   
USE testdb;
  
-- Transaction2
BEGIN TRAN
  
UPDATE table2
SET student_name = student_name + 'Transaction2'
WHERE id = 1
  
UPDATE table1
SET student_name = student_name + 'Transaction2'
WHERE id IN (1,2,3,4,5)
  
COMMIT TRANSACTION

  

  


  

Run again, the first SQL update table2, run the second SQL update table1, the deadlock problem reappears.

Here's how to track deadlocks:

1. Using the trace log Trace, execute the following SQL open 1222 and 1204 flag, and the deadlock information will be output in the SQL Server log.

DBCC TRACEON (1204,-1) DBCC TRACEON (1222,-1)

is the 1204 output information

is the 1222 output information

2. Using SQL Server Profiler for tracking

Click Tools-SQL Server Profiler to select the SQL locks template

Run is automatically captured when a deadlock occurs, click Dead Lock paragraph to view the deadlock

3. Using extended event tracking, the method applies only to the SQL Server 2012 version and the 08R2 version cannot be used directly.

Click Management, Extended Events->system Health->package0.event_file

Enter deadlock carriage return, you can click Details to save the content as a XDL file and then open, or click Deadlock View image

4. Use Windows performance counters to detect deadlocks and go to SQL query

Command line input: Perfmon or Perfmon/sys

Select instance: SQL server:locks \ number of deadlocks/sec \ \ _total

View in real time:

The following query provides all the deadlocks that have occurred on this server since the last restart:

SELECT Cntr_value as Numofdeadlocks

From Sys.dm_os_performance_counters

WHERE object_name = ' sqlserver:locks '

and counter_name = ' Number of deadlocks/sec '

and instance_name = ' _total '