Four ways to track SQL Server deadlock

Source: Internet
Author: User

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 DATABASETestDB;GO  UseTestDB;CREATE TABLEtable1 (idINT IDENTITY PRIMARY KEY, Student_nameNVARCHAR( -) ) INSERT  intoTable1Values('James')INSERT  intoTable1Values('Andy')INSERT  intoTable1Values('Sal')INSERT  intoTable1Values('Helen')INSERT  intoTable1Values('Jo')INSERT  intoTable1Values('Wik')  CREATE TABLEtable2 (IDINT IDENTITY PRIMARY KEY, Student_nameNVARCHAR( -) ) INSERT  intoTable2Values('Alan')INSERT  intoTable2Values('Rik')INSERT  intoTable2Values('Jack')INSERT  intoTable2Values('Mark')INSERT  intoTable2Values('Josh')INSERT  intoTable2Values('Fred')

First SQL, first run update only table1 section

 UseTestDB;--Transaction1BEGIN TRAN UPDATEtable1SETStudent_name=Student_name+ 'Transaction1'WHEREIdinch(1,2,3,4,5) UPDATEtable2SETStudent_name=Student_name+ 'Transaction1'WHEREId= 1 COMMIT TRANSACTION

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

Use TestDB; --Transaction2begin TRAN update Table2set student_name = student_name + ' Transaction2 ' WHERE id = 1 Update table1set stude Nt_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 '

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: