SQL Server 隔离级别 - 可重复读取
我无法理解为什么会发生这种情况。我很确定我理解这个理论，但一定还有其他我看不到的事情发生。
表 A 具有以下架构：
ID [Primary Key]
Name
Type [Foreign Key]
SprocA 将隔离级别设置为可重复读取，并从表 A 中选择具有 Type=1 的行。它还会更新这些行。
SprocB 从表 A 中选择具有 Type=2 的行。
现在考虑到这些是完全不同的行集，如果我同时执行两者（并放置 WAITFOR 调用以减慢速度），SprocB 直到 SprocA 才会完成。
我知道这与类型查询有关，就好像我根据主 ID 进行选择一样，它允许并发访问表。
有人透露任何信息吗？
干杯
I'm having problems getting my head round why this is happening. Pretty sure I understand the theory, but something else must be going on that I don't see.
Table A has the following schema:
ID [Primary Key]
Name
Type [Foreign Key]
SprocA sets Isolation Level to Repeatable Read, and Selects rows from Table A that have Type=1. It also updates these rows.
SprocB selects rows from Table A that have Type=2.
Now given that these are completely different rowsets, if I execute both at the same time (and put WAITFOR calls to slow it down), SprocB doesn't complete until SprocA.
I know it's to do with the query on Type, as if I select based on the Primary ID then it allows concurrent access to the table.
Anyone shed any light?
Cheers
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
通过为隔离级别设置“可重复读取”，您将对读取的所有数据持有共享锁，直到事务完成。直到您提交或回滚为止。
这将降低应用程序访问此数据的并发性。因此，如果您的第一个过程从表中进行 SELECTS，然后在事务中调用 WAITFOR，然后再次进行 SELECTS 等，那么您将一直持有共享锁，直到您提交事务或进程完成。
如果这是您正在使用的测试过程，请尝试在每次选择后添加 COMMIT，看看这是否有助于第二个过程同时运行。
祝你好运！
凯文
With Repeatable Read set for the isolation level, you will hold a shared lock on all data you read until the transaction completes. That is until you COMMIT or ROLLBACK.
This will lower the concurrency of your application's access to this data. So if your first procedure SELECTS from table then calls a WAITFOR then SELECTS again etc within a transaction you will hold the shared lock the entire time until you commit the transaction or the process completes.
If this is a test procedure you are working with try added a COMMIT after each select and see if that helps the second procedure to run concurrently.
Good luck!
Kevin
SQL Server 使用索引来执行范围锁（这是可重复读取经常使用的），因此如果您在 Type 上没有索引，它可能会锁定整个表...
SQL Server uses indexes to do range locks (which is what repeatable reads often use) so if you don't have index on Type perhaps it locks entire table...
需要记住的是，锁定的行对于其他进程来说是黑匣子。
您知道 SprocA 只是读取 type = 1，而 SprocbB 只是读取 type = 2。
但是，SprocB 不知道 SprocA 将要对这些记录执行什么操作。在事务完成之前，SprocA 可能会将所有记录更新为 type = 2。在这种情况下，如果不等待 SprocA 完成，SprocB 将无法正常工作。
在执行范围锁定/批量更改时保持并发性很困难。
The thing to remember is that the locked rows are black boxes to the other process.
You know that SprocA is just reading for type = 1 and that SprocbB is just reading for type = 2.
However, SprocB does not know what SprocA is going to do to those records. Before the transaction is completed, SprocA may update all of the records to type = 2. In that case, SprocB would be working incorrectly if it did not wait for SprocA to complete.
Maintaining concurrency when performing range locks / bulk changes is tough.