Shulou(Shulou.com)06/01 Report--

Preface

Recently, I often encounter deadlock victim when writing programs, and I look confused every time. Studied how to track, write it down and write it down. The introduction in this article is very detailed, which has a certain reference and learning value for everyone. I won't say much below. Let's take a look at the detailed introduction.

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')

The first section of sql, first run to update only the table1 part

USE testdb;-- Transaction1BEGIN TRAN UPDATE table1SET student_name = student_name + 'Transaction1'WHERE id IN UPDATE table2SET student_name = student_name +' Transaction1'WHERE id = 1 COMMIT TRANSACTION

The second section of sql, which only runs the update table2 part

USE testdb;-- Transaction2BEGIN TRAN UPDATE table2SET student_name = student_name + 'Transaction2'WHERE id = 1 UPDATE table1SET student_name = student_name +' Transaction2'WHERE id IN

Run again, the first section of sql updates table2, run the second section of sql to update table1, and the deadlock problem returns.

Talk about how to track deadlocks:

1. Using the trace log trace, execute the following sql to 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)

The following figure is the output of 1204.

The following figure is the output of 1222.

two。 Use sql server profiler for tracking

Click Tools-> sql server profiler to select the sql locks template

Run will be automatically captured when deadlock occurs. Click dead lock paragraph to view deadlock

3. With extended event tracking, the method applies only to the sql server 2012 version, not the 08r2 version.

Click Management-> Extended Events-> system health-> package0.event_file

Enter deadlock enter, you can click details to save the content as a xdl file and then open it, or click deadlock to view the picture

4. Use windows performance counters to detect deadlocks and then query in sql

Command line input: perfmon or perfmon / sys

Select an 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'

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.