Concurrency, Locking and Isolation Levels

Lock granularity: RID → KEY → PAGE → EXTENT → HoBT/PARTITION → TABLE (OBJECT) → DATABASE Lock escalation: roughly 5,000 locks on one object escalate to a table lock.

24.1 Concurrency Phenomena

Two transactions overwrite each other Reading uncommitted data

The same row reads differently within one transaction New rows appear in a repeated range query

24.2 Isolation Levels

No shared locks (NOLOCK) READ COMMITTED (default) Short shared locks READ COMMITTED SNAPSHOT (RCSI) Row versioning (statement-level) Shared locks held until commit

Row versioning (transaction-level), update conflict detection

ALTER DATABASE ShopDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ShopDB SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

24.3 Lock Modes

Read with intent to update (prevents conversion deadlocks) Signal locks at a lower granularity Compilation / DDL Bulk load with TABLOCK RangeS-S, etc.

Lock granularity: RID → KEY → PAGE → EXTENT → HoBT/PARTITION → TABLE (OBJECT) → DATABASE Lock escalation: roughly 5,000 locks on one object escalate to a table lock. Control it with ALTER TABLE ... SET (LOCK_ESCALATION = AUTO | TABLE | DISABLE).

24.4 Optimized Locking (SQL Server 2025 / Azure SQL)

Transaction ID (TID) locking plus Lock After Qualification (LAQ) sharply reduce lock memory and blocking. Requires ADR.

24.5 Table Hints

SELECT * FROM dbo.Orders WITH (NOLOCK);       -- dirty reads, may skip or duplicate rows! Avoid.
SELECT * FROM dbo.Orders WITH (UPDLOCK, HOLDLOCK) WHERE OrderID = 1;  -- safe upsert pattern
SELECT * FROM dbo.Queue WITH (READPAST, UPDLOCK, ROWLOCK);            -- queue processing
SELECT * FROM dbo.Orders WITH (TABLOCKX);

24.6 Blocking

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = 55;
EXEC sp_who2;  -- community tools: sp_WhoIsActive, sp_BlitzWho
KILL 55;

24.7 Deadlocks

Two or more sessions wait on each other in a cycle. SQL Server picks a victim (error 1205).

Capture: system_health Extended Events session (xml_deadlock_report), trace flag 1222.

Consistent access order and short transactions SET DEADLOCK_PRIORITY LOW Retry logic in the application

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (SELECT CAST(target_data AS XML) AS TargetData
      FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
      WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer') AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent);

24.8 Optimistic Concurrency with ROWVERSION

ALTER TABLE dbo.Products ADD RowVer ROWVERSION;
UPDATE dbo.Products SET UnitPrice = 99 WHERE ProductID = 1 AND RowVer = @OriginalRowVer;
IF @@ROWCOUNT = 0 THROW 50010, 'Row was modified by another user.', 1;

24.9 Application Locks

EXEC sp_getapplock @Resource = 'NightlyJob', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 0;
EXEC sp_releaseapplock @Resource = 'NightlyJob', @LockOwner = 'Session';