从存储过程中检测脏读

2021-09-10 00:00:00 sql tsql sql-server

我有 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.

相关文章