Deadlock-related issues in SQL Server

Deadlock definition:

A deadlock is a two-thread or multiple-thread that has a subset of resources and needs other resources.

But other resources are occupied by other threads, and each thread is in a waiting order to get the resources that other threads occupy

State, this time, if there is no external force to destroy the mutual waiting state or some of the threads automatically abandon the resources already occupied,

Then all the threads are unable to complete the task, this time the system is in a zombie state. This is called a deadlock.

SQL Server itself has a lock monitor, and once a deadlock is found, SQL Server kills one of the threads,

Keep another one (some) from completing the task. Damn it, the lock monitor is in the form of a background thread,

You can view sys.dm_exec_requests from System view

SELECT * FROM sys.dm_exec_requests

Where session_id < 50

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR2kfSa3k_AAEpAtlKuu0876.jpg "title=" 1.jpg " alt= "Wkiom1rr2kfsa3k_aaepatlkuu0876.jpg"/>

But it is not possible to rely solely on SQL Server's own deadlock monitor to resolve deadlocks, although SQL Server violence solves the deadlock problem

However, this kind of brute force solution can affect the performance of the system, which is undesirable for high concurrency performance.

Next, learn about the common forms of deadlock, figure out the cause of the deadlock, and then either rewrite the SQL or create an index or modify the business logic

To resolve the deadlock problem.

Creating test tables and data

CREATE TABLE Testklup

(

Clskey int NOT NULL,

Nlskey int NOT NULL,

Cont1 int NOT NULL,

Cont2 Char (3000)

)

Create unique clustered index inx_cls on Testklup (Clskey)

Create unique nonclustered index Inx_nlcs on Testklup (Nlskey) include (CONT1)

Insert INTO Testklup Select 1,1,100, ' AAA '

Insert INTO Testklup Select 2,2,200, ' BBB '

Insert INTO Testklup select 3,3,300, ' CCC '

Several forms of deadlock

1. Contention for data rows

Session One:

BEGIN Tran

SELECT * from Dbo.testklup with (Updlock)

where Clskey=1

WAITFOR DELAY ' 00:00:10 '

Update Dbo.testklup

Set cont1=200

where clskey=2

Session Two:

BEGIN Tran

SELECT * from Dbo.testklup with (Updlock)

where clskey=2

WAITFOR DELAY ' 00:00:10 '

Update Dbo.testklup

Set cont1=200

where Clskey=1

After session one is executed and session two is executed, the following deadlock-related information appears

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4D/78/wKiom1RR2n-z-KrQAAFuGARrciE721.jpg "title=" 2.jpg " alt= "Wkiom1rr2n-z-krqaafugarrcie721.jpg"/>

Causes of deadlocks:

The session first adds an update lock to the Clskey=1 data row, and then it wants to

clskey=2 data row Add exclusive lock, but this time clskey=2 has been added by session two

Shared lock, session two also want to add an exclusive lock to Clskey=1, but Clskey=1 has been added to the session one shared lock.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3LHDoJMHAAFwBM-pgMU671.jpg "title=" 4.jpg " alt= "Wkiom1rr3lhdojmhaafwbm-pgmu671.jpg"/>

Workaround: One of the reasons for creating deadlocks is simply that you do not access the objects in the database in a fixed order.

Resolving this type of deadlock requires modifying the business logic or SQL statements to access the objects in the database in a fixed order

2. Contention for key values. It took me a long time to figure this out.

Session One:

DECLARE @i int

Set @i=100

While 1=1

Begin

Update Testklup set [email protected]

where Clskey=1

Set @[email protected]+1

End

The implementation plan is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3dbzxdd2AAHcd5klERk738.jpg "title=" 5.jpg " alt= "Wkiom1rr3dbzxdd2aahcd5klerk738.jpg"/>

Session Two:

Declare @cont2 char (3000)

While 1=1

Begin

Select @cont2 =cont2 from Testklup where Nlskey=1

End

The implementation plan is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3hajKlIBAAHGR8RCUBw045.jpg "title=" 6.jpg " alt= "Wkiom1rr3hajklibaahgr8rcubw045.jpg"/>

Session Two is executed repeatedly and the results are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/78/wKiom1RR3RjDwj8-AAFjHjLG6yI498.jpg "title=" 3.jpg " alt= "Wkiom1rr3rjdwj8-aafjhjlg6yi498.jpg"/>

Causes of deadlocks:

Session One according to the primary key Clskey=1 found data row after the data row added an update lock (U), after finding the data, ready to modify the data,

Update locks are converted to exclusive locks (X), and the columns in the nonclustered index are updated after the table data is updated (CONT1)

(Create unique nonclustered index Inx_nlcs on Testklup (Nlskey) include (CONT1)),

So session one will add an exclusive lock to the nonclustered index and complete the update of the data.

Look at session two, session two walk the nonclustered index gets the primary key ID of the target data row, then session two adds a shared lock to the nonclustered index.

When session two obtains the primary key ID and also obtains the value of the column Cont2 through the clustered index, the second will add a shared lock to the primary key row

Look at the answer again and the data that the session two accesses, actually all is the same piece of data,

When a session pair of nonclustered indexes is added to an exclusive lock, session two has added a shared lock to the nonclustered index, so session one waits for session two to release the shared lock

When session two adds a shared lock to a clustered index, session one has added an exclusive lock to the clustered index, and session two waits for the session to release the exclusive lock

It's a dead lock at this time.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4D/79/wKioL1RR4Fnh5c3oAAFwBM-pgMU980.jpg "title=" 4.jpg " alt= "Wkiol1rr4fnh5c3oaafwbm-pgmu980.jpg"/>

Solution:

Scenario One: Do not let session two access the clustered index

Then you need to modify the nonclustered create unique nonclustered index Inx_nlcs on Testklup (Nlskey) include (CONT2,CONT1)

Scenario Two: Do not let session one update the nonclustered index

You still need to modify the nonclustered index create unique nonclustered index Inx_nlcs on Testklup (Nlskey)

Related Reference Links:

Http://www.cnblogs.com/shanksgao/p/3904662.html

This article is from "SQL Server MySQL" blog, declined reprint!

Deadlock-related issues in SQL Server