共享读取锁何时释放? [英] When are shared read locks released?

问题描述

当SQL Server联机丛书说 读取操作完成后，资源上的共享(S)锁会在 中释放，除非将事务隔离级别设置为可重复读取或更高级别，或者设置为锁定提示用于在交易期间保留共享(S)锁."

When SQL Server Books online says that "Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction."

假设我们正在谈论的是行级锁，没有显式事务，在默认隔离级别(已提交读)，" 读操作 "指的是什么?到?

Assuming we're talking about a row-level lock, with no explicit transaction, at default isolation level (Read Committed), what does "read operation" refer to?

• 要读取一行数据吗?
• 读取单个8k IO页面吗?
• 或者直到完成创建锁的完整Select语句执行完毕，无论有多少其他行 参与其中?

• The reading of a single row of data?
• The reading of a single 8k IO Page ?
• or until the the complete Select statement in which the lock was created has finished executing, no matter how many other rows are involved?

注意:我需要知道的原因是我们有一个由数据层Web服务生成的几秒钟的只读select语句，该语句创建页面级共享读取锁，由于与行级冲突而产生死锁来自复制程序的独占更新锁，该更新程序使服务器保持更新. select语句相当大，有许多子选择，并且一个DBA提议我们重写它以将其分解为多个较小的语句(运行时间较短的段)，以减少持有锁的时间".因为这假设共享读取锁将一直保持到完整的select语句完成，否则将导致错误(如果在读取行或页面时释放了锁)，则该方法将无效....

NOTE: The reason I need to know this is we have a several second read-only select statement generated by a data layer web service, which creates page-level shared read locks, generating a deadlock due to conflicting with row-level exclusive update locks from a replication prcoess that keeps the server updated. The select statement is fairly large, with many sub-selects, and one DBA is proposing that we rewrite it to break it up into multiple smaller statements (shorter running pieces), "to cut down on how long the locks are held". As this assumes that the shared read locks are held till the complete select statement has finished, if that is wrong (if locks are released when the row, or the page is read) then that approach would have no effect whatsoever....

推荐答案

实际上看起来很有趣，您可能希望启动分析器并跟踪一些简单查询的锁获取/释放.我前阵子这样做，就像这样: 获取第1页 获取第1行 获取第2行 发布第1行 获取第3行 发布第2行 获取第2页 发布第1页 ...

It's pretty interesting to watch actually, you may want to fire up profiler and trace the lock acquisition/release of some simple queries. I did this awhile back, it was something like: acquire page 1 acquire row 1 acquire row 2 release row 1 acquire row 3 release row 2 acquire page 2 release page 1 ...

我可能不是100％正确的，但这基本上就是方法.因此，在读取行之后释放锁，或者更准确地说，是在获取下一行锁之后释放锁.我怀疑这可能与保持一致的遍历状态有关.

I may not be 100% correct, but that was basically the approach. So the lock is released after the row is read, or maybe more correctly it is after the next rows lock is acquired. I suspect this may have to do with keeping a consistent state for traversal.

这篇关于共享读取锁何时释放?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！