About the appearance of two update statements deadlock each other, deepen our understanding of the lock

Some time ago in the MSDN forum to see Sanda reply to a question, feel more understanding of the lock, first apart "take" record under study.

Original post address: Http://social.msdn.microsoft.com/Forums/zh-CN/6559504d-c546-45a6-89e2-eeb75041b3e7/-?forum=sqlserverzhchs

First, the Environment script

CREATE TABLE [dbo]. [Table1] (

[A] [nvarchar] (A) NULL,

[B] [nvarchar] (a) not NULL,

[C] [nvarchar] (a) NULL

) on [PRIMARY]

Go

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa1 ', n ' B1 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' aa2 ', n ' b3 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' b4 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' B5 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' B2 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' b6 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' B7 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa3 ', n ' B8 ', n ' 11 ')

INSERT [dbo]. [Table1] ([A], [B], [C]) VALUES (n ' Aa1 ', n ' B9 ', n ' 11 ')

Then there are three scripts

--Enquiry 1

SET TRANSACTION Isolation Level READ UNCOMMITTED

BEGIN Tran

Print convert (nvarchar (), CONVERT (Datetime,getdate (), 121), 121)

Update table1

Set a= ' Aa1 '

where b= ' B3 '

Print convert (nvarchar (), CONVERT (Datetime,getdate (), 121), 121)

EXEC sp_lock @ @spid

WAITFOR DELAY ' 00:00:10 '

Update table1

Set a= ' Aa2 '

where b= ' B8 '

EXEC sp_lock @ @spid

Print convert (nvarchar (), CONVERT (Datetime,getdate (), 121), 121)

Commit Tran

--Query Two

SET TRANSACTION Isolation Level Read UNCOMMITTED

BEGIN Tran

Update table1

Set a= ' Aa3 '

Where b= ' B7 '

EXEC sp_lock @ @spid

Commit Tran

--Query Three:

SET TRANSACTION Isolation Level Read UNCOMMITTED

BEGIN Tran

Update table1

Set a= ' Aa3 '

Where b= ' B1 '

EXEC sp_lock @ @spid

Commit Tran

The situation is running in two threads

Situation 1:

Run Query 1 First, and then run Query 2 in another thread immediately.

The result is a smooth completion of the update on both sides

Situation 2:

Run Query 1 First, and then run Query 3 in another thread immediately.

The result is a deadlock condition in Query 3.

The above is the phenomenon

---------------------------------------------------------------------------------------------

Let's look at why this is happening, and first let's see what the update action is going to do.

We start with Profiler, monitor lock:acquired and lock:released two projects, and limit suid to the process IDs we need to monitor, and then we get the following monitoring results

We can draw a conclusion that:

Scan tables during update record U lock on each line, if satisfied, convert to x Lock update, Release U lock if condition is not satisfied. From this conclusion, we can infer the reason why the above causes the deadlock and does not cause the deadlock.

Situation 1:

Query 1: Scan the table to record the U-lock per line, if the conditions are met, the conversion to the X-lock update, if not satisfied with the release of the U lock. When the update is complete, the X lock remains if it is not committed.

Note: The number of X locks retained here is line 2nd (B3) because this is a heap table, which is usually sorted in the order of insertion, and the update scans the table from the first

Query 2: Scan the table once for each row to record the U lock, because the target that needs to be queried is after the X lock of 1, the query cannot be queried until the condition that needs to be updated is not able to add a U lock wait, so that only after query 1 completes the query, Query 2 can continue, so it does not cause a deadlock.

Note: Because query 2 is to satisfy X-lock conditions on line 7th (B7), so when query 2 of the U lock to get the release action, to the second line, the query 1 retained the X lock, the entire transaction into the waiting state, and did not leave any interference to query 1 of the second statement of the lock, so, when the query 1 directly after the completion , query 2 continues to perform normally

Situation 2:

Query 1: Scan the table to record the U-lock per line, if the conditions are met, the conversion to the X-lock update, if not satisfied with the release of the U lock. When the update is complete, the X lock remains if it is not committed.

Query 3: Scan a table to record a U lock on each line, because the condition requiring update is found and X-locked before query 1, but unable to add U lock after scanning to the X lock added by the query 1 lock will cause you to wait for query 1 to complete before submitting. At this point, query 1 of the second UPDATE statement began to run, update U lock, found that query 2 x, to wait for the query 3 of the X-lock release while the deadlock condition.

Note: Because of the Add X lock action for Query 3, the first line occurs (B1), then the second row is, and the X lock retained by query 1 blocks the continuation of the second line (B3). So he needs to wait for query 1 to release the X lock in the second line to continue with the update, but the second statement of Query 1 needs to query 3 to first release the X lock in the first row (B1), thus forming the deadlock condition.

So this on the same page, the different rows of each other deadlock, and the reason for the deadlock is quite clear.

The focus of this case is that update is a two-action transaction, divided into queries and modifications, and that the action is modified by an X-lock on each row that satisfies the condition.

This action, when confronted with concurrency, can lead to deadlock, even for different rows.

In this case we should come to the conclusion that multithreading of a table should be avoided. Because they are likely to cause deadlocks.

--------------------------------------------------------------------------------------------

The following is an extension study:

So now another problem, if the update action will scan the entire table, and the U lock to get the release action, this will cause a lot of performance consumption,

Whether the big data will be different. Does adding an index improve? Does adding a primary key improve?

We do the following experiments

1, do not increase the primary key or index, only the table to enlarge the amount of data, and then update the 1000th Row Records

--Environment Establishment statement
Create Table DBO.A2
(
ID int identity (1,1),
Value1 Char (10),
value2 varchar (20)
)

DECLARE @n int
Set @n=1
--Test data population
while (@n<10000)
Begin
INSERT into DBO.A2
(Value1, value2
)
VALUES
(REPLICATE (' C ', 10)
, CONVERT (varchar, REPLICATE (' F ', 20))
)
Set @n=@n+1
End

The resulting lock and number of releases and number of lines *2 the gap is not very large, which allows us to directly confirm the table scan of the Terrible

2, increase the primary key, and expand the amount of data

Create a table with the int listed as the self-added primary key, insert 1W data, update the 1000th row record with the primary key

This time we can see that the entire transaction does not use the U lock at all, but first through the S lock, positioning to the specific page, and then directly lock the corresponding row, to implement the update.

3, does not add the primary key, but the query table to add index, data volume 10000

Create Table dbo.a3
(
ID int identity (1,1),
Value1 Char (10),
value2 varchar (20)
)
Create INDEX IX_A3 on A3 (ID)

DECLARE @n int
Set @n=1

while (@n<10000)
Begin
INSERT into dbo.a3
(Value1, value2
)
VALUES
(REPLICATE (' C ', 10)
, CONVERT (varchar, REPLICATE (' F ', 20))
)
Set @n=@n+1
End

1:18,051 of which are index pages,

This query results in rows of about 80 lines, which is the end of the output, which repeatedly shows the release of the S lock.

We can judge this by querying the index, locating the corresponding row of the index, and then adding a U-lock to the corresponding data row. Note that you can see here is an IU action on the index page, which is to determine if the change will affect the index page, because the index page has not been accounts, thereby releasing the IU lock.