30 June 2006 07:31
tonyrogerson
Assisting Concurrency by creating your own Locks (Mutexs in SQL)
There are times when we are faced with multiple concurrent connections (running something at the same time against the same tables) and some of the connections may need to insert the same data (same key or unique index). The business logic would be, if the row doesn't already exist then insert it otherwise get the surrogate key id or something similar.
Our table for the examples to follow :-
CREATE
TABLE Users (
logon_name varchar(50) not null
constraint pk_Users PRIMARY KEY,
some_data int not null
)
Now our logic:-
declare
@one_to_add varchar(50)
set @one_to_add = 'Trevor Dwyer'
BEGIN TRAN
IF NOT EXISTS (
SELECT *
FROM Users
WHERE logon_name = @one_to_add
)
BEGIN
INSERT Users ( logon_name )
VALUES( @one_to_add )
END
COMMIT
TRAN
In the above example we check for the existance of the row in Users and if it doesn't already exist we insert it. Whats wrong with this?
The SELECT * FROM Users bit will take shared locks for the duration the query runs, the locks aren't held even though we are in a transaction, worse still - there is not a row to lock because we are probably going to be inserting it.
Now to some tests, in order to provide a realistic test we must account for connections that manage to run the NOT EXISTS at the same time so get the same result (the row doesn't exist) and therefore both try the INSERT - because there is no blocking locking then that can happen, but you will only notice it once you ramp volumes up! To simulate this condition we must use a WAITFOR DELAY, saves us reproducing 'x' number of concurrent connections...
Open up two query windows and run the following SQL in each.
declare @one_to_add varchar(50)
set @one_to_add = 'Trevor Dwyer'
BEGIN TRAN
IF
NOT EXISTS (
SELECT *
FROM Users
WHERE logon_name = @one_to_add
)
BEGIN
WAITFOR DELAY '00:00:30'
INSERT Users ( logon_name )
VALUES( @one_to_add )
END
COMMIT
TRAN
TRUNCATE TABLE Users
One of the connections will fail with the error:-
Msg 2627, Level 14, State 1, Line 16
Violation of PRIMARY KEY constraint 'pk_Users'. Cannot insert duplicate key in object 'dbo.Users'.
Oh dear! Now, unless you had done a thorough scalability test with lots of concurrent users you would not have trapped that 'timebomb' problem.
So, lets look at how we can fix this concurrency problem.
Remember, the problem occurs when the row does not exist and because of the volume of connections trying to insert the same key / unique index values. Essentially we need to serialise the INSERT into the table, two options for creating blocking locks are XLOCK and HOLDLOCK locking hints; the XLOCK takes an exclusive lock on the row, actually XLOCK is no good in this situation because the row doesn't exist so how can you lock a row that doesn't already exist! Ok, what about HOLDLOCK? Well, HOLDLOCK will take a range lock so that's fine, but what happens?
Run the SQL again in two connections (notice we use HOLDLOCK) and check the output from sp_lock.
declare @one_to_add varchar(50)
set @one_to_add = 'Trevor Dwyer'
BEGIN TRAN
IF NOT EXISTS (
SELECT *
FROM Users WITH ( HOLDLOCK )
WHERE logon_name = @one_to_add
)
BEGIN
INSERT Users ( logon_name )
VALUES( @one_to_add )
END
COMMIT
TRAN
Anyway, one of the connections will fail, this time with a worse error :-
Msg 1205, Level 13, State 48, Line 14
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Ouch! Bit more dramatic than the violation of PRIMARY KEY error, so HOLDLOCK is not the way forward on this either.
What have we left? Remember - we need to serialise the INSERT; the transaction isolation level has no affect here either, the only way I've found to get round this problem is to use sp_getapplock.
The sp_getapplock stored procedure allows us to use our own locking, we get a choice of locking types, we want Exclusive. The script now becomes...
** Remember the WAITFOR is simply there to simulate concurrent users entering the code path before the INSERT.
declare
@one_to_add varchar(50)
set @one_to_add = 'Trevor Dwyer'
BEGIN
TRAN
IF
NOT EXISTS (
SELECT *
FROM Users
WHERE logon_name = @one_to_add
)
BEGIN
WAITFOR DELAY '00:00:30'
DECLARE @result int
EXEC @result = sp_getapplock @Resource = 'Unique Label for this INSERT', @LockMode = 'Exclusive'
IF @result NOT IN ( 0, 1 ) -- Only successful return codes
BEGIN
PRINT @result
RAISERROR ( 'Lock failed to acquire.', 16, 1 )
END
ELSE
BEGIN
IF NOT EXISTS (
SELECT *
FROM Users
WHERE logon_name = @one_to_add
)
INSERT Users ( logon_name, some_data )
VALUES( @one_to_add, 1 )
EXEC @result = sp_releaseapplock @Resource = 'Unique Label for this INSERT'
END
END
COMMIT
TRAN
TRUNCATE
TABLE Users
We need the second NOT EXISTS to catch the occaison where by the time we got into this code path but before we could take the application lock somebody has already been in and inserted the row.
Remember the lock is still part of your transaction so if you expand the logic out you may end up shooting yourself in the foot and creating deadlocks, for example - put the HOLDLOCK hint back on the NOT EXISTS query and you'll see what I mean, the getapplock returns a -3 which indicates it couldn't acquire the lock because its been chosen as a deadlock victim.
Just a note, sp_getapplock must be called inside an open transaction, if there is no transaction active then it returns an error.
Another note, and thanks to Chris for this - a return code of 0 or 1 means a successful lock acquired, I was only checking for 0 - the post has been modified.
Filed under: SQL Server