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."
当SQL Server Books联机表示，一旦读取操作完成，就会释放“资源上的共享（S）锁定”，除非将事务隔离级别设置为可重复读取或更高，或者使用锁定提示来保留共享（ S）在交易期间锁定。“
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?
假设我们在默认隔离级别（Read Committed）上讨论的是没有显式事务的行级锁，那么“读操作”指的是什么？
- The reading of a single row of data?
- 读取单行数据？
- The reading of a single 8k IO Page ?
- 读取单个8k IO页面？
- or until the the complete Select statement in which the lock was created has finished executing, no matter how many other rows are involved?
- 或者直到创建锁的完整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....
注意：我需要知道的原因是我们有一个由数据层Web服务生成的第二个只读select语句，它创建页级共享读锁，由于与行级独占更新锁冲突而生成死锁来自保持服务器更新的复制程序。 select语句相当大，有许多子选择，一个DBA建议我们重写它以将其分解为多个较小的语句（较短的运行部分），“减少锁定持有的时间”。因为这假设共享读锁一直保持到完成的select语句结束，如果这是错误的（如果在行或页面被读取时释放锁）那么这种方法将无任何效果....
2 个解决方案
#1
3
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 ...
实际观察非常有趣，你可能想要启动探查器并跟踪一些简单查询的锁定获取/释放。我做了一段时间回来，它是这样的：获取第1页获取第1行获取第2行第1行获取第3行第2行获取第2页第1页...
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.
我可能不是100％正确，但这基本上是方法。因此，在读取行之后释放锁定，或者可能更正确地在获取下一行锁定之后释放锁定。我怀疑这可能与保持遍历的一致状态有关。
#2
1
I don't believe that it's acquiring two page level locks at the same time. I think it only appears in profiler that way because the events happen so quickly. if it occurs like you suspect, there would always be two page level locks, but when running a large query with shared lock, I sometimes see two page level locks and sometimes one through this query:
我不相信它同时获得两个页面级锁。我认为它只会出现在分析器中，因为事件发生得如此之快。如果它像您怀疑的那样发生，总会有两个页面级锁，但是当运行带有共享锁的大型查询时，我有时会看到两个页级锁，有时通过此查询看到一个：
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = <SPID>
So, what I think is happening is:
所以，我认为正在发生的事情是：
- acquire: db shared lock, table shared lock, page shared lock
- 获取：db共享锁，表共享锁，页共享锁
- page is read... simultaneous release lock on page AND acquire lock on next page
- 页面被读取...页面上的同时释放锁定并在下一页获取锁定
The result of two is that sometimes in the sys.dm_tran_lock query. I'm seeing two PAGE locks and sometimes one and a few times three.. depends on what occurs faster during simultaneous actions.
两个结果是有时在sys.dm_tran_lock查询中。我看到两个PAGE锁，有时一次和几次三次......取决于在同时操作期间发生的更快。
#1
3
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 ...
实际观察非常有趣，你可能想要启动探查器并跟踪一些简单查询的锁定获取/释放。我做了一段时间回来，它是这样的：获取第1页获取第1行获取第2行第1行获取第3行第2行获取第2页第1页...
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.
我可能不是100％正确，但这基本上是方法。因此，在读取行之后释放锁定，或者可能更正确地在获取下一行锁定之后释放锁定。我怀疑这可能与保持遍历的一致状态有关。
#2
1
I don't believe that it's acquiring two page level locks at the same time. I think it only appears in profiler that way because the events happen so quickly. if it occurs like you suspect, there would always be two page level locks, but when running a large query with shared lock, I sometimes see two page level locks and sometimes one through this query:
我不相信它同时获得两个页面级锁。我认为它只会出现在分析器中，因为事件发生得如此之快。如果它像您怀疑的那样发生，总会有两个页面级锁，但是当运行带有共享锁的大型查询时，我有时会看到两个页级锁，有时通过此查询看到一个：
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = <SPID>
So, what I think is happening is:
所以，我认为正在发生的事情是：
- acquire: db shared lock, table shared lock, page shared lock
- 获取：db共享锁，表共享锁，页共享锁
- page is read... simultaneous release lock on page AND acquire lock on next page
- 页面被读取...页面上的同时释放锁定并在下一页获取锁定
The result of two is that sometimes in the sys.dm_tran_lock query. I'm seeing two PAGE locks and sometimes one and a few times three.. depends on what occurs faster during simultaneous actions.
两个结果是有时在sys.dm_tran_lock查询中。我看到两个PAGE锁，有时一次和几次三次......取决于在同时操作期间发生的更快。