Using SQL to detect deadlocks

First step: First create two test tables, table goods_sort and goods

Table Goods_sort: Creating and writing Test data

IF EXISTS(SELECTName fromsysobjectsWHEREName='Goods_sort'  andXtype='U')
    DROP TABLEDbo.goods_sort--Create a Product classification table
CREATE TABLEDbo.goods_sort (Isortidint                 not NULL
        CONSTRAINTPk_isortidPRIMARY KEY
        IDENTITY(1001,1), SsortnameNVARCHAR( -) not NULL
)
GO

INSERT  intoDbo.goods_sortVALUES('Apparel')
INSERT  intoDbo.goods_sortVALUES('Female Bag')
INSERT  intoDbo.goods_sortVALUES('Shoes')
INSERT  intoDbo.goods_sortVALUES('Jewellery')
INSERT  intoDbo.goods_sortVALUES('Beauty')
GO

Table goods: Creating and writing Test data

IF EXISTS(SELECTName fromsysobjectsWHEREName='Goods'  andXtype='U')
    DROP TABLEDbo.goods; --Create a product table
CREATE TABLEDbo.goods (IIDint                 not NULL
        CONSTRAINTPk_iidPRIMARY KEY
        IDENTITY(1,1), Igoodsidvarchar( -) not NULL, Sgoodsnamenvarchar( -) not NULL, Igoodtotalint                 not NULL
        CONSTRAINTDf_igoodtotalDEFAULT(0), Ipriceint                 not NULL
        CONSTRAINTDf_ipriceDEFAULT(0), Ipricetotalint                 not NULL, Isortidint                 not NULL, Tadddatesmalldatetime     not NULL
        CONSTRAINTDf_tadddateDEFAULT getdate()
)
GO

INSERT  intodbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal,isortid)VALUES('YR6001','Slimming down Jacket', -, $,4000,1001)
INSERT  intodbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal,isortid)VALUES('YR6002','Padded down jacket', -, -,6000,1001)
INSERT  intodbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal,isortid)VALUES('BB7001','Small yellow cowhide saddle bag', -, -, the,1002)
INSERT  intodbo.goods (Igoodsid,sgoodsname,igoodtotal,iprice,ipricetotal,isortid)VALUES('BB7002','cross-stitch tassel bag', -, Max,7500,1002)
GO

Step Two: Create two transactions that will cause a deadlock

Transaction 1:

SETNOCOUNT on;
SETXact_abort on;
GO

--use Try-catch to keep code errors and continue to run
BEGINTRYBEGIN TRAN
    UPDATEDbo.goods_sortSETSsortname='Women's Shoes' WHEREIsortid=1003; WAITFORDELAY'00:00:05'; UPDATEDbo.goodsSETSgoodsname='Fat down jacket' WHEREIid=2; COMMIT TRAN
ENDTRYBEGINCATCHIF(Xact_state ()=-1)
    ROLLBACK TRAN; --a error_number () value of 1205 indicates a deadlock has occurred
  IF(Error_number ()= 1205)
    PRINT 'Transaction 1 has a deadlock'
    --writes the SQL Server log or returns an error to the application
ENDCATCHSELECTIid,sgoodsname fromDbo.goodsWHEREIid=2;
SELECTIsortid,ssortname fromDbo.goods_sortWHEREIsortid=1003;
GO 

Transaction 2:

SETNOCOUNT on;
SETXact_abort on;
GO

--use Try-catch to keep code errors and continue to run
BEGINTRYBEGIN TRAN
    UPDATEDbo.goodsSETSgoodsname='Skinny Down jacket' WHEREIid=2; WAITFORDELAY'00:00:05'; UPDATEDbo.goods_sortSETSsortname='Men's Shoes' WHEREIsortid=1003; COMMIT TRAN
ENDTRYBEGINCATCHIF(Xact_state ()=-1)
    ROLLBACK TRAN; --a error_number () value of 1205 indicates a deadlock has occurred
  IF(Error_number ()= 1205)
    PRINT 'Transaction 2 has a deadlock'
    --writes the SQL Server log or returns an error to the application
ENDCATCHSELECTIid,sgoodsname fromDbo.goodsWHEREIid=2;
SELECTIsortid,ssortname fromDbo.goods_sortWHEREIsortid=1003;
GO 

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

1th: Use Try.catch to let the transaction that generates the exception continue to complete the code that follows.

2nd: Use WAITFOR delay to create an environment in which deadlocks occur.

3rd: Use Error_number () to determine whether a transaction has occurred.

4th: A deadlock occurs, writes the SQL Server log, or returns the application to write the log. It is convenient to check the log to find the deadlock and make corresponding changes.

Using SQL to detect deadlocks