共享读锁何时释放？

Question

当SQL Server Books online说“在读操作完成后，对资源的共享（S）锁将被释放”，除非事务隔离级别设置为可重复读或更高，或者使用锁提示来保留共享（S）锁直到事务结束。

假设我们讨论的是行级锁，在默认隔离级别（读取提交）下，"读操作"指的是什么？

- 单个数据行的读取？ - 单个8k IO页的读取？ - 或者直到创建锁的完整Select语句执行完成，无论涉及多少其他行？

注意：我需要知道这个原因是因为我们有一个由数据层Web服务生成的几秒钟只读选择语句，它创建了页面级共享读锁，由于与保持服务器更新的复制过程中的行级独占更新锁冲突而导致死锁。选择语句相当大，包含许多子选择，并且有一个数据库管理员建议我们将其重写为多个较小的语句（运行时间较短的片段），以“减少锁定的持续时间”。如果假设共享读锁在完成整个选择语句之前一直保持，那么如果这种假设是错误的（如果在读取行或页面时释放锁），那么这种方法将毫无效果...

- Charles Bretana

---

这是一个关于SQL Server存储引擎的技术问题。我知道在ServerFault.com上有一些非常精通SQL Server管理员，因此这篇文章可能更适合在那里发布。 - John Sansom

这是一个很好的问题。我正在从一张大表迁移数据，想知道使用单个查询将所有行从源传输到目标的“流式传输”会有什么后果。 - Ronnie Overby

Answer 1

这实际上非常有趣，你可能想启动分析器并跟踪一些简单查询的锁获取/释放。我以前做过这样的事情，大致如下： 获取页面1 获取行1 获取行2 释放行1 获取行3 释放行2 获取页面2 释放页面1 ...... 我可能不是100%正确，但基本上就是这样的方法。因此，在读取行后释放锁定，或者更准确地说，在获取下一行的锁之后释放锁定。我怀疑这可能与保持遍历的一致状态有关。

- ahains

---

谢谢，你有没有参考文献可以证明这种行为？ - Charles Bretana

@Charles，我还没有看到任何概述这个的文档。但正如你所说，分析器输出是相当明确的 :) 如果你好奇的话，我建议你去做一下那个练习。 - ahains

@hainstech， 我计划在家庭办公室机器上抽出一些空闲时间来做这件事......不幸的是，在这个客户（政府机构）的现场，我无法访问任何正在运行的Sql Server上的分析工具。 - Charles Bretana

@IanWarburton，恐怕我已经好几年没有使用mssql或者profiler了，但是如果我没记错的话，在运行基本表查询时，我只需要在profiler中查看Lock:Acquired和Lock:Released事件（https://learn.microsoft.com/en-us/sql/relational-databases/event-classes/locks-event-category）即可。 - ahains

Answer 2

我不相信它会同时获取两个页面级别的锁。我认为只是因为事件发生得太快，所以在分析器中看起来是那样。如果像你想的那样发生，就会始终存在两个页面级别的锁，但是当运行具有共享锁的大型查询时，有时会通过此查询看到两个页面级别的锁，有时只有一个。

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = <SPID>

所以，我认为正在发生的是：

1. 获取：数据库共享锁、表共享锁、页面共享锁
2. 读取页面...同时释放页面上的锁并获取下一页的锁

第二个步骤的结果是，在sys.dm_tran_lock查询中有时会看到两个PAGE锁，有时一个，有时三个...这取决于同时操作中哪个更快。

- vic

Answer 3

关于具体文档，我找到了这个：

如果 READ_COMMITTED_SNAPSHOT 设置为 OFF（SQL Server 上的默认设置），
数据库引擎使用共享锁以防止其他事务在当前事务运行读操作时修改行。共享锁也会阻止语句读取其他事务修改的行，直到其他事务完成。共享锁类型确定何时释放锁。行锁在处理下一行之前被释放。页锁在读取下一页时被释放，表锁在语句完成时被释放。

来源：https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

- Ronnie Overby