Production systems face concurrency challenges beyond basic locking: preventing double form submissions, safely incrementing counters, and coordinating across multiple application instances. SQL Server’s atomic operations, idempotency patterns, and application-level advisory locks solve these problems without requiring complex distributed coordination. These patterns are the difference between a system that works in testing (single user, no concurrency) and one that works in production (many concurrent users).
Production Concurrency Patterns
-- ── 1. Atomic increment — ViewCount update (no race condition) ────────────
-- UPDATE with arithmetic is atomic — no read-modify-write cycle needed
UPDATE dbo.Posts
SET ViewCount = ViewCount + 1
WHERE Id = @PostId;
-- SQL Server executes this as a single atomic operation
-- Multiple concurrent increments all succeed without conflict
-- This is NOT a race condition — SQL Server serialises row-level writes
-- ── 2. Idempotency — prevent double form submissions ─────────────────────
-- Client generates a unique key before submission:
CREATE TABLE dbo.IdempotencyKeys (
IdempotencyKey UNIQUEIDENTIFIER NOT NULL,
OperationType VARCHAR(50) NOT NULL,
ResultData NVARCHAR(MAX) NULL, -- store the result for replay
CreatedAt DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
ExpiresAt DATETIME2(7) NOT NULL,
CONSTRAINT PK_IdempotencyKeys PRIMARY KEY (IdempotencyKey, OperationType)
);
CREATE OR ALTER PROCEDURE dbo.usp_CreatePost_Idempotent
@IdempotencyKey UNIQUEIDENTIFIER,
@AuthorId NVARCHAR(450),
@Title NVARCHAR(200),
@Slug VARCHAR(200),
@Body NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON; SET XACT_ABORT ON;
-- Check if we've already processed this key
DECLARE @ExistingResult NVARCHAR(MAX);
SELECT @ExistingResult = ResultData
FROM dbo.IdempotencyKeys
WHERE IdempotencyKey = @IdempotencyKey AND OperationType = 'CreatePost';
IF @ExistingResult IS NOT NULL
BEGIN
SELECT @ExistingResult AS Result, 1 AS WasIdempotent;
RETURN; -- return the cached result
END
BEGIN TRANSACTION;
-- Create the post
INSERT INTO dbo.Posts (AuthorId, Title, Slug, Body, Status)
VALUES (@AuthorId, @Title, @Slug, @Body, 'draft');
DECLARE @NewId INT = SCOPE_IDENTITY();
-- Store the result for future duplicate requests
INSERT INTO dbo.IdempotencyKeys (IdempotencyKey, OperationType, ResultData, ExpiresAt)
VALUES (@IdempotencyKey, 'CreatePost',
'{"postId":' + CAST(@NewId AS VARCHAR) + '}',
DATEADD(HOUR, 24, SYSUTCDATETIME()));
COMMIT TRANSACTION;
SELECT '{"postId":' + CAST(@NewId AS VARCHAR) + '}' AS Result, 0 AS WasIdempotent;
END;
GO
-- ── 3. Application-level advisory lock (sp_getapplock) ───────────────────
-- Coordinate across multiple app instances without row-level locks:
BEGIN TRANSACTION;
DECLARE @LockResult INT;
EXEC @LockResult = sp_getapplock
@Resource = 'PublishPost_42', -- arbitrary lock name
@LockMode = 'Exclusive',
@LockOwner = 'Transaction',
@LockTimeout = 5000; -- wait up to 5 seconds
IF @LockResult < 0
BEGIN
ROLLBACK;
RAISERROR('Could not acquire lock. Please try again.', 16, 1);
RETURN;
END
-- Safe to proceed — no other instance is publishing this post
UPDATE dbo.Posts
SET IsPublished = 1, PublishedAt = SYSUTCDATETIME()
WHERE Id = 42 AND IsPublished = 0;
COMMIT TRANSACTION; -- releases the advisory lock automaticallyNote:
UPDATE Posts SET ViewCount = ViewCount + 1 WHERE Id = @PostId is atomic at the row level — SQL Server acquires an exclusive lock on the row, performs the read-modify-write internally, then releases the lock. Multiple concurrent increments are serialised at the row level and all succeed correctly. This is fundamentally different from the read-then-write pattern (SELECT then UPDATE in separate statements) which has a race condition. Always increment counters in a single UPDATE statement, never in a SELECT + UPDATE pair.Tip:
sp_getapplock provides application-level advisory locks — named locks that coordinate between application instances without locking specific rows or tables. They are useful for operations that must run exclusively across multiple app servers (like a scheduled job that should not run on two servers simultaneously, or a complex publish workflow). Unlike row locks, advisory locks are not tied to any database object — they simply prevent other callers from acquiring the same named lock concurrently.Warning: Idempotency keys should have an expiry and a cleanup job. An unbounded
IdempotencyKeys table accumulates every create/update operation forever. Add a scheduled job to delete expired keys: DELETE FROM dbo.IdempotencyKeys WHERE ExpiresAt < SYSUTCDATETIME(). Also ensure the idempotency key check and the operation itself are in the same transaction — otherwise two concurrent requests can both pass the check (seeing no existing key) and both insert, causing a duplicate key constraint violation that must be caught and handled.Common Mistakes
Mistake 1 — Read then update for counter increment (race condition)
❌ Wrong — SELECT @count = ViewCount ... ; UPDATE Posts SET ViewCount = @count + 1; two sessions read same value, both write same incremented value; one increment is lost.
✅ Correct — UPDATE Posts SET ViewCount = ViewCount + 1; single atomic statement.
Mistake 2 — Idempotency check and operation not in the same transaction (duplicate key race)
❌ Wrong — check key existence, then begin transaction, then insert; two requests both pass check before either inserts.
✅ Correct — begin transaction first, then check key existence (with XLOCK hint if needed), then insert.