問題描述

我有 100 個線程，每個線程都調用如下定義的存儲過程.

I've got 100 threads that are each calling the stored procedure as defined below.

如何防止臟讀?

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity
    (@NextKey int output, @TableID int)
AS
    declare @RowCount int, @Err int

    set nocount on

    select  
        @NextKey = 0

    begin transaction

Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber 
    SET CerealNumber = CerealNumber + 1  
    WHERE CerealNumberID = @TableID

    SELECT 
        @RowCount = @@RowCount, 
        @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0            /* If Error gets here then exit         */
    BEGIN
        RAISERROR ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        ROLLBACK TRANSACTION

        set nocount off
        return 1
    END

    IF @RowCount = 0                /* No Record then assume table is not   */
                                /* been initialized for TableID Supplied*/
    BEGIN
        RAISERROR('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16, 1, @TableID)
        set nocount off
        Rollback Transaction
        return 1
    END

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
    FROM CfgCerealNumber 
    WHERE CerealNumberID = @TableID

    SELECT @Err = @@Error                       /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0                            /* If Error gets here then exit         */
    BEGIN
        RAISERROR('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16, 1, @Err, @TableID)
        Rollback Transaction    
        set nocount off
        return 1
    END

    commit transaction
    set nocount off
    return 0
GO

看起來這部分存儲過程在并行運行時大約有 0.01% 的時間返回相同的值:

It looks like this part of the stored procedure is returning the same value around 0.01% of the time when run in parallel:

SELECT @NextKey = CerealNumber 
FROM CfgCerealNumber 
WHERE CerealNumberID = @TableID

我通過將更新包裝在事務中來以防止臟讀的方式構建我的代碼.

I've structured my code in such a way to prevent dirty reads by wrapping the update in a transaction.

如何防止臟讀?

推薦答案

如果您需要更新并返回您更新的內容，那么我會使用 OUTPUT 子句:

If you need to update and return what you updated, then I would just use the OUTPUT clause:

UPDATE CfgCerealNumber 
SET CerealNumber = CerealNumber + 1 
OUTPUT INSERTED.CerealNumber
WHERE CerealNumberID = @TableID;

如果你需要額外的檢查，你可以在從存儲過程返回結果集之前輸出到一個聲明的表變量中.

If you need additional checking, you can OUTPUT into a declared table variable before returning the result set from the stored procedure.

另一種選擇是先在表上創建一個阻塞鎖，然后更新:

Another alternative would be to create a blocking lock on the table first, and then update:

SELECT @CerealNumber = CerealNumber + 1 
FROM CfgCerealNumber WITH (HOLDLOCK, UPDLOCK) 
WHERE CerealNumberID = @TableID;

UPDATE CfgCerealNumber
SET CerealNumber = @CerealNumber
WHERE CerealNumberID = @TableID;

但我會放下錢，因為我已經看到這仍然會導致問題.我不太相信它.

But I would put money down that I've seen this still cause problems. I trust it much less.

這篇關于從存儲過程中檢測臟讀的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！