Detailed description of SQL Deadlock Detection Methods, detailed description of SQL lock Detection

Detailed description of SQL Deadlock Detection Methods, detailed description of SQL lock Detection

The deadlock in SQL server refers to the state in which processes are permanently blocked. The following describes how to detect SQL server deadlocks.

A deadlock is a state in which processes are permanently blocked. SQL can detect a deadlock and terminate a transaction to intervene in the SQL server deadlock State.

Step 1: create two test tables: goods_sort and goods.

Table goods_sort: Create and write Test Data

If exists (SELECT name FROM sysobjects WHERE name = 'goods _ sort 'AND xtype = 'U') drop table dbo. goods_sort -- create table dbo. goods_sort (iSortID int not nullconstraint PK_iSortID primary keyidentity (1001,1), sSortName NVARCHAR (20) not null) goinsert into dbo. goods_sort VALUES ('apparel ') insert into dbo. goods_sort VALUES ('handbags ') insert into dbo. goods_sort VALUES ('shoes ') insert into dbo. goods_sort VALUES ('Jewelry ') insert into dbo. goods_sort VALUES ('beauty ') GO

Table goods: Create and write Test Data

If exists (SELECT name FROM sysobjects WHERE name = 'goods 'AND xtype = 'U') drop table dbo. goods; -- create table dbo. goods (iID int not nullconstraint PK_iID primary keyidentity (1,1), iGoodsID varchar (20) not null, sGoodsName nvarchar (100) not null, iGoodTotal int not nullconstraint limit DEFAULT (0 ), iPrice int not nullconstraint DF_iPrice DEFAULT (0), iPriceTotal int not null, iSortID int not null, tAddDate smalldatetime not nullconstraint DF_tAddDate DEFAULT getdate () goinsert into dbo. goods (iGoodsID, sGoodsName, iGoodTotal, iPrice, iPriceTotal, iSortID) VALUES ('r6001', 'slimming down jacket ', 20,200,400) insert into dbo. goods (iGoodsID, sGoodsName, iGoodTotal, iPrice, iPriceTotal, iSortID) VALUES ('r6002', 'padded undercoats ', 20,300,600) insert into dbo. goods (iGoodsID, sGoodsName, iGoodTotal, iPrice, iPriceTotal, iSortID) VALUES ('bb7001', 'Little Yellow leather saddle package ', 30,100,300) insert into dbo. goods (iGoodsID, sGoodsName, iGoodTotal, iPrice, iPriceTotal, iSortID) VALUES ('bb7002 ', 'cross stitch liusu Bao', 50,150,750) GO

Step 2: create two transactions that will produce deadlocks

Transaction 1:

Set nocount on; SET XACT_ABORT ON; GO -- use TRY-CATCH to enable the code to run begin trybegin tranupdate dbo if an error occurs. goods_sort SET sSortName = 'Women's shoes 'where iSortID = 1003; WAITFOR delay' 00: 00: 05 '; UPDATE dbo. goods SET sGoodsName = 'fat catch' WHERE iID = 2; commit tranend trybegin catchif (XACT_STATE () =-1) rollback tran; -- ERROR_NUMBER () IF the value is 1205, the deadlock occurs. IF (ERROR_NUMBER () = 1205) PRINT 'transaction 1 has deadlocked '-- write SQL Server logs or return an error to the application END CATCHSELECT iID, sGoodsName FROM dbo. goods WHERE iID = 2; SELECT iSortID, sSortName FROM dbo. goods_sort WHERE iSortID = 1003; GO

Transaction 2:

Set nocount on; SET XACT_ABORT ON; GO -- use TRY-CATCH to enable the code to run begin trybegin tranupdate dbo if an error occurs. goods SET sGoodsName = 'slim down jacket 'where iID = 2; WAITFOR delay' 00: 00: 05 '; UPDATE dbo. goods_sort SET sSortName = 'men's shoes 'WHERE iSortID = 1003; commit tranend trybegin catchif (XACT_STATE () =-1) rollback tran; -- ERROR_NUMBER () IF the value is 1205, the deadlock occurs. IF (ERROR_NUMBER () = 1205) PRINT 'transaction 2 has deadlocked '-- write SQL Server logs or return an error to the application END CATCHSELECT iID, sGoodsName FROM dbo. goods WHERE iID = 2; SELECT iSortID, sSortName FROM dbo. goods_sort WHERE iSortID = 1003; GO

Then run transaction 1 and run transaction 2 immediately. In this case, a deadlock occurs in a transaction and the modification is unsuccessful. Another transaction is completed.

First: Use TRY. CATCH to allow the exception-producing transactions to continue to complete the subsequent code.

Second, use waitfor delay to generate a deadlock environment.

Third: Use ERROR_NUMBER () to determine whether a transaction has occurred.

Point 4: When a deadlock occurs, write the SQL Server log or return the application to write the log. It is easy to check logs and find deadlocks and make corresponding modifications.

The preceding content introduces the SQL Deadlock Detection Method.

Articles you may be interested in:

• In-depth analysis of mssql in high frequency, high concurrency access key to find deadlocks
• MySQL deadlock analysis and solution example
• SQL Server deadlock monitoring
• Analysis and Induction of deadlock logs caused by MySQL Innodb tables
• Use sys. sysprocesses to check the blocking and deadlock of SqlServer
• Solutions to SQL Server table deadlock
• SQL application in SQL2008-deadlock (Deadlocking)
• Deadlock description in SQLServer