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

This article will explain in detail how to analyze the SQL SERVER deadlock in detail. The content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Speaking of SQL SERVER, in fact, this database is widely used in enterprises, which must be mentioned why enterprises widely use SQL SERVER,. NET VS JAVA, we currently have two camps in development, .NET camp and JAVA camp, and generally speaking, .NET developers are very willing to take SQL SERVER as the preferred database, especially in small and medium-sized enterprises, fast development, excellent performance, and a variety of beautiful graphics Let the development and management, become very simple, in the efficiency of today, there are still a large part of enterprises use .NET and SQL SERVER to meet the various needs of enterprises.

Today I want to talk about the SQL SERVER programmer, DBA, no one wants to see the deadlock, DEAD LOCK. This situation shows that there may be some problems in the design and hardware of our entire system. So we have to figure out why deadlocks are formed.

To simplify the instructions, I need to create some tables here

CREATE TABLE Countrys

(

CountryID INT NOT NULL IDENTITY (1,1)

CONSTRAINT PK_Country PRIMARY KEY

CountryName VARCHAR (100)

PopulationSize INT NOT NULL

)

CREATE TABLE Citys

(

CityID INT NOT NULL IDENTITY (1,1)

CONSTRAINT PK_City PRIMARY KEY

CountryID INT NOT NULL

CityName VARCHAR (100) NOT NULL

PopulationSize INT NOT NULL

CountOfHospitals INT NOT NULL

SomeProperty1 VARCHAR (100) NOT NULL

SomeProperty2 VARCHAR (100) NOT NULL

SomeProperty3 VARCHAR (100) NOT NULL

SomeProperty4 VARCHAR (100) NOT NULL

SomeProperty5 VARCHAR (100) NOT NULL

SomeProperty6 VARCHAR (100) NOT NULL

SomeProperty7 VARCHAR (100) NOT NULL

SomeProperty8 VARCHAR (100) NOT NULL

SomeProperty9 VARCHAR (100) NOT NULL

SomeProperty10 VARCHAR (100) NOT NULL

SomeProperty11 VARCHAR (100) NOT NULL

SomeProperty12 VARCHAR (100) NOT NULL

SomeProperty13 VARCHAR (100) NOT NULL

SomeProperty14 VARCHAR (100) NOT NULL

SomeProperty15 VARCHAR (100) NOT NULL

SomeProperty16 VARCHAR (100) NOT NULL

SomeProperty17 VARCHAR (100) NOT NULL

SomeProperty18 VARCHAR (100) NOT NULL

SomeProperty19 VARCHAR (100) NOT NULL

SomeProperty20 VARCHAR (100) NOT NULL

SomeProperty21 VARCHAR (100) NOT NULL

SomeProperty22 VARCHAR (100) NOT NULL

SomeProperty23 VARCHAR (100) NOT NULL

SomeProperty24 VARCHAR (100) NOT NULL

SomeProperty25 VARCHAR (100) NOT NULL

SomeProperty26 VARCHAR (100) NOT NULL

SomeProperty27 VARCHAR (100) NOT NULL

SomeProperty28 VARCHAR (100) NOT NULL

SomeProperty29 VARCHAR (100) NOT NULL

SomeProperty30 VARCHAR (100) NOT NULL

SomeProperty31 VARCHAR (100) NOT NULL

SomeProperty32 VARCHAR (100) NOT NULL

SomeProperty33 VARCHAR (100) NOT NULL

SomeProperty34 VARCHAR (100) NOT NULL

SomeProperty35 VARCHAR (100) NOT NULL

SomeProperty36 VARCHAR (100) NOT NULL

SomeProperty37 VARCHAR (100) NOT NULL

SomeProperty38 VARCHAR (100) NOT NULL

SomeProperty39 VARCHAR (100) NOT NULL

SomeProperty40 VARCHAR (100) NOT NULL

CONSTRAINT FK_Country

FOREIGN KEY (CountryID)

REFERENCES dbo.Country (CountryID)

)

GO

CREATE INDEX IDX_City_Not_Covering_Index

ON dbo.Citys (CountryID)

INCLUDE (CityName)

GO

_ _ _

Let's start the magic trick. Please open three REQUEST windows to mark the window on which the statement was executed.

one

two

one

two

So far, there has been no deadlock. (what do you think of deadlock? you can look at it through EXEVENT or your own deadlock script. A script will be given later, but EXEVENT will not be introduced here, although it is much easier to use than a script.)

1 in the window to continue with the following script

INSERT INTO dbo.Citys

VALUES

(1,-- CountryID-int

'New York',-- CityName-varchar

8538000,-- As Google just said and that's cool!

1000,-- From the ceiling

)

2 continue to execute the following script in window 2

INSERT INTO dbo.Citys

VALUES

(2,-- CountryID-int

'Delhi',-- CityName-varchar

19980000,-- As Google just said and that's cool!

2000,-- From the ceiling

)

1 We continue to query in window 1

2 We also query in window 2.

Then the magic is performed, and a deadlock prompt appears in window 2.

Deadlock icon also appears in EXEVENT

It is estimated that some SQL SERVER DBA have a clear understanding of what is going on here, why the deadlock? If you already understand, then there is really no need to read on.

-

Deadlock occurs when inserting records into CITYS table, when inserting CITYS table "NEW YORK", I add X lock to this row, (row lock), when I insert CITYS table "Delhi", give Delhi this row (should be the second row added X lock) so far, it is still at peace.

But I immediately did a bad operation, I queried the CITY table, although I built the index, (here to buy a pass, think about it, I still use the above operation, only need to change one place to avoid this deadlock, depending on the way of the query)

This adds an S lock, and unfortunately, this S lock also locks "NEW YORK" and Delhi, and then triggers the last step of the deadlock. I query Delhi, and this query also gives "NEW YORK" an S lock. Well, since each row has an X lock before, although the row lock can avoid waiting for the bank's S lock, it cannot avoid waiting for other rows, so two queries Turned into a deadlock, waiting for each other. (I guess I'm done. Someone's dizzy. Draw a picture and you'll understand it right away.)

After reading the picture, you should understand why deadlocks occur, and also understand the causes of some failures this Thursday, which is very similar to this, so at some point, people will be killed at the same time.

In fact, to solve this problem, it is also very simple, but not simple, especially in a complex system, serial does not have a problem, does not mean that parallel will not have a problem, a large number of deadlocks are so "magical".

Finally, you can view some deadlocks, or lock grants, through the following script.

SELECT OBJECT_NAME (p.object_id) AS TableName

Dtl.resource_type

Dtl.resource_description

-- c.CityName AS Locked_Row

-- c.CityID AS Locked_Row_ID

Dtl.request_mode

Dtl.request_type

Dtl.request_status

Dtl.request_session_id

Dtl.request_owner_type

Dtl.request_owner_id

FROM sys.dm_tran_locks AS dtl

LEFT JOIN sys.partitions AS p

ON p.hobt_id = dtl.resource_associated_entity_id

SQL SERVER deadlock on how to share the depth of analysis here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.