SQL Server deadlock Diagnostics-deadlock caused by the same row of data under different index operations

Deadlock Overview

Typically, the simplest and most common deadlocks occur at different table levels,
Session 1 The first step to modify a table, the second step to modify the B table,
Session 2 The first step to modify the B table, the second step to modify a table,
When the session 1 and Session 2 push sequence occurs when the deadlock occurs, this method of ending is relatively simple, in the same order of propulsion operation can unlock the deadlock.

The following shows a deadlock that is not used for the above situation, a little bit special.

Dead-lock demo on same table

But there are many types of deadlocks, and the above is just one of the simplest and most common deadlocks,
Span style= "FONT-SIZE:13PX; font-family: ' Microsoft Yahei '; " In theory, as long as the conditions of deadlock occur: the different session (session) holds a portion of the resources exclusively, and applies each other to the resources held by each other

 -- testdeadlock is the primary key (the clustered index is generated by default), and the Col2 field is unique to the nonclustered index  create  table   Testdeadlock (Id  int  constraint  pk_testdeadlock_id  Key   int  constraint  uk_testdeadlock_col2 unique  , Remark  varchar  (100    

Then use sqlquerystress, open two reply, respectively, according to the clustered index and nonclustered index, delete the same row of data (the test data will be set when the ID and Col2 are 1),
As shown
First let these two sessions execute (Air line), then insert a row of data into the Testdeadlock table (insert into [Testdeadlock] values (1,1,newid ()))
It may take several times to observe that an exception occurred in one of the sqlquerystress.

To open the details of its exception information, it will find that it is a deadlock

First, check the ID of the index on the table, which will be used during the analysis and lock-up process.
PK_TESTDEADLOCK_ID is a clustered index whose ID is 72057594050314240
Uk_testdeadlock_col2 is a nonclustered index whose ID is 72057594050379776

Observe the deadlock information (Xml_deadlock_report) using the System_health extended event that comes with SQL Server

SELECT  CAST(Xet.target_data asXML) fromsys.dm_xe_session_targets XetJOINSys.dm_xe_sessions XE on(xe.address=xet.event_session_address)WHEREXe.name= 'System_health'SelectXml_event_data,xml_event_data.value ('(event[@name = "Xml_deadlock_report"]/@timestamp) [1]','datetime') Execution_time,xml_event_data.value ('(Event/data/value) [1]','varchar (max)') Query from (    SELECTEvent_table.xml_event_data from(            SELECT CAST(Event_data asXML) Xml_event_data fromSys.fn_xe_file_target_read_file (N'Your Path \system_health_*',NULL,NULL,NULL)        )  asevent_table CrossAPPLY Xml_event_data.nodes ('//event') n (event_xml)WHEREEvent_xml.value ('(./@name)','varchar (+)')inch('Xml_deadlock_report')) vOrder  byExecution_time

The following deadlock information is obtained, and the Xml_deadlock_report in the extended event clearly indicates that: for the current line of data (8194443284a0)
delete from [Testdeadlock] where id= 1 waits for a lock on a nonclustered index (waitresource= "key:11:72057594050379776 (8194443284a0)")
delete from [Testdeadlock] where Col2 = 1 waits for a lock on the clustered index (waitresource= "key:11:72057594050314240 (8194443284a0)")
both have deadlocks, and must be waiting for each other's own resources (the lock on the index)
so the current deadlock can be so understandable.
delete from [Testdeadlock] where id=1 holds U lock on clustered index, request x lock on nonclustered index
delete from [Testdeadlock] where Col2 = 1 holds an X lock on a nonclustered index, request U lock on a clustered index
Result: Deadlock!

The interpretation of waitresource, ref: 41687209

Lock sequence analysis for two SQL

The above analysis is only based on the existing phenomenon to speculate on the process, if you can observe the execution of each SQL statement lock application and release order, the problem is easier to understand.
The following uses profile to observe the application and release order of locks during two statements execution

Take a look at delete from [Testdeadlock] where Id = 1 The order of the lock for the execution of the SQL procedure
Profile is clear, for delete from [Testdeadlock] where Id = 1
First request a clustered index (72057594050314240) on the page level of intent exclusive Lock (ix), to the row level of the exclusive lock (X), and then request the nonclustered index (72057594050379776) of the page level intent exclusive Lock (ix), Convert to row level exclusive lock (X)

For delete from [Testdeadlock] where Col2 = 1
First, apply the intent Update lock (IU) at the page level on the nonclustered index (72057594050379776), turn the row level update lock (U), and then request the intent exclusive lock (IX) of the page level clustered index (72057594050314240), Convert to row level exclusive lock (X)

Through the above lock sequence analysis, confirmed the above-mentioned locking method of speculation, it is not difficult to understand why two of SQL statements deadlock.
Still back to the concept of deadlock: different sessions (session) have exclusive ownership of a subset of resources, and simultaneously apply for the resources held by the other party
This mutually-held resource can be a resource on a different table, a resource on the same table, or even a different resource (a resource of different indexes) on the same row of data.
Deadlocks can occur as long as different sessions are held with each other in an exclusive way to hold each other's desired resources.

This method is a deadlock caused by the simultaneous delete of the two sides according to different indexes, similar to the above, can be extended to both sides simultaneously update, both delete or update, both simultaneous update or select and so on
As long as the index propulsion sequence is inconsistent, it is possible to cause deadlocks to occur, such problems can be attributed to the same row of data, different index operations caused by the deadlock.

How to solve?

1, trying to get started with the business and be able to manipulate the data in a uniform way.
2, using queues to eliminate spikes in concurrency operations.
3, try Tablockx, lock the entire table at once.
4, attempting to change the isolation level and attempting to serialize the isolation level.

In the end Buddha Department:
Many questions like to use strange explanations, in fact, many problems are not strange, just do not know,
Technical problems, do not know it is not a big deal, know more no big deal, know it is just know, do not know the experience once know, know that there is no pride or inferiority
Your knowledge corner can not deny your technical ability, application level of things, but it is in the people make good rules to play games, no one to pretend.

Reference:
Https://www.cnblogs.com/Uest/p/4998527.html
https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

SQL Server deadlock Diagnostics-deadlock caused by the same row of data under different index operations