none
insert和update到底加的什么锁 RRS feed

  • 问题

  • 如题,有一张表,有两个字段ID,Field,其中ID为主键,当我提交一个INSERT事务时,进行延迟,不影响以主键为WHERE条件的UPDATE语句,但以Field为WHERE条件的UPDATE缺会被阻塞,这是为什么。
    • 已移动 Sheng Jiang 蒋晟 2010年2月2日 2:52 SQL (发件人:.NET Framework 一般性问题讨论区)
    2010年2月2日 1:38

答案

全部回复

  • 因为id为条件的where 的update 在查找的时候会直接根据SQL Server 中的索引结构B-Tree查询到对应id行的地址
    然后直接访问

    但是Field由于不是主键没有索引则需要直接在整个实际Table的存储单元去查找 所以会阻塞

    我个人是这样理解的
    Memory all alone in the moonlight~My Blog in .NET~~~
    2010年2月2日 1:58
  • 没有想过这个问题,学习一下。。
    2010年2月2日 2:06
    • 已标记为答案 Liuhongyu 2010年2月4日 6:47
    2010年2月2日 3:48
    版主
  • sp_lock 直接看一下指定对象的锁分配和等待的情况就很清楚了
    2010年2月2日 4:21
  • Or enable snapshot isolation in that database.
    2010年2月2日 4:23
  • 有可能是表里引索的影响,你的两个columns有没有index,是non-clustered还是clustered。另外还和你的update语句有关。

    sp_lock有你想要的信息。要解读sp_lock返回的信息,可以看看:http://msdn.microsoft.com/zh-cn/library/ms187749.aspx

    如果是page lock,可以用dbcc page 检查是哪个object的什么信息被锁住。
    2010年2月2日 14:11
  • 我使用了一个Test表做测试,按顺序执行三个操作
    INSERT
    UPDATE WHERE(非主键)
    SELECT (允许脏读)
    待插入的数据也在UPDATE的WHERE范围之内
    最后的锁情况是INSERT产生了一个针对行的X锁
    也就是已插入但尚未提交的数据
    而UPDATE产生了3个X锁1个U锁

    产生的U锁则是刚插入但尚未提交的数据
    由于U锁无法与X锁兼容
    导致UPDATE语句的U锁处于被阻塞状态
    直至INSERT事务提交
    U锁升级为X锁完成更新后释放

    如果这样解释是对的话,那么我之前的疑问就不存在了
    根据主键加X锁
    由于新插入的行的主键不在UPDATE的WHERE范围之内
    所以不会阻塞UPDATE语句加X锁
    因而UPDATE语句可直接完成

    但我依然还是有一个疑问
    MSDN中我查到的锁模式表中有这样一句话

    排他 (X)

    用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。

    既然UPDATE是修改数据,那么应该是直接加上X锁才是
    为何被阻塞的是U锁
    当U锁获得后再升级为X锁进行更新操作
    这样岂不是多此一举?
    2010年2月2日 16:19
  • Because insert had x lock already. By the way, sql can't get x lock on object if there's any kind of lock.
    • 已标记为答案 Liuhongyu 2010年2月4日 6:47
    2010年2月3日 14:18
  • liuhongyu,

    你的分析是对的。由于新插入的主键不在UPDATE的WHERE范围之内,所以update不需要在那一行加锁,也就没有block的问题。

    其实你已经在你自己的分析中发现了在update的时候,他先做了一个读的动作(where clause),加s锁,如果成功就升级为x锁,完成更新。

    但是设想如果有两个update同时修改一样的数据,它们同时给那个数据加了s锁,当需要升级为x锁时,就出现问题了。因为两个同时持有s锁,都等待对方离开。这样就造成了deadlock.

    为解决这个问题,就有了u锁。这样就可以告诉sql,这个读的动作后面会跟着x锁。所以sql就不会让其他的锁加在这个数据上了。

    不知道我是否解释清楚了。
    2010年2月3日 17:33