行级锁为什么锁住了整个表?

作者:vkvi 来源:ITPOW(原创) 日期:2017-1-5

SQL Server 中,行级锁,听名字就是锁定指定行的,锁定之后,其他连接不能操作该行,但可以操作其他行。

它的优点是不锁那么宽泛,不像表级锁一样,整个表都锁住了。缺点据说是如果行级锁太多,就比较耗资源,当 SQL Server 发现行级锁太多时,就会自动升级为表级锁。据说是这样的。

今天要讨论的是另外一个问题,为什么只有一个行级锁,却锁住了整个表呢?

事务 A:update 就加了锁,默认是行级锁,它要操作的是 where id=1。

事务 B:update 要操作的是 where id=2,按理说它与事务 A 不冲突,可是实际测试过程中会发现它要等事务 A 执行完了才执行。

这是因为没有索引的原因,我们为 id 建立索引或唯一键,情况就不一样了。事务 B 根本不会等事务 A(因为这里他已经通过索引就知道事务 B 与事务 A 不会冲突)。

虽然我们建聚集索引、非聚集索引、唯一键都可以。但是如果是组合索引就需要注意了,同样是上面的代码,我们在建立索引时,多冒一个 fld1 出来,得出如下结论:

  • fld1、id 组成的聚集索引,事务 B 需要等待。
  • fld1、id 组成的非聚集索引,事务 B 需要等待。
  • id、fld1 组成的聚集索引,事务 B 需要等待。
  • id、fld1 组成的非聚集索引,事务 B 需要等待。

其实,我们也不需要去记,我们只需要知道:SQL Server 是很聪明的,它知道 where 条件与索引的搭配,然后决定事务 B 是否需要等待。比如,我们将事务 B 的代码改成如下:

表中不存在 id=1 和 fld1=9999 的记录,也就是说它不会修改任何记录。

那么此时事务 B 到底要不要等事务 A 呢?

要不要等,并不取决于结果,而是取决于过程。

  • 如果我们只对 id 建立了索引,此时 SQL Server 并不能预判,所以它只有等事务 A 执行完。
  • 如果我们对 id、fld1 建立了组合索引(不论是不是聚集的,注意与前面区别,因为这里组合索引与 where 字段相同,所以不在乎聚集与否),SQL Server 能预判,它知道两个事务不冲突,所以它不需要等待事务 A 执行完。

前面条件都是用的 = 号,如果改用 >、>= 这类符号呢?

一样的道理,都是取决于 SQL Server 对索引的使用程度。建立什么样的索引才有能够与 where 很好地搭配,不仅需要理论知识,更需要长期总结经验。

实际操作中,可在“SQL Server Management Studio 菜单 -> 查询 -> 包含的实际执行计划”中查看是否使用了索引。还可以在“工具 -> SQL Server Profiler”中监测每个语句的执行情况。

还有人问,如果代码是这样的:

而我们只建立了 id 索引,事务 B 是否会等待事务 A 呢?

我觉得这样问,说明人已经被绕晕了,我们来理一理:

  • 最先执行第 1 句,它加了锁了,是对 id=1 这条记录加的锁。
  • 然后执行第 3 句,由于事务 A 还没完成,所以它对 id=1 的锁也还没释放,但是第 3 句通过索引会判断到,它不会使用 id=1 这条记录,两者不冲突,故立即执行第 3 句。
  • 最后执行第 2 句,它有没有使用索引根本就不会影响第 3 句,因为第 3 句在前面执行的。
相关文章