关于两个update语句互相死锁的显现,加深我们对锁的了解

原创 2014年06月05日 15:45:02

前段时间在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]

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


其中1:18051是索引页,

这个查询结果行数大约80行,这个是输出结果的末尾,上面反复出现S锁的释放。

我们可以判断这个是通过查询索引,定位到 索引对应行以后, 再对对应数据行进行U锁添加释放动作。注意你可以看到这里有一个对索引页的IU动作,这里是在确定是否该更改会影响到索引页,因为没有使索引页发生更变,从而释放了这个IU锁。


高并发update的 死锁产生原因

在说这个之前首先了解一下讲一下update语句sql中的情况。死锁产生的条件:出现循环等待资源。update对锁的流程:       当sql发出一个update请求之后,数据库会对表中的每条记录加上...
  • zc474235918
  • zc474235918
  • 2016年01月20日 16:58
  • 11943

ORACLE锁机制以及For update语句

转自http://www.jb51.net/article/32322.htm和http://blog.csdn.net/liqfyiyi/article/details/7948282,不知源头。...
  • zhq200902
  • zhq200902
  • 2017年02月06日 16:20
  • 4178

初步了解更新锁(U)与排它锁(X)

一直没有认真觉察UPDATE操作的锁,最近在MSDN上看到一个问题,询问堆表更新的死锁问题,问题很简单,有类似这样的表及数据:CREATE TABLE dbo.tb(     c1 int,     ...
  • zjcxc
  • zjcxc
  • 2014年05月28日 17:13
  • 14435

(转)共享锁,排他锁,更新锁。。。。

锁的概述 一. 为什么要引入锁 多个用户同时对数据库 的并发操作时会带来以下数据不一致的问题: 丢 失更新 A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果...
  • zlb6363061
  • zlb6363061
  • 2010年07月20日 11:21
  • 5818

多线程死锁的产生以及如何避免死锁

一、死锁的定义 多线程以及多进程改善了系统资源的利用率并提高了系统 的处理能力。然而,并发执行也带来了新的问题——死锁。所谓死锁是指多个线程因竞争资源而造成的一种僵局(互相等待),若无外力作用,这些进...
  • ls5718
  • ls5718
  • 2016年07月13日 11:07
  • 21476

两个事物 update同一张表出现的死锁问题

引言 近来做省一级计算机一级考试系统的时候,学生端进行大批量判分的时候,出现了这样的问题(事务(进程 ID 262)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务...
  • u010540106
  • u010540106
  • 2016年01月24日 21:11
  • 1963

数据库中的悲观锁和乐观锁和死锁

悲观锁当我们使用悲观锁的时候我们首先必须关闭mysql数据库的自动提交属性,因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交。关闭命令为...
  • lzh657083979
  • lzh657083979
  • 2017年08月03日 09:26
  • 199

MySQL批量更新死锁案例分析

问题描述 在做项目的过程中,由于写SQL太过随意,一不小心就抛了一个死锁异常,如下: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollb...
  • aesop_wubo
  • aesop_wubo
  • 2012年12月12日 18:25
  • 31661

mysql查询更新时的锁表机制分析

在许多情况下,可以根据培训猜测应用程序使用哪类锁定类型最好,但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序,应用程序的不同部分可能需要不同的锁类型。为了确定是否想要使用行级锁定的存储引...
  • fuxuejun
  • fuxuejun
  • 2011年03月24日 12:53
  • 7342

sql server在高并发状态下同时执行查询与更新操作时的死锁问题

最近在项目上线使用过程中使用SqlServer的时候发现在高并发情况下,频繁更新和频繁查询引发死锁。通常我们知道如果两个事务同时对一个表进行插入或修改数据,会发生在请求对表的X锁时,已经被对方持有了。...
  • ajianchina
  • ajianchina
  • 2015年07月08日 18:39
  • 6141
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于两个update语句互相死锁的显现,加深我们对锁的了解
举报原因:
原因补充:

(最多只允许输入30个字)