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

办理要领

我添加行使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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!