首先是环境脚本
CREATE TABLE [dbo].[table1](
[A] [nvarchar](10) NULL,
[B] [nvarchar](10) NOT NULL,
[C] [nvarchar](10) 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')
然后是三个脚本
--查询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
![](https://i-blog.csdnimg.cn/blog_migrate/412efc8d3567d0467caca471309ab1c1.jpeg)
其中1:18051是索引页,
这个查询结果行数大约80行,这个是输出结果的末尾,上面反复出现S锁的释放。
我们可以判断这个是通过查询索引,定位到 索引对应行以后, 再对对应数据行进行U锁添加释放动作。注意你可以看到这里有一个对索引页的IU动作,这里是在确定是否该更改会影响到索引页,因为没有使索引页发生更变,从而释放了这个IU锁。
https://blog.csdn.net/starseeker7/article/details/28632773