The sp_getapplock secret

sp_getapplock is not very well advertised in SQL Server 2005, however it is a good way to synchronize code in a stored procedure.

Before finding out about sp_getapplock, I would SELECT from a table with an exclusive lock, like so:

BEGIN TRAN;
SELECT TOP 1 * FROM dbo.ATable with (tablockx, holdlock);
-- Do something while the lock is held, for instance:
UPDATE dbo.ATable SET FieldA = FieldA + 1 WHERE FieldB = 'something';
COMMIT TRAN;

This blocks other users from entering the section of code until the lock is released (when the transaction is committed). Normally you couldn’t do FieldA = FieldA + 1, because other users might be updating the table, however with the lock on the table you could.

This approach has some downsides

1. Other users can’t SELECT from the table.
2. If you rebuild indexes on the table with the “online=on” option, it will want to put a schema lock on the table, to prevent other schema changes. The exclusive lock prevents the rebuild from starting.

sp_getapplock is the built-in way to allow only one user in a section of code at a time, for example:

BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock @Resource = 'Lock ID', @LockMode = 'Exclusive';
IF @res >= 0
BEGIN
PRINT 'lock is held.';
END
COMMIT TRAN;

@Resource can be used for different locks that don’t interfere with each other.

You have to be careful which database you are “use”ing. SQL Server assumes that different databases have nothing to do with each other and won’t block each other.