Two schedulers start the same job before either notices the other. With sp_getapplock, SQL Server can reserve that logical job name. Every competing entry point must request the same reservation.
Define the Resource the Job Owns
I identify every entry point before adding an application lock. A lock works only when all cooperating callers use it. An unmodified scheduler can still run the same work without asking.
sp_getapplock locks a named application resource. It doesn't need a row representing that resource. The name should identify the business operation you want to serialize.
The resource is scoped through the database and database principal. Matching text in different databases doesn't automatically describe the same lock. Keep the database context consistent across callers.
Resource name comparison is case-sensitive. Treat spelling and capitalization as part of the contract. A dynamically altered name can accidentally create a second independent lock.
Use an Exclusive lock for the one-job-at-a-time example. Other modes support other coordination patterns. Select the mode from the allowed concurrent behavior rather than from a copied script.
Call sp_getapplock With Transaction Ownership
A transaction-owned application lock requires an active transaction. It is released when that transaction completes. That matches a job whose protected database work belongs in one transaction.
The first-session script below deliberately holds the reservation briefly. Run it in a disposable database. The WAITFOR gives you time to try the second session.
The work placeholder is a harmless SELECT. Replace it with reviewed test work if needed. Don't hold a production transaction open merely to demonstrate waiting.
The TRY and CATCH ensure the owned transaction is rolled back after failure. SET XACT_ABORT ON supplies appropriate runtime-error behavior. THROW preserves the original failure.
The lock request uses an explicit timeout. Never ignore the returned value and continue anyway. A failed reservation means the caller hasn't earned permission to enter the protected work.
SET XACT_ABORT ON;
DECLARE @LockResult int;
BEGIN TRY
BEGIN TRANSACTION;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'Job:DailyImportDemo', @LockMode = N'Exclusive',
@LockOwner = N'Transaction', @LockTimeout = 5000;
IF @LockResult < 0 THROW 51000, 'The job reservation was not granted.', 1;
SELECT N'Protected test work' AS WorkDescription;
WAITFOR DELAY '00:00:15';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;Let the Second Caller Skip Deliberately
Open a second SSMS connection to the same database. Run this block while the first caller is waiting. The resource name and owner match the first request.
A zero timeout asks for immediate acquisition or failure. That fits a scheduler allowed to skip an already-running job. A scheduler required to wait should use a bounded positive timeout instead.
The second caller rolls back its test transaction when the reservation isn't granted. It doesn't enter the protected work. In my test it returned -1 while the first session waited, and 0 once that session finished. Its output records the decision rather than claiming success.
Skipping needs a business rule. A daily job already running is different from a job whose previous run failed. Keep scheduling and completion evidence outside the lock name alone.
What should the scheduler report when another copy is active? Define that status distinctly from completed and failed. Otherwise successful exclusion looks like mysterious missing work.
DECLARE @LockResult int;
BEGIN TRANSACTION;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'Job:DailyImportDemo', @LockMode = N'Exclusive',
@LockOwner = N'Transaction', @LockTimeout = 0;
IF @LockResult < 0
BEGIN
ROLLBACK TRANSACTION;
SELECT @LockResult AS LockResult, N'Skipped: reservation unavailable' AS JobStatus;
END
ELSE
BEGIN
SELECT @LockResult AS LockResult, N'Protected work can start' AS JobStatus;
COMMIT TRANSACTION;
END;
Interpret Every sp_getapplock Return Code
Zero means the lock was granted without waiting. One means it was granted after waiting. Nonnegative results therefore indicate successful acquisition for this use.
Negative one reports a timeout. Negative two reports cancellation. Negative three reports selection as a deadlock victim.
Negative 999 reports a parameter validation or another call error. Preserve the actual result for operational diagnosis. Collapsing every negative value into busy hides configuration failures.
A deadlock return deserves explicit transaction cleanup. Don't assume this application-lock result rolled back all your work for you. The owning transaction's state still needs handling.
I log the resource name and return code when reviewing overlapping job behavior. The lock itself isn't a job-history table. A reservation tells you who can enter, not whether the business work later succeeded.
Use Session Ownership When Work Spans Transactions
A session-owned application lock survives individual transaction commits. That fits coordination spanning several small committed batches. It also requires explicit release while the connection remains alive.
sp_releaseapplock must use the same resource and owner. Repeated successful acquisition requires matching release behavior. Don't accidentally accumulate acquisitions inside a loop.
The next script demonstrates cleanup on success and failure. It runs separate from the transaction-owned test. Use the same database context so the resource scope remains understandable.
Session ownership has connection-pooling implications. A logical job must release its reservation before returning the connection to the pool. Don't rely on a future physical disconnect as normal cleanup.
The release return code deserves checking too. A cleanup failure shouldn't disappear from operational evidence. Keep it visible without replacing the original business exception in a production handler.
DECLARE @AcquireResult int, @ReleaseResult int;
EXEC @AcquireResult = sys.sp_getapplock
@Resource = N'Job:BatchImportDemo', @LockMode = N'Exclusive',
@LockOwner = N'Session', @LockTimeout = 5000;
IF @AcquireResult < 0 THROW 51001, 'Session reservation unavailable.', 1;
BEGIN TRY
SELECT N'Work across committed batches belongs here' AS WorkDescription;
EXEC @ReleaseResult = sys.sp_releaseapplock
@Resource = N'Job:BatchImportDemo', @LockOwner = N'Session';
IF @ReleaseResult < 0 THROW 51002, 'Session reservation release failed.', 1;
END TRY
BEGIN CATCH
EXEC @ReleaseResult = sys.sp_releaseapplock
@Resource = N'Job:BatchImportDemo', @LockOwner = N'Session';
THROW;
END CATCH;Inspect the Held Application Locks
A third inspection connection can read sys.dm_tran_locks during the test pause. APPLICATION identifies these logical resources. request_owner_type helps distinguish transaction and session ownership.
The resource description isn't a complete readable job log. Application resource names also have limits on what remains visible. Use your own recorded resource name when connecting the observation to the job.
Permissions determine which sessions you can inspect. Review the server version's required state permission. An empty restricted view isn't evidence that nobody holds a lock.
Read granted and waiting requests together when diagnosing a timeout. The caller's returned code belongs beside that snapshot. One moment of observation doesn't reconstruct an earlier overlap.
SELECT request_session_id, resource_database_id, resource_description,
request_mode, request_status, request_owner_type
FROM sys.dm_tran_locks WHERE resource_type = N'APPLICATION';Keep the Job Contract Larger Than sp_getapplock
sp_getapplock prevents cooperating callers entering together. It doesn't make a partly completed job safe to rerun. Add idempotent work rules and durable completion records where required.
I keep the reservation interval as short as the business requirement permits. I also test timeout and cancellation paths. The happy path alone doesn't prove cleanup.
A named reservation makes the scheduling rule concrete. Test two callers and inspect the release behavior. The second scheduler doesn't need optimism, it needs a returned decision.
Give each lock resource a documented owner and purpose. Unrelated jobs should not accidentally share the same resource name. Related jobs need the same database and principal boundary before the lock can coordinate them.
Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Running SQL Agent Job After Completing Another Job.
An application lock is not a job result, it is a reservation for cooperating callers.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
