sql - 从存储过程中检测脏读

我有100个线程，每个线程都按以下定义调用存储过程。

如何防止脏读？

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％的时间内返回相同的值:

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

我已经将代码结构化为，通过将更新包装在事务中来防止脏读。

如何防止脏读？

最佳答案

如果您需要更新并返回更新的内容，那么我将使用the OUTPUT clause:

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

如果需要进行其他检查，则可以在从存储过程返回结果集之前，将OUTPUT输入声明的表变量中。

另一种选择是先在表上创建阻塞锁，然后更新:

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

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

但是我会放下我看到这仍然会引起问题的钱。我不太相信。