A detailed approach to SQL deadlock detection _mssql

Deadlock in SQL Server refers to a state of permanent blocking between processes, and the following will show you how to detect SQL Server deadlocks in the hope of helping you.

Deadlock (deadlock) refers to a state where processes are permanently blocked, SQL can detect deadlocks, and choose to terminate one of the transactions to intervene in the SQL Server deadlock state.

The first step: first create two test tables, table goods_sort and goods

Table Goods_sort: Creating and writing Test data

IF EXISTS (SELECT name from sysobjects WHERE name= ' goods_sort ' and xtype= ' U ')
DROP table Dbo.goods_sort
--Create commodity classification table
CREATE TABLE dbo.goods_sort (
isortid int not NULL
CONSTRAINT pk_isortid PRIMARY KEY
IDENTITY (1001,1),
ssortname NVARCHAR () not NULL
)
Go
inserts into Dbo.goods_sort values (' costumes ')
insert into dbo.goods_sort values (' female package ')
INSERT INTO Dbo.goods_sort values (' shoes ')
insert into dbo.goods_sort values (' jewelry ')
insert into dbo.goods_sort values (' Beauty ') Go

Table goods: Creating and writing Test data

IF EXISTS (SELECT name from sysobjects WHERE name= ' goods ' and xtype= ' U ') DROP TABLE dbo.goods; --Create a product table dbo.goods (IID int not NULL CONSTRAINT pk_iid PRIMARY KEY IDENTITY (1,1) igoodsid varchar L, Sgoodsname nvarchar (MB) not NULL, igoodtotal int NOT null CONSTRAINT df_igoodtotal DEFAULT (0), Iprice int NOT null CON STRAINT Df_iprice DEFAULT (0), ipricetotal int not NULL, Isortid int to NOT NULL, tadddate smalldatetime NOT null CONSTRAINT D
F_tadddate DEFAULT getdate ()) Go INSERT into Dbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal,isortid) VALUES (' YR6001 ', ' slimming down jacket ', 20,200,4000,1001) INSERT into Dbo.goods (igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal , Isortid) VALUES (' YR6002 ', ' thickened down jacket ', 20,300,6000,1001) INSERT into Dbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice, Ipricetotal,isortid) VALUES (' BB7001 ', ' Small yellow cowhide Saddle pack ', 30,100,3000,1002) INSERT into Dbo.goods (Igoodsid,sgoodsname, Igoodtotal,iprice,ipricetotal,isortid) VALUES (' BB7002 ', ' Cross stitch tassel bag ', 50,150,7500,1002) go

Step Two: Create two transactions that will cause deadlocks

Transaction 1:

SET NOCOUNT on;
SET xact_abort on;
Go-use Try-catch to cause code errors and continue to run the
begin TRY
begin TRAN
UPDATE dbo.goods_sort SET ssortname= ' Women's Shoes ' WHERE isortid=1003;
WAITFOR DELAY ' 00:00:05 ';
UPDATE dbo.goods SET sgoodsname= ' fatty down jacket ' WHERE iid=2;
COMMIT TRAN End
TRY
BEGIN CATCH
IF (xact_state () =-1)
ROLLBACK TRAN;
A--error_number () value of 1205 indicates a deadlock IF (
error_number () = 1205)
PRINT ' Transaction 1 has a deadlock '
-write SQL The server log either returns an error to the application end
CATCH
SELECT iid,sgoodsname from Dbo.goods WHERE iid=2;
SELECT isortid,ssortname from Dbo.goods_sort WHERE isortid=1003;

Transaction 2:

SET NOCOUNT on;
SET xact_abort on;
Go-use Try-catch to make code error also continue to run
begin TRY
begin TRAN
UPDATE dbo.goods SET sgoodsname= ' skinny down jacket ' WHERE iid= 2;
WAITFOR DELAY ' 00:00:05 ';
UPDATE dbo.goods_sort SET ssortname= ' men's shoes ' WHERE isortid=1003;
COMMIT TRAN End
TRY
BEGIN CATCH
IF (xact_state () =-1)
ROLLBACK TRAN;
A--error_number () value of 1205 indicates a deadlock IF (
error_number () = 1205)
PRINT ' Transaction 2 has a deadlock '
-write SQL The server log either returns an error to the application end
CATCH
SELECT iid,sgoodsname from Dbo.goods WHERE iid=2;
SELECT isortid,ssortname from Dbo.goods_sort WHERE isortid=1003;

Then run transaction 1, then run transaction 2 immediately, in which case a transaction prompts for a deadlock and the modification is unsuccessful. A second transaction is completed.

1th: Use Try.catch to allow transactions that generate exceptions to continue to complete the following code.

2nd: Use WAITFOR delay to produce a deadlock-causing environment.

3rd: Use Error_number () to determine whether transactions occur.

4th: Deadlock occurs, write SQL Server log, or return application to write log. It is easy to check the log to find the deadlock and make the corresponding modification.

The above content to introduce the SQL deadlock detection method, I hope you like.