A detailed approach to SQL deadlock detection _mssql

Source: Internet
Author: User
Tags commit rollback

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.

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.