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:

Comments

# Interesting Finds: June 30, 2006 AM

30 June 2006 16:12 by Jason Haley

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

03 July 2006 08:12 by GBN

Some good thoughts on the NOT EXISTS - not something I'd ever considered.

We discussed this article in the office and our worry is what happens if the code (SQL 2000) falls over before the user defined lock is released?

What about inserting the new row anyway then delete if a duplicate record (assuming key allow this?)?
Does this improve anything?

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

03 July 2006 08:54 by tonyrogerson

Because you are in a transaction most failures will result in the transaction being rolled back, to be honest - I really should have checks on @@ERROR after the INSERT too.

The application lock is scoped within a transaction, if you try and execute it when you have no transaction open then you get this error:

You attempted to acquire a transactional application lock without an active transaction.

So, in a nutshell; if a failure occurs then you'd pick it up with @@ERROR <> 0 and the ROLLBACK or implicit ROLLBACK will release the lock.

Tony.

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

05 July 2006 17:40 by cope360

Nice post, Tony.  This helps with a project I am working on.

When checking the result from sp_getapplock, I think you want to treat >= 0 as success since a result code of 1 is still a success.

BOL says:
0 = Lock was successfully granted synchronously.
1 = Lock was granted successfully after waiting for other incompatible locks to be released.

Chris

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

05 July 2006 18:06 by tonyrogerson

Thanks Chris, I've updated the entry to reflect that.

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

07 July 2006 12:58 by beckylou

The proposed solution implies that you have only one point of entry in your whole application for adding entries into Users, which is often not the case.

We do have some situations which could benefit from this (stored procs which generate global temporary tables for export using BCP) which can't be run at the same time.

-

Another alternative to the above problem is add the data and do the check at the same time.  You have to use Serializable isolation level to prevent phantom records being inserted.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

INSERT Users ( Login , some_data )
SELECT Login , some_data FROM ( @login AS Login , 'foo' as some_data ) NewData
LEFT JOIN Users ON NewData.Login = Users.Login
WHERE Users.PK IS NULL
SELECT @PK = SCOPE_IDENTITY() , @Err = @@ERROR , @Rows = @@ROWCOUNT

IF @Err != 0 BEGIN ROLLBACK TRAN RETURN END
IF @Rows = 0 BEGIN PRINT 'Already Exists' RETURN END
PRINT 'New Record ' + CAST( @PK AS VARCHAR(11))

COMMIT TRAN

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

07 July 2006 13:50 by tonyrogerson

Not really, you can use the same logic in any parts; good practice dictates we don't duplicate code so we should strive for entering users using the same stored procedure, anyway thats an aside and detracts from the technical side of this.

I like the use of outer join - neat.

Doing some testing, I've run up the above SQL that uses SERIALISABLE with 7 concurrent connections all starting (WAITFOR TIME) at the same time, at least one of them deadlocked which you wouldn't get with the solution I propose.

It does prevent the dup key, but serialisable is very heavy and has a big effect on concurrency so may have other side effects as well as the deadlock.

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

17 July 2006 16:42 by grambowk

Interesting article - I've been wondering about the same problems you've brought up here and I have a couple of questions.

What would happen if you checked for the existance within the insert statement's where clause?

e.g.

INSERT Users ( logon_name, some_data )
select  @one_to_add, 1
where not exists
       (SELECT *
        FROM Users
        WHERE logon_name = @one_to_add)

Might possibly need a locking hint in the subquery?

Unfortunately, it isn't easy to test this because you cannot put a "waitfor delay" in the subquery.  But I was wondering if you knew what locks are applied at each stage of the above statement (or specifically, during the where part of the statement).

I've got several reservations about using sp_getapplock.

Firstly, with sp_getapplock all processes that are trying to insert into the table will be prevented from doing so for the duration of the transaction.  You might as well use a table lock as a locking hint in the first example.

if exists(select * from Users with (tablockx, holdlock).....)

Secondly, instead of being blocked, an error is going to be raised so those statements will fail.

Imagine you have a highly transactional system where loads of records are being inserted into the above table per second.  And for arguments sake let's say that it takes 10 seconds to run the above transaction.

For those 10 seconds, each process trying to insert into the table will fail with an error even though they might be trying to insert different values.  At least, with a table lock, the processes will wait until the resource isn't available.

Admittedly a table lock is hardly ideal and it would also lock other processes from doing updates/deletes/etc.

Regards,

Karl

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

17 July 2006 16:56 by tonyrogerson

Hi Karl,

The other processes will block unless you specify @LockTimeout so it works just like normal locks, the test I've just done the second connection is stilling waiting after 6 minutes.

The problem with using normal locking hints like tablockx is that its more heavy handed, basically tablockx will prevent selects against the table etc.... By using sp_getapplock you are only using it to control execution along a specific code path, people could still be inserting into the table (so long as that code path hasn't the sp_getapplock logic....).

Holdlock and serialisable causes deadlocks.

This solution came out of a problem one of my clients had - 32 concurrent connections reading a huge number of rows from a file and needing to control inserting of user names, it was not good enough for the SQL to fail with a constraint violation instead they wanted more granularity - it actually improves concurrency and the throughput....

Using NOT EXISTS has the same problem used on the INSERT as on the IF, locks aren't applied in the order you want them (shared locks on the query and then exclusive locks for the insert - in that order).

Tony.

# re: Assisting Concurrency by creating your own Locks (Mutexs in SQL)

17 July 2006 17:49 by grambowk

Thanks for the reply Tony.

I didn't realise sp_getapplock behaved in the same way as normal locks.  I'd assumed that it returned a result immediately.

Shame about the NOT EXISTS solution.  I was hoping that the shared locks would be held for the duration of insert statement rather than just the duration of the subquery.

Regards,

Karl