代码之家 › 专栏 › 技术社区 › Synesso

将SQL Server用作具有多个客户端的数据库队列

concurrency database sql-server sql

Synesso · 技术社区 · 14 年前

| ID | COMMAND | PROCESSED |
|  1 | ...     | true      |
|  2 | ...     | false     |
|  3 | ...     | false     |

客户端可能会获得一个命令来处理,如下所示:

select top 1 COMMAND 
from EXAMPLE_TABLE 
with (UPDLOCK, ROWLOCK) 
where PROCESSED=false;

但是,如果有多个worker,则每个worker都会尝试获取ID=2的行。只有第一个会得到悲观锁,其余的会等待。然后他们中的一个会得到第三排,等等。

编辑:

有几个答案建议使用表本身来记录进程中的状态。我认为这在一笔交易中是不可能的。(即,如果在提交txn之前没有其他工作人员会看到状态,那么更新状态有什么意义?)或许建议是:

# start transaction
update to 'processing'
# end transaction
# start transaction
process the command
update to 'processed'
# end transaction

7 回复 | 直到 14 年前

Remus Rusanu 14 年前

我建议你过去 Using tables as Queues . 正确实现的队列可以处理数千个并发用户和高达每分钟1/2百万的入/出队列操作的服务。在SQLServer2005之前,这个解决方案很麻烦,而且涉及到 SELECT 和一个 UPDATE 在单个事务中,并提供正确的锁提示组合,如gbn链接的文章中所述。幸运的是,自从SQLServer2005出现了OUTPUT子句之后,就有了一个更加优雅的解决方案,现在MSDN建议使用 OUTPUT clause :

您可以在应用程序中使用输出将表用作队列或应用程序不断添加或从表中删除行

1) 你需要自动出列。您必须找到该行,跳过所有锁定的行,并在单个原子操作中将其标记为“dequeued”,这就是 OUTPUT 条款生效:

with CTE as (
  SELECT TOP(1) COMMAND, PROCESSED
  FROM TABLE WITH (READPAST)
  WHERE PROCESSED = 0)
UPDATE CTE
  SET PROCESSED = 1
  OUTPUT INSERTED.*;

2) 你呢必须使用表上最左边的聚集索引键构造表 PROCESSED ID 被用作主键,然后将其作为聚集键中的第二列移动。是否在服务器上保留非集群密钥的争论 身份证件 专栏是开放的,但我强烈赞成不

CREATE CLUSTERED INDEX cdxTable on TABLE(PROCESSED, ID);

3) 您不能通过任何其他方式查询此表,只能通过出列。尝试执行Peek操作或尝试同时将表用作队列和 很有可能 导致死锁,并将显著降低吞吐量。

原子出列、搜索要出列的元素的readpass提示和基于处理位的聚集索引上最左边的键的组合确保了在高并发负载下非常高的吞吐量。

Community kfsone 7 年前

我的答案告诉你如何使用表作为队列。。。 SQL Server Process Queue Race Condition

你基本上需要“ROWLOCK,readpass,UPDLOCK”提示

JustBeingHelpful 11 年前

如果要序列化多个客户机的操作,只需使用应用程序锁即可。

BEGIN TRANSACTION

EXEC  sp_getapplock @resource = 'app_token', @lockMode = 'Exclusive'

-- perform operation

EXEC  sp_releaseapplock @resource = 'app_token'

COMMIT TRANSACTION

Macros 14 年前

您可以使用int来定义命令的状态,而不是使用布尔值来处理:

1 = not processed
2 = in progress
3 = complete

no_one 14 年前

可能更好的选择是使用trisSate处理列和version/timestamp列。然后,processed列中的三个值将指示行是正在处理、已处理还是未处理。

    CREATE TABLE Queue ID INT NOT NULL PRIMARY KEY,
    Command NVARCHAR(100), 
    Processed INT NOT NULL CHECK (Processed in (0,1,2) ), 
    Version timestamp)

您可能还需要添加一个客户机标识符,这样,如果客户机在处理它时死亡,它就可以重新启动,查看最后一行,然后从它所在的位置开始。

ZippyV 14 年前

我不想乱动桌子上的锁。只需创建两个额外的列,如IsProcessing(位/布尔值)和ProcessingStarted(日期时间)。当一个worker崩溃或者在超时后没有更新他的行时,您可以让另一个worker尝试处理数据。

Andomar 14 年前

一种方法是使用单个update语句标记行。如果您在 where set 子句之间不能有其他进程,因为行将被锁定。例如:

declare @pickup_id int
set @pickup_id = 1

set rowcount 1

update  YourTable
set     status = 'picked up'
,       @pickup_id = id
where   status = 'new'

set rowcount 0

return @pickup_id

它使用 rowcount 最多更新一行。如果找不到行, @pickup_id -1 .