Prevent race conditions across multiple rows

Question

I have read a lot about preventing race conditions, but typically with one record in an upsert scenario. For example: Atomic UPSERT in SQL Server 2005

I have a different requirement, and it is to prevent race conditions across multiple rows. For example, say I have the following table structure:

GiftCards:
  GiftCardId int primary key not null,
  OriginalAmount money not null

GiftCardTransactions:
  TransactionId int primary key not null,
  GiftCardId int (foreign key to GiftCards.GiftCardId),
  Amount money not null

There could be multiple processes inserting into GiftCardTransactions and I need to prevent inserting if SUM(GiftCardTransactions.Amount) + insertingAmount would go over GiftCards.OriginalAmount.

I know I could use TABLOCKX on GiftCardTransactions, but obviously this would not be feasible for lots of transactions. Another way would be to add a GiftCards.RemainingAmount column and then I only need to lock one row (though with possibility of lock escalation), but unfortunately this isn't an option for me at this time (would this have been the best option?).

Instead of trying to prevent inserting in the first place, maybe the answer is to just insert, then select SUM(GiftCardTransactions.Amount), and rollback if necessary. This is an edge case, so I'm not worried about unnecessarily using up PK values, etc.

So the question is, without modifying the table structure and using any combination of transactions, isolation levels and hints, how can I achieve this with a minimal amount of locking?

Answer 1

I have run into this exact situation in the past and ended up using SP_GetAppLock to create a semaphore on a key to prevent a race condition. I wrote up an article several years ago discussing various methods. The article is here:

http://www.sqlservercentral.com/articles/Miscellaneous/2649/

The basic idea is that you acquire a lock on a constructed key that is separate from the table. In this way, you can be very precise and only block spids that would potentially create a race condition and not block other consumers of the table.

I've left the meat of the article below but I would apply this technique by acquiring a lock on a constructed key such as

@Key = 'GiftCardTransaction' + GiftCardId 

Acquiring a lock on this key (and ensuring you consistently apply this approach) would prevent any potential race condition as the first to acquire the lock would do it's work with all other requests waited for the lock to be released (or time out, depending on how your want your app to work.)

The meat of the article is here:

SP_getapplock is a wrapper for the extended procedure XP_USERLOCK. It allows you to use SQL SERVERs locking mechanism to manage concurrency outside the scope of tables and rows. It can be used you to marshal PROC calls in the same way the above solutions with some additional features.

Sp_getapplock adds locks directly to the server memory which keeps your overhead low.

Second, you can specify a lock timeout without needing to change session settings. In cases where you only want one call for a particular key to run, a quick timeout would ensure the proc doesn't hold up execution of the application for very long.

Third, sp_getapplock returns a status which can be useful in determining if the code should run at all. Again, in cases where you only want one call for a particular key, a return code of 1 would tell you that the lock was granted successfully after waiting for other incompatible locks to be released, thus you can exit without running any more code (like an existence check, for example). The synax is as follows:

   sp_getapplock [ @Resource = ] 'resource_name',
      [ @LockMode = ] 'lock_mode'
      [ , [ @LockOwner = ] 'lock_owner' ]
      [ , [ @LockTimeout = ] 'value' ]

An example using sp_getapplock

/************** Proc Code **************/
CREATE PROC dbo.GetAppLockTest
AS

BEGIN TRAN
    EXEC sp_getapplock @Resource = @key, @Lockmode = 'Exclusive'

    /*Code goes here*/

    EXEC sp_releaseapplock @Resource = @key
COMMIT

I know it goes without saying, but since the scope of sp_getapplock's locks is an explicit transaction, be sure to SET XACT_ABORT ON, or include checks in code to ensure a ROLLBACK happens where required.

Answer 2

My T-SQL is a little rusty, but here is my shot at a solution. The trick is to take an update lock on all transactions for that gift card at the beginning of the transaction, so that as long as all procedures don't read uncommitted data (which is the default behavior), this effectively will lock the transactions of the targeted gift card only.

CREATE PROC dbo.AddGiftCardTransaction
    (@GiftCardID int,
    @TransactionAmount float,
    @id int out)
AS
BEGIN
    BEGIN TRANS
    DECLARE @TotalPriorTransAmount float;
    SET @TotalPriorTransAmount = SELECT SUM(Amount) 
    FROM dbo.GiftCardTransactions WTIH UPDLOCK 
    WHERE GiftCardId = @GiftCardID;

    IF @TotalPriorTransAmount + @TransactionAmount > SELECT TOP 1 OriginalAmout 
    FROM GiftCards WHERE GiftCardID = @GiftCardID;
    BEGIN
        PRINT 'Transaction would exceed GiftCard Value'
        set @id = null
        RETURN
    END
    ELSE
    BEGIN
        INSERT INTO dbo.GiftCardTransactions (GiftCardId, Amount) 
        VALUES (@GiftCardID, @TransactionAmount);
        set @id = @@identity
        RETURN
    END
    COMMIT TRANS
END

While this is very explicit, I think it would be more efficient, and more T-SQL friendly to use a rollback statement like:

BEGIN
    BEGIN TRANS
    INSERT INTO dbo.GiftCardTransactions (GiftCardId, Amount) 
    VALUES (@GiftCardID, @TransactionAmount);
    IF (SELECT SUM(Amount) 
        FROM dbo.GiftCardTransactions WTIH UPDLOCK 
        WHERE GiftCardId = @GiftCardID) 
        > 
        (SELECT TOP 1 OriginalAmout FROM GiftCards 
        WHERE GiftCardID = @GiftCardID)
    BEGIN
        PRINT 'Transaction would exceed GiftCard Value'
        set @id = null
        ROLLBACK TRANS
    END
    ELSE
    BEGIN
        set @id = @@identity
        COMMIT TRANS
    END
END