sp_getapplock - when database locking isn't enough

sp_getapplock is used to lock on a keyword instead of tables in MS SQL Server. this is beneficial when your stored procedures are multi statement. This is often the case in complex business systems that must obey business rules where the stored procedures can have tens of steps within them, yet you don't want it all to sit in a transaction.

Shared vs. Exclusive Lock Queuing

It is important to note that if a shared lock is already held by a session, then an EXCLUSIVE lock is made to wait, that new requests for shared locks will wait until the exclusive lock has been granted.

I use this in a stock system where processes who want a "rough" number e.g. for reports request a shared lock. But processes that need an exact figure, or need to update the stock numbers request an exclusive lock.

Click on read more to see sample code and the testing procedure.

Example Code Used For Testing

To run this code you will need to start 3 instances of MS SQL Server Management Studio and connect to the same database 3 times.

Shared lock code


   BEGIN TRANSACTION;

   DECLARE @result INT;
  
   BEGIN TRY
  
       EXEC @result = sp_getapplock @Resource = 'Form1',
                      @LockMode = 'Shared', @LockTimeout = 300000;
       --EXEC @result = sp_getapplock @Resource = 'Form1',
       --    @LockMode = 'Exclusive', @LockTimeout = 300000;
       EXEC @result = sp_releaseapplock @Resource = 'Form1';
      
       COMMIT TRANSACTION
       PRINT 'COMMITTED'
   END TRY
   BEGIN CATCH
       PRINT 'rollback'
       ROLLBACK TRANSACTION
   END CATCH

Exclusive lock code


   BEGIN TRANSACTION;

   DECLARE @result INT;
  
   BEGIN TRY
  
       --EXEC @result = sp_getapplock @Resource = 'Form1',
       --    @LockMode = 'Shared', @LockTimeout = 300000;
       EXEC @result = sp_getapplock @Resource = 'Form1',
                      @LockMode = 'Exclusive', @LockTimeout = 300000;
       EXEC @result = sp_releaseapplock @Resource = 'Form1';
      
       COMMIT TRANSACTION
       PRINT 'COMMITTED'
   END TRY
   BEGIN CATCH
       PRINT 'rollback'
       ROLLBACK TRANSACTION
   END CATCH

Test Sequence

1. Debug session 1 using the shared code, and step through until you have requested the shared lock and the yellow code marker is pointing at the release lock statement. You now hold a shared lock.
2. Debug session 2 using the exclusive code, and step through until you hit the request line, at this point the process will hang while it waits for a lock.
3. Debug session 3 using the shared code, and step through until you hit the request line, at this point the process will hang while it waits in line behind the exclusive lock request. At this point Session 1 has the Shared lock granted, Session 2 is waiting for an exclusive lock and Session 3 is waiting after Session 2 for a shared lock.
4. go back to Session 1 and release the lock, your window in Session 2 will now come back to life as the lock can now be granted, Session 3 is still waiting.
5. go to Session 2 and step through to release the lock; Session 3 will now come back to life as its lock is granted.
6. to finish step through session 3 to release the lock and close the transaction.

Further reading

http://technet.microsoft.com/en-us/library/ms189823.aspx - Microsoft books on-line for sp_getapplock.

http://www.bizagi.com/ - the free and excellent software used to draw the diagram.