sql-server – 高效的事务,记录锁定

我有一个存储过程,它选择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

解决方法

使用READPAST锁定提示是正确的,您的sql看起来没问题.

我添加使用XLOCK虽然也是HOLDLOCK / SERIALIZABLE

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

这意味着您获得了ID,并在您继续并更新它时专门锁定该行.

编辑：在Dispatched和Received列上添加索引以使其更快.如果[ID](我认为它是PK)没有聚集,则INCLUDE [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

sql-server – 高效的事务,记录锁定

解决方法

猜你在找的MsSQL相关文章