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
There GiftCardTransactions
may be multiple processes in and I need to prevent insertion if it SUM(GiftCardTransactions.Amount) + insertingAmount
goes over GiftCards.OriginalAmount
.
I know what I could use TABLOCKX
on GiftCardTransactions
, but obviously it would not be possible for a lot of transactions. Another way would be to add a column GiftCards.RemainingAmount
, 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 SUM(GiftCardTransactions.Amount)
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:
http://www.sqlservercentral.com/articles/Miscellaneous/2649/
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:
SP_getapplock
is a wrapper for an extended procedure XP_USERLOCK
. 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.
SP_getapplock
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.
Third, it SP_getapplock
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' ]
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 this is understandable, but since the locking scope SP_getapplock
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