当大量行具有更新锁时，Select 查询会挂起
我正在设计一个读取队列表的程序。我使用锁定来确保多个实例不会拉取同一行。
我像这样锁定行：
BEGIN TRANSACTION
UPDATE top(10) Source with (ROWLOCK, READPAST, updlock)
SET status = status + 1
通过另一个连接，我读取这样的行：
SELECT COUNT(*) FROM Source WITH (ROWLOCK, READPAST, updlock)
select 语句中的计数不包括我已锁定的行。这正是我想要的。
当我选择前 10 行、或 100 行、甚至 1000 行时，此方法工作正常。在 4,690 行左右（不一致）时，选择开始挂起，直到提交事务。它不仅慢，而且慢。它等待事务结束。
这是一个测试。我真正的查询不会使用 top。它将使用联接，当锁定太多行时，这也会导致问题。
关于可能导致这种情况的任何想法？ 有没有更好的方法让多个实例读取表而不发生冲突？
I am designing a program that will read a queue table. I am using locking to make sure that multiple instances do not pull the same row.
I am locking rows like this:
BEGIN TRANSACTION
UPDATE top(10) Source with (ROWLOCK, READPAST, updlock)
SET status = status + 1
With another connection I read the rows like this:
SELECT COUNT(*) FROM Source WITH (ROWLOCK, READPAST, updlock)
The count from the select statement does not include the rows I have locked. This is exactly what I want.
This works fine when I pick the top 10 rows, or 100, or even 1000. Somewhere around 4,690 (it's not consistent) the select begins to hang until the transaction is committed. It's not just slow; it waits for the transaction to end.
This is a test. My real query will not be using top. It will use a join which also causes the problem when too many rows are locked.
Any ideas on what may cause this?
Is there a better way to have multiple instances read a table and not have conflicts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论