Question

This is an over-simplified explanation of what I m working on.
I have a table with status column. Multiple instances of the application will pull the contents of the first row with a status of NEW, update the status to WORKING and then go to work on the contents.
It s easy enough to do this with two database calls; first the SELECT then the UPDATE. But I want to do it all in one call so that another instance of the application doesn t pull the same row. Sort of like a SELECT_AND_UPDATE thing.

存储过程是最好的选择吗？

Answer 1

听起来像是队列处理场景，您希望仅有一个进程来提取给定的记录。

If that is the case, have a look at the answer I provided earlier today which describes how to implement this logic using a transaction in conjunction with UPDLOCK and READPAST table hints: Row locks - manually using them

最好用Sproc裹起来。

我不确定这是否是您想要做的事情，因此我没有投票关闭为重复。

Answer 2

你可以使用OUTPUT语句。

DECLARE @Table TABLE (ID INTEGER, Status VARCHAR(32))
INSERT INTO @Table VALUES (1,  New )
INSERT INTO @Table VALUES (2,  New )
INSERT INTO @Table VALUES (3,  Working )

UPDATE  @Table
SET     Status =  Working 
OUTPUT  Inserted.*
FROM    @Table t1
        INNER JOIN (
          SELECT  TOP 1 ID 
          FROM    @Table
          WHERE   Status =  New 
        ) t2 ON t2.ID = t1.ID

Answer 3

不完全正确，但您可以使用 SELECT ... WITH (UPDLOCK)，然后随后使用 UPDATE ... 。这是一种很好的原子操作，因为它告诉数据库您将要更新先前选择的内容，因此它可以锁定这些行，防止与其他客户端发生冲突。在Oracle和一些其他数据库（我想是MySQL）中，语法是 SELECT ... FOR UPDATE 。

注意：我认为您需要确保这两个语句在同一个事务内才能正常工作。

Answer 4

你应该在这里做三件事：

Lock the row you re working on
Make sure that this and only this row is locked
Do not wait for the locked records: skip the the next ones instead.

要做到这一点，您只需要发出这个命令：

SELECT  TOP 1 *
FROM    mytable (ROWLOCK, UPDLOCK, READPAST)
WHERE   status =  NEW 
ORDER BY
        date

UPDATE  …

在一次交易中。

Answer 5

储存的程序是前进的道路。你们需要研究交易。锡克勒服务器是出于这种原因生产的。

Answer 6

是的，可能使用行锁提示将其与其他线程隔离。

UPDATE
Jobs WITH (ROWLOCK, UPDLOCK, READPAST)
SET Status =  WORKING 
WHERE JobID =
(SELECT Top 1 JobId FROM Jobs WHERE Status =  NEW )

编辑：如Quassnoi所建议的那样，Rowlock可能更好，但在一个查询中执行更新的想法是一样的。

友情链接