sql-server - 高效事务，记录锁定

Question

我有一个存储过程，它选择 1 条记录。可以从不同 PC 上的多个不同应用程序调用存储过程。思路是存储过程带回下一条需要处理的记录，如果两个应用程序同时调用存储过程，不应该带回相同的记录。我的查询如下，我正在尝试尽可能高效地编写查询(sql 2008)。它能比这更有效吗？

CREATE PROCEDURE GetNextUnprocessedRecord
AS
BEGIN
    SET NOCOUNT ON;

    --ID of record we want to select back
    DECLARE @iID BIGINT     

    -- Find the next processable record, and mark it as dispatched
    -- Must be done in a transaction to ensure no other query can get
    -- this record between the read and update
    BEGIN TRAN

        SELECT TOP 1
            @iID = [ID]
        FROM
            --Don't read locked records, only lock the specific record
            [MyRecords] WITH (READPAST, ROWLOCK)
        WHERE
            [Dispatched] is null
        ORDER BY
            [Received]

        --Mark record as picked up for processing   
        UPDATE 
            [MyRecords]
        SET
            [Dispatched] = GETDATE()
        WHERE
            [ID] = @iID     

    COMMIT TRAN

    --Select back the specific record
    SELECT 
        [ID],
        [Data]
    FROM    
        [MyRecords] WITH (NOLOCK, READPAST)
    WHERE
        [ID] = @iID

END

Answer 1

使用 READPAST 锁定提示是正确的，并且您的 SQL 看起来不错。

我会添加 use XLOCK 虽然它也是 HOLDLOCK/SERIALIZABLE

...
[MyRecords] WITH (READPAST, ROWLOCK, XLOCK)
...

这意味着您获得 ID，并在继续和更新它时独占锁定该行。

编辑:在 Dispatched 和 Received 列上添加索引以使其更快。如果 [ID](我假设它是 PK)没有聚集，则包括 [ID]。并过滤索引，因为它是 SQL 2008

您也可以使用这种结构，无需 XLOCK 或 HOLDLOCK 即可一次性完成所有操作

UPDATE
    MyRecords
SET
    --record the row ID
    @id = [ID],
    --flag doing stuff
    [Dispatched] = GETDATE()
WHERE
    [ID] = (SELECT TOP 1 [ID] FROM MyRecords WITH (ROWLOCK, READPAST) WHERE Dispatched IS NULL ORDER BY Received)

UPDATE, assign, set in one