Detailed Explanation of SQL Transactions, Concurrency, Locks, Isolation Levels, and Deadlocks

This article offers a thorough guide to SQL Server transaction management and concurrency control.

Transaction basics : A transaction is a unit of work that groups operations such as queries, data modifications, and DDL statements. It can be defined explicitly with BEGIN TRAN ... COMMIT TRAN or implicitly where each statement is auto‑committed.

ACID properties :

Atomicity – all changes are applied or none are.

Consistency – the database remains in a valid state after commit.

Isolation – concurrent transactions do not interfere with each other.

Durability – committed changes survive crashes via transaction logs.

Example of an explicit transaction:

BEGIN TRAN
INSERT INTO b(t1) VALUES(1)
INSERT INTO b(t1) VALUES(2)
COMMIT TRAN

Lock mechanisms : SQL Server uses exclusive (X) locks for writes and shared (S) locks for reads. Locks can be applied to resources such as RID, KEY, PAGE, TABLE, DATABASE, EXTENT, ALLOCATION_UNIT, HEAP, and B‑tree structures. Lock escalation upgrades fine‑grained locks (row/page) to coarser ones (table) when many locks are acquired.

Blocking : When multiple transactions request incompatible locks, the later request is blocked until the first releases its lock. Blocking can be diagnosed with DMVs such as sys.dm_tran_locks , sys.dm_exec_connections , sys.dm_exec_sql_text , sys.dm_exec_sessions , and sys.dm_exec_requests . The SET LOCK_TIMEOUT option controls how long a session waits, and KILL <spid> terminates a blocking session.

Isolation levels determine how concurrent reads and writes are handled. The main levels are:

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED (default) – prevents dirty reads but allows non‑repeatable reads.

REPEATABLE READ – holds shared locks for the transaction duration, preventing non‑repeatable reads.

SERIALIZABLE – adds range locks to prevent phantom rows.

SNAPSHOT and READ_COMMITTED_SNAPSHOT – use row versioning to provide consistent reads without blocking writers.

Each level can be set for a session with commands such as SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or via database options for snapshot isolation.

Phenomena summary :

Dirty read – reading uncommitted changes.

Non‑repeatable read – data changes between two reads within the same transaction.

Lost update – two transactions overwrite each other's changes.

Phantom read – new rows appear in a subsequent read.

Range lock – locks the key range to prevent phantom rows.

Deadlocks : A deadlock occurs when two or more transactions hold locks that each other needs, causing a circular wait. SQL Server detects deadlocks, selects a victim, rolls back its work, and releases its locks. Example commands for deadlock handling include SET LOCK_TIMEOUT 5000 and KILL 52 .

Overall, understanding transactions, lock types, isolation levels, and the tools for monitoring and resolving blocking and deadlocks is essential for building reliable, high‑performance database applications.