SELECT和UPDATE表,所以没有线程的重叠 [英] SELECT and UPDATE table so there is no overlap of Threads
问题描述
说我有如下表:
ID|Read
-------
1|true
2|false
3|false
4|false
...我需要阅读的最小ID,已[阅读] ==虚假; 。再加上,更新了我现在读
... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it.
所以,如果我执行我的存储过程dbo.getMinID,它将返回ID:2,和更新[阅读] - >真实的。
So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true.
CREATE PROCEDURE [dbo].[getMinID]
(
@QueryID INT OUTPUT
)
BEGIN
SELECT TOP 1 @QueryID = [ID] from Table
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID
END
的问题是,我有十(10)执行dbo.getMinID,同时异步线程,和我不能让他们选择在任何情况下相同的[ID]。我很担心,第二个线程我的我的SELECT和UPDATE语句之间执行,从而回到[ID]:在这两种情况下2
The problem is that I have ten (10) asynchronous Threads executing dbo.getMinID, at the same time, and I CANNOT have them select the SAME [ID] under any circumstances. I am worried that a second thread my execute between my SELECT and UPDATE statement, thus returning [ID]: 2 in both scenarios.
我怎么能保证我不选择/更新相同的记录两次,无论有多少个线程在存储过程行事?另外,请记住,表不变地增加新行,所以我不能锁表!
How can I ensure that I do not select/update the same record twice, no matter how many threads are acting upon the Stored Procedure? ALSO, please keep in mind that the table CONSTANTLY has new rows added, so I cannot lock the table!
推荐答案
如果你的意思并发安全队列类型的锁定,然后使用ROWLOCK,UPDLOCK,READPAST提示?
If you mean a concurrency safe queue type locking, then use ROWLOCK, UPDLOCK, READPAST hints?
BEGIN TRAN
SELECT TOP 1 @QueryID = [ID] from Table WITH (ROWLOCK, UPDLOCK, READPAST)
UPDATE Table SET [Read] = 1 WHERE [ID] = @QueryID
COMMIT TRAN -- TRAM
然而,在一个声明。像
WITH T AS
(
--ORDER BY with TOP , or perhaps MIN is better?
SELECT TOP 1 [Read], [ID] from Table
WITH (ROWLOCK, UPDLOCK, READPAST) ORDER BY [Read]
)
UPDATE
T
SET
[Read] = 1;
这篇关于SELECT和UPDATE表,所以没有线程的重叠的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!