Detecting dirty reads from a stored procedure

问题:

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?

回答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.

回答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

回答3:

You need to replace this statement

UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  
WHERE CerealNumberID = @TableID

by this:

declare @CerealNumber int

SELECT @CerealNumber = CerealNumber  + 1
FROM CfgCerealNumber WITH (READCOMMITTED, READPAST, ROWLOCK) 
WHERE CerealNumberID = @TableID

if @CerealNumber is not null
    UPDATE CfgCerealNumber Set CerealNumber = @CerealNumber
    WHERE CerealNumberID = @TableID
else
    raiserror ('Row was locked by another update (no dirty read and no deadlock happen) or no Table Record Exists in CfgCerealNumber for ID:%d   ', 
              16,1, @TableID)

these table hints READCOMMITTED, READPAST, ROWLOCK will make sure that you have no dirty read and no deadlock

it will also let you decide if you still want to do an update

READCOMMITTED
Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. If the database option READ_COMMITTED_SNAPSHOT is OFF, the Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, the Database Engine does not acquire locks and uses row versioning.

READPAST
Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

Source MSDN Table Hints (Transact-SQL)

You might also need to use UPDLOCK and/or HOLDLOCK

回答4:

sp_getapplock will ensure that the transaction has an exclusive lock. The updates and reads will commit before the next thread can use it, so there cannot be any 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
   --ADDED CODE
    EXEC sp_getapplock @Resource='MyLock', @LockMode='Exclusive'
                , @LockOwner='Transaction', @LockTimeout = 15000
    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

回答5:

The begin transaction / commit transaction will ensure that you have no dirty reads.

There is a drawback in performance, if the procedure is run from inside another transaction the write lock will not be released until the most external transaction will be committed. This will serialize all threads and block concurrency.

See this example (suppose it takes a long time to execute):

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the write lock is established
    ...
commit tran -- the write lock is released

It is possible to release the lock before the end of the transaction, but you must create an application lock using procedures sp_getAppLock and sp_releaseAppLock inside the GetNextCerealIdentity procedure.

This can be quite tricky, you must pay attention or you can have both a deadlock or some dirty reads.

You must exec sp_getAppLock at the beginning of your procedure and sp_releaseAppLock at the end (before the return. In your example you have many return's so you will have to release the lock in many points)

Do not forget to release the lock also in case of errors. The lock will be released at the end of the transaction, but you want to release it at the end of the procedure! :-)

You must be sure that your application lock is the only one holding on the table with the counters (CfgCerealNumber).

Usually SQL Server will put a write lock on the table and will interfere with your lock because the write lock will be released at the end of the transaction and not at the end of your procedure.

You must change the procedure to a transaction level READ UNCOMMITED so that the UPDATE in your code will not generate write locks. remember to go back to COMMITTED in the same moment as you release the application lock.

If you acquire a lock in exclusive mode you will be sure that only one connection will be able to do execute the update / select on table CfgCerealNumber.

You can give the lock any name you want. I used the same name as the table (CfgCerealNumber) but it is not important. The most important thing is that you must use the same name for the initial get and for all release that you put in your code.

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
declare @RowCount int, @Err int
set nocount on
select  @NextKey = 0

-- replace begin tran with:    
EXEC sp_getapplock @Resource = 'CfgCerealNumber', @LockMode = 'Exclusive';  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*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)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  
    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

    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  

    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)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  
    set nocount off
    return 1
    end

-- replace commit transaction with:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
EXEC sp_releaseapplock @Resource = 'CfgCerealNumber';  

set nocount off
return 0
GO

If you change the procedure like that, my previous example will not give problems with concurrency:

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the lock is established AND released
    ...
commit tran -- common "write locks" are released

A possible addition is to use the BEGIN/END TRY .. BEGIN/END CATCH construct, so that you release the lock also in case of unexpected exceptions (this will give another pro: you will have a single point of exit from the procedure so you will have a single point where you must put the instructions to release the lock and put back the previous transaction isolation level.

See the following links: (sp_getAppLock) https://msdn.microsoft.com/en-us/library/ms189823.aspx and (sp_releaseAppLock) https://technet.microsoft.com/en-us/library/ms178602.aspx

回答6:

One option would be to use the sp_getapplock system stored procedure and use the built in locking of sql server to ensure serialized access to a resource.

CREATE PROC MyCriticalWork(@MyParam INT)      
AS
    DECLARE @LockRequestResult INT
    SET @LockRequestResult=0

    DECLARE @MyTimeoutMiliseconds INT
    SET @MyTimeoutMiliseconds=5000--Wait only five seconds max then timeouit

    BEGIN TRAN

    EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN

        /*
        DO YOUR CRITICAL READS AND WRITES HERE
        */

        --Release the lock
        COMMIT TRAN
    END ELSE
        ROLLBACK TRAN   

回答7:

Bacon Bits beat me to it, but using the OUTPUT clause will be the easiest way to work around your racing issue. Off course locking is an option too, although I think it will have slightly higher overhead. That said, using an IDENTITY column or a SEQUENCE is so much easier than trying to implement this functionality manually.

I took the liberty of putting the answer in your code and add a few remarks:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
set nocount on

DECLARE @RowCount int, @Err int
DECLARE @output TABLE (NextKey int)

begin transaction

    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber WITH (UPDLOCK) 
       Set CerealNumber = CerealNumber + 1
    OUTPUT inserted.CerealNumber INTO @output (NextKey)
     WHERE CerealNumberID = @TableID

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

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
            Rollback Transaction    
            raiserror ('GetNextCerealIDSeries Failed with Error: %d TableID: %d ', 16,1, @Err, @TableID)
            return -1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
            Rollback Transaction
            raiserror ('No Table Record Exists in CfgCerealNumber for ID:%d   ', 16,1, @TableID)
            return -1
        end

COMMIT TRANSACTION


/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = NextKey 
 From @output

return 0
GO

Remarks:

• There is no need to do SET NOCOUNT OFF again before exiting the stored procedure. As you go out of scope this setting will return back to what it was before you entered the stored procedure.
• I'm not sure you need the WITH (UPDLOCK), but it certainly won't hurt.
• I kept the transaction open for as short as possible, there is no reason to fetch the value from the table-variable inside the transaction.
• I think it's safer to first do a ROLLBACK and then do a RaisError() simply because the latter might cause the connection to be dropped by some client-software and/or you might be inside a TRY...CATCH. Both will break the flow of the commands and you'll end up with transaction count mismatch.
• YMMV but I've always been told to use negative return codes in case of an error. Positive return-codes might be used to indicate the number of rows.. although I've never ever seen that one used in practice.

回答8:

As already mentioned, you can use the auto increment built-in functionality, such as identity columns or sequence.

If you don't want this, you need to make the access to the table in serial manner: using application lock or other abilities.

For example, you can add the hints to the FIRST access to the table (in the transaction) like the below:

UPDATE CfgCerealNumber
Set CerealNumber = CerealNumber + 1
FROM CfgCerealNumber with (tablockx, holdlock)
WHERE CerealNumberID = @TableID

This will guarantee the sequential access to the table in all parallel threads.