Detecting dirty reads from a stored procedure

Question

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

How do I prevent dirty reads?

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

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.

How do I prevent dirty reads?

Answer 1

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.

Answer 2

You can avoid the problem by using the @variable = column = expression syntax as described in the Books Online. Also, since the statement executes in an single-statement automatic transaction, you can avoid explicit transaction.

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE PROCEDURE GetNextSerialIdentity
      @NextKey int output
    , @TableID int
AS
SET NOCOUNT ON;

UPDATE dbo.CfgSerialNumber
SET @NextKey = SerialNumber = SerialNumber + 1
WHERE SerialNumberID = @TableID;

IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 
                  16,1, @TableID);
END
GO