关于两个update语句互相死锁的显现-CSDN博客

前段时间在msdn的论坛上看到邹老大对一个问题的回复，觉得对锁更了解了，先二话不说“拿来”记录学习下。

原帖地址：http://social.msdn.microsoft.com/Forums/zh-CN/6559504d-c546-45a6-89e2-eeb75041b3e7/-?forum=sqlserverzhchs

首先是环境脚本

CREATE TABLE [dbo].[table1](

[A] [nvarchar](10) NULL,

[B] [nvarchar](10) NOT NULL,

[C] [nvarchar](10) NULL

) ON [PRIMARY]

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’)

然后是三个脚本

–查询1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begin tran

print convert(nvarchar(30),convert(datetime,getdate(),121),121)

update table1

set A=’aa1’

where B=’b3’

print convert(nvarchar(30),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(30),convert(datetime,getdate(),121),121)

commit tran

–查询二

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED

begin tran

update table1

set A=’aa3’

where B=’b7’

EXEC sp_lock @@spid

commit tran

–查询三：

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED

begin tran

update table1

set A=’aa3’

where B=’b1’

EXEC sp_lock @@spid

commit tran

运行的情况是在两个线程中

情况1：

先运行查询1，然后立刻在另外一个线程中运行查询2。

结果是两边顺利的完成更新

情况2：

先运行查询1，然后立刻在另外一个线程中运行查询3.

结果是查询3出现死锁情况。

以上是现象

———————————————————————————————

下面我们来分析为什么会有这个现象，首先我们先来看看update动作到底会做哪些事.

我们先开启profiler，监控lock:acquired以及lock:released两个项目，并且限制suid为我们需要监控的进程ID，然后我们得到下面的监控结果

我们可以得出这样一个结论：

Update过程中对表进行扫描依此对每行记录下U锁，若满足条件则转换为X锁更新，若不满足条件则释放U锁。由此结论，我们就可以推断出上面分别导致死锁，和不会导致死锁的情况是怎样的原因了。

情况1：

查询1：对表进行扫描依此对每行记录下U锁，若满足条件则转换为X锁更新，若不满足条件则释放U锁。更新完成后，若未提交则X锁继续保留。

注意：这里被保留的X锁的行数为第2行（b3），因为这个是一个堆表，排列顺序一般是按照插入顺序，update扫描该表时候从从第一开始扫描的

查询2：对表进行扫描一次对每行记录下U锁，因为需要查询的目标在查询1的X锁之后，未能查询到需要更新得条件既被发生无法添加U锁等待，这样只有当查询1完成查询后，查询2才能继续，所以不会导致死锁。

注意：正因为查询2要满足X锁的条件在第7行以(b7),所以当查询2的U锁获取再释放动作，到了第二行的时候，遇到查询1保留的X锁，整个事务进入等待状态，并且未留下任何可以干扰查询1的第二个语句的锁，所以，当查询1直接完毕后，查询2得以继续正常执行完成

情况2：

查询1：对表进行扫描依此对每行记录下U锁，若满足条件则转换为X锁更新，若不满足条件则释放U锁。更新完成后，若未提交则X锁继续保留。

查询3：对表进行扫描依此对每行记录下U锁，因为需要update的条件在查询1前就被发现并加上X锁，但是扫描到查询1锁添加的X锁后无法添加U锁导致需要等待查询1完成后才能继续提交。这时查询1的第二个update语句开始运行，更新U锁时，发现查询2的X，要等待查询3的X锁释放同时达成死锁条件。

注意：正因为查询3的添加X锁动作，在第一行就发生了（b1），然后达到第二行是，又被查询1保留的X锁阻止了继续操作第二行(b3)。这样他就需要等待查询1释放在第二行的X锁才能继续update，但是查询1的第二个语句，又需要查询3先释放在第一行(b1)的X锁，就这样形成了死锁条件。

如此这个在同一页面，不同的行的互相死锁，和不死锁的原因就相当清楚了。

这个案例重点就是，update是一个两个动作的事务，分为查询和修改，并且修改这个动作是通过对各个满足条件的行做X锁来保证一致性的

而这个动作当遇到并发的时候，即使是不同的行也可能导致互相死锁。

通过这个案例我们应该得出一个结论，就是应该避免对一个表的多线程操作。因为他们很可能造成死锁。

——————————————————————————————–

以下是延伸研究：

那么现在又一个问题，若update动作会对整个表进行扫描，并一一进行U锁获取释放动作，这必然会造成大量性能消耗，

是否大数据的的情况会不同呢？增加索引是否会改善呢？增加主键是否会改善呢？

我们进行以下实验

1、不增加主键或索引，仅对表扩大数据量，然后update其中第1000行记录

–环境建立语句
Create Table dbo.a2
(
id int identity(1,1),
value1 char(10),
value2 varchar(20)
)

declare @n int
set @n=1
–测试数据填充
while (@n<10000)
begin
INSERT INTO dbo.a2
           (value1       ,value2
           )
     VALUES
           (REPLICATE(‘c’,10)
           ,CONVERT(varchar(20),REPLICATE(‘F’,20))
           )
set @n=@n+1
end

产生的锁和释放数量和行数*2差距不大，这让我们直接确认了表扫描的可怕

2、增加主键,并扩大数据量

创建一个表，int列为自增主键，插入1W笔数据，使用主键update其中第1000行记录

这次我们可以看到整个事务中根本不使用U锁了，而是先通过S锁，定位到具体页面，再直接锁定对应行，实现更新。

3、不添加主键，而是查询表添加索引，数据量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(20),REPLICATE(‘F’,20))
           )
set @n=@n+1
end