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.