Application Locks: Serialize One Business Operation

Two workers can each decide that an invoice is ready to finalize before either worker records completion. A unique constraint may protect a final invoice number, but it does not coordinate every intermediate step. An application lock gives cooperating SQL Server sessions a named resource on which to serialize a specific business operation.

Choose the resource and ownership

Name the resource after the smallest business unit that must run exclusively, such as invoice:481 rather than all-invoices. A global name silently serializes independent customers. Conversely, inconsistent spelling produces independent locks and no protection. Centralize name construction, including tenant identifiers when invoice numbers are unique only within a tenant.

The resource belongs to a database and a database-principal namespace. Sessions connected to different databases do not compete merely because they pass the same text. Resource-name comparisons are case-sensitive. Define a canonical representation and stay within the documented length limit; do not allow truncation to create accidental collisions.

Transaction ownership is usually easier to reason about for a short database-only operation. Commit or rollback releases the lock. Session ownership is useful in some designs, but explicit release and connection-pool behavior then become part of correctness. A pooled connection is not a business-operation boundary.

Acquire, check, and finish

Run the following pattern in a test database with no outer transaction. Replace the marked location with the actual short database operation. The explicit return-code test is essential: an unsuccessful acquisition must not fall through into protected work.

IF @@TRANCOUNT <> 0
    THROW 50000, 'This example owns its transaction.', 1;
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    DECLARE @rc int;
    EXEC @rc = sys.sp_getapplock
        @Resource = N'invoice:481',
        @LockMode = 'Exclusive',
        @LockOwner = 'Transaction',
        @LockTimeout = 5000,
        @DbPrincipal = 'public';
    IF @rc < 0
        THROW 50001, 'Application lock was not acquired.', 1;
    -- Read durable operation record; perform short database work.
    SELECT @rc AS LockResult;
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

A nonnegative return code indicates acquisition; a negative code indicates a failure such as timeout, cancellation, or deadlock selection. The example treats all acquisition failures as unsuccessful operations and rolls back. Production code can classify failures for logging and bounded retry, but must not reinterpret them as permission to continue.

The five-second lock timeout applies to acquisition of this application lock. It is not an end-to-end execution deadline and does not bound later row-lock waits, network delays, or statements inside the transaction. Choose a separate request budget and ensure cancellation is followed by reliable transaction cleanup.

To test exclusion, open a transaction in window A and acquire the resource without completing it. Run the pattern in B: B should fail acquisition after its timeout. Roll back A and repeat B; acquisition should now succeed. Also test distinct invoice resources and verify that they can proceed independently. Always finish the deliberately open test transaction.

Keep durable guarantees in the data

An application lock protects only paths that acquire the same resource. An administrator's direct update or an older application version can bypass the convention. Retain unique constraints, foreign keys, and validation of legal state transitions. The lock coordinates cooperating work; it does not replace the database model.

Nor does release remember that an operation completed. If the client loses the response after commit, a retry can acquire the lock again. Store a durable operation identifier and completion outcome inside the same transaction as the business change. Under the lock, read that record first and return the existing outcome for a duplicate request.

Do not hold this transaction open while calling a payment service. A database rollback cannot reverse a successful external charge. For external effects, record an outbox item transactionally and process it through an idempotent delivery path. The application lock may still coordinate a short local transition, but the workflow needs a durable protocol across system boundaries.

Finally, if one transaction needs several named resources, acquire them in a consistent order. Application locks participate in deadlock scenarios too. Monitor acquisition failures, wait duration, and transaction age. The useful outcome is controlled contention for the same business key while unrelated work remains concurrent, with durable evidence that retries cannot repeat completed effects.

Technical references: Microsoft Learn: sp_getapplock · Microsoft Learn: sp_releaseapplock.