Avoiding multi-row race conditions
I've read a lot about avoiding race conditions, but usually with one entry per upsert script. For example: Atomic UPSERT in SQL Server 2005
I have a different requirement and it should prevent race conditions across multiple lines. For example, let's 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
may be multiple processes in and I need to prevent insertion if it
SUM(GiftCardTransactions.Amount) + insertingAmount
I know what I could use
, but obviously it would not be possible for a lot of transactions. Another way would be to add a column
, and then I only need to lock one row (albeit with a lock escalation option), but unfortunately this is not an option for me at the moment (would that be the best option?).
Rather than trying to prevent the insert in the first place, maybe the answer should just insert and then select
and rollback if necessary. This is an edge case, so I'm not worried about unnecessarily using PK values, etc.
So the question is, without changing the structure of the table and using any combination of transactions, isolation levels and hints, how can I achieve this with as little locking as possible?
source to share
I ran into this particular situation in the past and ended up using SP_GetAppLock to create a semaphore on the key to prevent a race condition. Several years ago I wrote an article on various methods. The article is here:
The basic idea is that you get a lock on the constructed key that is separate from the table. This way you can be very precise and only block blocks that could potentially create a race condition and not block other users of the table.
I left the meat in the article below, but I would apply this technique by getting a lock on a configured key like
@Key = 'GiftCardTransaction' + GiftCardId
Acquiring a lock on that key (and ensuring that this approach is consistently applied) will prevent any potential race condition, since the first to acquire the lock will handle all other requests waiting for the lock to exit (or timeout, depending on how your application should work.)
The meat of the article is here:
is a wrapper for an extended procedure
. It allows the use of the SQL SERVER locking mechanism to manage concurrency outside the scope of tables and rows. It can be used to marshal PROC calls in the same way as above, with some additional functionality.
adds locks directly to server memory which keeps your overhead to a minimum.
Second, you can specify a blocking timeout without requiring you to change your session settings. In cases where only one call to a specific key is required to start, a fast timeout ensures that proc does not delay execution of the application for very long.
returns a status, which can be useful in determining whether the code should run at all. Again, in cases where you only need one call for a specific key, a return code of 1 will tell you that the lock was successfully removed while waiting for other incompatible locks to be released, so you can exit without running any more code (for example, checking existence, for example). The syntax is as follows:
sp_getapplock [ @Resource = ] 'resource_name', [ @LockMode = ] 'lock_mode' [ , [ @LockOwner = ] 'lock_owner' ] [ , [ @LockTimeout = ] 'value' ]
/************** 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 this is understandable, but since the locking scope
is an explicit transaction, it is imperative that
SET XACT_ABORT ON
or include code checks to ensure that ROLLBACK occurs where needed.
source to share
My T-SQL is a little rusty, but here's my shot at the solution. The trick is to commit all transactions to that gift card at the start of the transaction, so that until all procedures read uncommitted data (which is the default behavior), this effectively blocks the transaction on the target 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 would be convenient to use the rollback operator, e.g .:
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
source to share