我认为写的最好的关于SQL Server更新锁和排它锁了

原创 2016年11月18日 11:19:19

转载至:http://blog.csdn.net/zjcxc/article/details/27351779

一直没有认真了解UPDATE操作的锁,最近在MSDN论坛上看到一个问题,询问堆表更新的死锁问题,问题很简单,有类似这样的表及数据:

CREATE TABLE dbo.tb(

     c1 int,

     c2 char(10),

     c3 varchar(10)

);

GO

DECLARE @id int;

SET @id = 0;

WHILE @id <5

BEGIN;

     SET @id = @id + 1;

     INSERT dbo.tb VALUES( @id, 'b' + RIGHT(10000 + @id, 4), 'c' + RIGHT(100000 + @id, 4) );

END;

在查询一中执行更新操作:

BEGIN TRAN

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 2;

WAITFOR DELAY '00:00:30';

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 5;

ROLLBACK;

在查询一执行开始后,马上在查询二中执行下面的操作

BEGIN TRAN

UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 1;

ROLLBACK;

为什么会出现死锁,如果条件改为 c1 = 4 则不会死锁。

开始的时候想得比较简单,死锁的表现是形成循环等待(对于两个查询而言,可以简单地认为就是在相互等待对方锁定资源的释放)

    对于这个例子而言,第一个查询更新两次,会先更新并锁定一条记录,然后等待第二个更新;但第二个查询只会更新一条记录,它要么与第一个查询冲突,无法获得锁,需要等待查询一完成,这个时候它并没有锁定什么;要么能够获得锁,完成更新。似乎不应该会出现死锁,死锁会不会是其他原因导致。

    在自己的电脑上简单测试了一下,似乎也确实没有死锁。

    但后面通过Profile跟踪更新操作的下锁情况才发现,自己的分析大错特错了。主要原因在于没有正确理解更新操作是如何用锁的。

    在联机帮助上锁模式中有关于更新的U(更新锁)和X(排它锁)的说明

http://msdn.microsoft.com/zh-cn/library/ms175519(v=sql.105).aspx

不过说得确实挺模糊的,里面还提到了S锁,我一直以为是查询数据过程中用的S锁(也 SELECT 一样),找到满足条件的记录后用U锁,再转换为X锁做更新。


    SQL Server Profiler(事件探查器)跟踪的结果让我知道了这是一个错误的理解,在SQL Server Profiler中新建一个跟踪,选择Locks中的Lock:Acquired (加锁)Lock:Acquired(释放锁)解两个事件,在筛选中设置只跟踪测试用的查询窗口对应的spid(可以执行 PRINT @@SPID 获得),然后执行一个更新语句,比如 UPDATE dbo.tb SET c2 = 'xx' WHERE c1 = 3

Profile中可以看到,对于每条记录都有加 锁的操作,对于不满足条件的记录,会马上释放U锁;对于满足条件的记录,最终转换为X锁。如下图所示。




注意一下,在这个跟踪结果里面,并没有出现S锁。




另外学做了一些测试:


  1. 通过加大记录量做更新测试,会发现数据扫描涉及的记录都有U锁,并不限于更新记录所在的页。这从另一个角度说明了大表中Scan 可怕。

  2. 当使用索引Scan的时候,也会通过跟踪发现所Scan的索引资源有U锁,如果更新不涉及索引变化,那以只会对应的记录有UX锁,索引的U锁会释放;如果影响索引,那么索引的U锁会转X锁。

  3. 删除操作与更新操作类似

  4. 使用 UPDATE aSET c2 = 'xx' FROM dbo.tb AS a WITH(NOLOCK) WHERE c1 = 3  的加锁情况是一样的, 并不会因为NOLOCK的提示而不加 U 或者 X 



最后回头研究一下示例中的死锁问题:

  • 对于查询一,第一个更新依次扫描表中所有记录,对于每条记录,加 锁,判断是否符合更新条件,如果符合,转换为 锁;如果不符合条件,释放 锁。第一个更新完成的时候,查询一锁定了一条记录(由于事务未完成,所以锁一直保持),然后等待第二个更新

  • 对于查询二,依次扫描表中的每条记录(与前面的更新一样),如果它更新的记录在查询一更新的记录前被扫描到,那么这条记录也会变成 锁;当继续并进行到查询一的X锁记录的零点,与 冲突,无法继续,这时候查询二等待查询一释放锁

  • 查询一的第二个更新开始执行,依次扫描每条记录,同一个事务内不会有冲突,所以它不会与自己之前锁定的记录有冲突,但进行到查询二锁定的记录的时候,它也无法获得 锁,它需要等待查询二释放资源。这个时候就形成了相互等待,符合死锁条件

  • 如果查询二需要更新的记录在查询一的第一个更新记录之后,则不会有死锁,因为查询二在扫描到查询一第一个更新的记录时就会因为锁冲突等待了,这个时候它没有对任何记录设置与查询一的操作有冲突的锁。我自己测试的时候没有死锁,就是这种情况。

    注意这里面提到的顺序,是数据读取的顺序,不一定与存储顺序一样,磁盘上记录的顺序也不一定与INSERT的记录顺序一样,这也是我用同样条件没有测试出死锁的原因(我的环境中,恰好读出的顺序与INSERT的顺序不一样)

更新时,记录读取的顺序,可以通过Profile跟踪的Lock:Acquired (加锁)事件来看,涉及大量数据时,如果服务器支持,还会有并发读取。这也是分析死锁时要考虑的因素

SQL排他锁的解决方案

问题描述: 我有一个数据库叫做inOutSell 已经备份到D:/mydatabase.bak 现在的情况是我要用sql语句进行还原 语句如下: USE inOutSell RESTORE DATAB...
  • htl258
  • htl258
  • 2009年04月28日 22:41
  • 2429

SQL SERVER (MSSQL) 排它锁的实例

需求: 根据某个字段的标记,来判断要拿到的记录sn,再更新此条记录的标记,防止被别的记录拿到 要解决的问题: 总有那一个瞬间,2个线程同时拿到同一条记录 解决方法: 使用排它锁,下面为实例,好用请给好...

用例子来说明SQL Server的更新锁

1:首先创建表,插入数据: create table table1(A nchar(10) not null primary key,B nchar(10),C nchar(10)); inser...

SqlServer中的更新锁(UPDLOCK)

SqlServer中的更新锁(UPDLOCK) 2013-01-24 10:49 by 假面Wilson, 3503 阅读, 1 评论, 收藏, 编辑 UPDLOCK.UPDLOCK 的优...

sql server锁知识及锁应用

sql server锁(lock)知识及锁应用 一 关于锁的基础知识 二 锁的分析及应用系列 三 SQL Server 锁机制 悲观锁 乐观锁 实测解析 四 SQL Server 中WITH NOLO...

sql server中高并发情况下 同时执行select和update语句死锁问题 (一)

最近在项目上线使用过程中使用SqlServer的时候发现在高并发情况下,频繁更新和频繁查询引发死锁。通常我们知道如果两个事务同时对一个表进行插入或修改数据,会发生在请求对表的X锁时,已经被对方持有了。...
  • lishehe
  • lishehe
  • 2014年12月31日 00:39
  • 28376

sql server中update会加行锁还是表锁?

update from table set xx='aa' where yy='bb' 这里的yy字段不是主键,但值都是唯一的,这样的话,不加 rowlock时update是锁行还是锁表? ...

Mysql数据库日志,备份及回滚操作

一、打开二进制日志配置 : 在Windows系统下,对mysql的my.ini的log-bin等进行配置目录位置时,假设要将log-bin的日志配置到D盘的mysqllog的文件为binlog。...
  • kmguo
  • kmguo
  • 2014年03月16日 19:30
  • 16316

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

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

sql server行级锁,排它锁,共享锁的使用

锁的概述 一. 为什么要引入锁 多个用户同时对数据库的并发操作时会带来以下数据不一致的问题: 丢失更新 A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:我认为写的最好的关于SQL Server更新锁和排它锁了
举报原因:
原因补充:

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