高效交易，记录锁定 [英] Efficient transaction, record locking

问题描述

我有一个存储过程，它选择了1条记录.可以从不同PC上的几个不同应用程序中调用存储过程.想法是，存储过程将带回需要处理的下一条记录，并且如果两个应用程序同时调用存储的proc，则不应将同一条记录带回.我的查询在下面，我正在尝试尽可能高效地编写查询(sql 2008).能否比这做得更有效率?

I've got a stored procedure, which selects 1 record back. the stored procedure could be called from several different applications on different PCs. The idea is that the stored procedure brings back the next record that needs to be processed, and if two applications call the stored proc at the same time, the same record should not be brought back. My query is below, I'm trying to write the query as efficiently as possible (sql 2008). Can it get done more efficiently than this?

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

推荐答案

使用READPAST锁定提示是正确的，并且您的SQL看起来还可以.

Using the READPAST locking hint is correct and your SQL looks OK.

我将添加使用XLOCK，尽管它也是HOLDLOCK/SERIALIZABLE

I'd add use XLOCK though which is also HOLDLOCK/SERIALIZABLE

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

这意味着您获得了ID，并在进行和更新时独占锁定了该行.

This means you get the ID, and exclusively lock that row while you carry on and update it.

在已分派​​"和已接收"列上添加索引以使其更快.如果[ID](我认为是PK)未聚类，则包含[ID].还要过滤索引，因为它是SQL 2008

add an index on Dispatched and Received columns to make it quicker. If [ID] (I assume it's the PK) is not clustered, INCLUDE [ID]. And filter the index too because it's SQL 2008

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

You could also use this construct which does it all in one go without XLOCK or 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)

更新，分配并设置为一个

这篇关于高效交易，记录锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！