为什么这个存储过程有时不能正常工作?
我有一个名为“Schedule”的表,它充当多个进程/计算机频繁访问该表的队列机制。 该过程的目标是拾取不超过@count行的行,这些行有资格被拾取(它们的LastCompletedProcessingId和LastStartedProcessingId必须匹配)并将它们标记为已拾取(将LastStartedProcessingId更改为NEWID()),这样下一个进程就不会尝试选取已经标记的行。
我的问题是,在极少数情况下,当多个客户端在非常接近的时间调用该过程时,多个客户端最终会得到相同的行。这怎么可能?我该如何避免呢?桌子本身并不大。 @timeout 不是问题,因为这些事情的处理时间不会超过 300 秒,而且我有一个日志,在进程获取多个记录之前,它们运行的时间不会超过 300 秒。这是在 SQL Azure 中运行的
你知道这是如何实现的吗? 谢谢
CREATE PROCEDURE X
@count int,
@timeout int = 300
AS
BEGIN
SET NOCOUNT ON;
DECLARE @batchId uniqueidentifier
SELECT @batchId = NEWID()
BEGIN TRAN
-- Update rows
UPDATE Schedule
WITH (ROWLOCK)
SET
LastBatchId = @batchId,
LastStartedProcessingId = NEWID(),
LastStartedProcessingTime = GETDATE()
WHERE
AccountId IN (
SELECT TOP (@count) AccountId
FROM Schedule
WHERE
(LastStartedProcessingId = LastCompletedProcessingId OR LastCompletedProcessingId IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) > @timeout) AND
(LastStartedProcessingTime IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) > Frequency)
ORDER BY (DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) - Frequency) DESC
)
-- Return the changed rows
SELECT AccountId, LastStartedProcessingId, Frequency, LastProcessTime, LastConfigChangeTime
FROM Schedule
WHERE LastBatchId = @batchId
COMMIT TRAN
END
I have a table called "Schedule" that serves as a queue mechanism for multiple processes/computers hitting the table on a frequent basis.
The goal of the proc is to pick up rows no more then @count of rows, that qualify to be picked up (their LastCompletedProcessingId & LastStartedProcessingId's must match) and mark them as having been picked up (change LastStartedProcessingId to NEWID()), so that the next process doesn't try to pick up already marked rows.
My issue is that once in a rare while when the proc is called at very near times by multiple clients, multiple clients end up with the same rows. How is this possible? How do I avoid it? The table itself is not huge. @timeout is not an issue as these things do not take more then 300seconds to process, and i have a log that prior to the processes picking up multiple records, they didnt run over 300seconds. This is running in SQL Azure
Any thoughts of how this can be possible?
Thanks
CREATE PROCEDURE X
@count int,
@timeout int = 300
AS
BEGIN
SET NOCOUNT ON;
DECLARE @batchId uniqueidentifier
SELECT @batchId = NEWID()
BEGIN TRAN
-- Update rows
UPDATE Schedule
WITH (ROWLOCK)
SET
LastBatchId = @batchId,
LastStartedProcessingId = NEWID(),
LastStartedProcessingTime = GETDATE()
WHERE
AccountId IN (
SELECT TOP (@count) AccountId
FROM Schedule
WHERE
(LastStartedProcessingId = LastCompletedProcessingId OR LastCompletedProcessingId IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) > @timeout) AND
(LastStartedProcessingTime IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) > Frequency)
ORDER BY (DATEDIFF(SECOND, LastStartedProcessingTime, GETDATE()) - Frequency) DESC
)
-- Return the changed rows
SELECT AccountId, LastStartedProcessingId, Frequency, LastProcessTime, LastConfigChangeTime
FROM Schedule
WHERE LastBatchId = @batchId
COMMIT TRAN
END
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
不要使用 ROWLOCK 提示,而是将isolation_level 设置为 SERIALIZABLE 来运行更新。
此外,如果在更新中使用 OUTPUT 子句,则更新完成后即可获得受影响行的列表。这意味着您可以更快地结束事务 1 DML 语句并保持 ACIDity。
Instead of using the ROWLOCK hint, run your update with the isolation_level set to SERIALIZABLE.
Also, if you use an OUTPUT clause in your update, you can have the list of affected rows as soon as the update is complete. That means you can end your transaction 1 DML statement sooner and maintain your ACIDity.
也许这是你的问题?
来源
May be this is your issue?
Source