Understanding SQL Server Transactions, Locks, Isolation Levels, and Deadlocks

1. Transactions

A transaction is a single unit of work that executes a series of operations such as queries, data modifications, or DDL statements.

1.1 What is a transaction

It groups related operations so they succeed or fail together.

1.2 Syntax

Explicit transaction boundaries

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

Implicit transaction

If you do not define boundaries, SQL Server treats each statement as an independent transaction and commits it automatically after execution.

1.3 ACID properties

Atomicity

The transaction must be an atomic unit; all changes are applied or none.

If a failure occurs before the commit is logged, SQL Server rolls back all modifications.

SQL Server can automatically roll back a transaction when an error occurs.

Minor errors (e.g., primary‑key conflict, lock timeout) may not trigger automatic rollback; you can handle them manually.

Use SELECT @@TRANCOUNT to check whether a transaction is open.

Consistency

Concurrent transactions must not conflict when reading or modifying data.

Consistency requirements depend on the application and can be controlled by isolation levels.

Isolation

Controls how concurrent users read and write data.

Locks are used to enforce isolation.

Durability

Before data pages are written to disk, modifications are first written to the transaction log.

On restart, SQL Server replays the log to recover committed transactions.

Recovery consists of a redo phase and an undo phase.

2. Locks

2.1 Locks in a transaction

SQL Server uses locks to implement isolation and protect resources from conflicting access.

2.2 Lock modes

Exclusive lock (X)

Requested when a transaction modifies data.

Held until the transaction completes.

Cannot coexist with any other lock on the same resource.

Shared lock (S)

Requested when a transaction reads data.

Held for the duration of the read operation.

Multiple transactions can hold shared locks on the same resource simultaneously.

2.3 Compatibility of exclusive and shared locks

If a row is being modified by one transaction, other transactions cannot read or modify it until the exclusive lock is released. If a row is being read, other transactions cannot obtain an exclusive lock on that row.

2.4 Lockable resource types

RID, KEY (row), PAGE, OBJECT (table), DATABASE, EXTENT, ALLOCATION_UNIT, HEAP, B‑tree.

2.5 Lock escalation

SQL Server may upgrade many fine‑grained locks (row or page) to a coarser lock (table) when a threshold is reached (e.g., 5 000 locks). If escalation fails due to conflicts, it retries after acquiring additional locks.

3. Blocking

3.1 What is blocking

When multiple transactions request incompatible locks on the same resource, the later request is blocked until the earlier lock is released. A lock timeout can limit how long a request waits.

3.2 Analyzing blocking

Example setup:

-- Create test table
CREATE TABLE dbo.myProduct(
    id int NOT NULL,
    price money NOT NULL
) ON PRIMARY;
INSERT INTO dbo.myProduct(id, price) VALUES (1, 10);

Open three query windows (Connection1‑3) and run the following statements:

-- Connection1
BEGIN TRAN;
UPDATE dbo.myProduct SET price = price + 1 WHERE id = 1;
-- Connection2
SELECT * FROM dbo.myProduct WHERE id = 1;
-- Connection3
SELECT session_id, resource_type, resource_description, request_mode, request_status
FROM sys.dm_tran_locks;

Results show which sessions hold exclusive or shared locks and which are waiting.

3.2.1 Using dynamic management views

sys.dm_tran_locks – shows lock owners and waiters.

sys.dm_exec_connections – connection times, last read/write, and recent SQL handle.

sys.dm_exec_sessions – login time, host name, program name, etc.

sys.dm_exec_requests – identifies blocking chains and wait durations.

sys.dm_exec_sql_text – retrieves the actual SQL text from a handle.

3.2.3 Resolving blocking

Set a lock timeout:

SET LOCK_TIMEOUT 5000; -- 5 seconds
-- To disable timeout
SET LOCK_TIMEOUT -1;

Or kill the blocking session:

KILL 52; -- terminates session 52 and rolls back its transaction

4. Isolation Levels

Isolation levels determine how concurrent transactions see each other's changes.

4.1 Basic concepts

Write operations acquire exclusive locks.

Read operations acquire shared locks by default.

Isolation level controls the duration and type of locks used for reads.

4.2 Types of isolation levels

READ UNCOMMITTED (dirty read)

READ COMMITTED (default)

REPEATABLE READ

SERIALIZABLE

SNAPSHOT

READ_COMMITTED_SNAPSHOT

4.3 Setting isolation level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Table hint example
SELECT * FROM dbo.myProduct WITH (READCOMMITTED);

When using the SET statement, separate words with spaces; table hints do not require spaces (e.g., NOLOCK = READ UNCOMMITTED).

4.4 Behavior of each level

READ UNCOMMITTED

Allows dirty reads; a reader can see uncommitted changes and does not block writers.

READ COMMITTED

Readers acquire shared locks; they block only when a writer holds an exclusive lock. Results may change between reads (non‑repeatable reads).

REPEATABLE READ

Shared locks are held for the entire transaction, preventing other transactions from acquiring exclusive locks on the same rows.

SERIALIZABLE

Uses range locks to prevent phantom rows; both reads and inserts that would affect the query result are blocked until the transaction ends.

4.5 Summary of isolation levels

Dirty read – reads uncommitted data.

Non‑repeatable read – data can change between reads.

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

Phantom read – number of rows returned changes.

Range lock – locks the key range accessed by a query.

5. Deadlocks

A deadlock occurs when two or more sessions wait for each other’s locks, creating a cycle of permanent blocking.

Example:

-- Session A
BEGIN TRAN;
UPDATE dbo.myProduct SET price = price + 1 WHERE id = 1;
SELECT * FROM dbo.myOrder WHERE id = 1;
-- Session B
BEGIN TRAN;
UPDATE dbo.myOrder SET customer = 'ddd' WHERE id = 1;
SELECT * FROM dbo.myProduct WHERE id = 1;

Session A holds an exclusive lock on myProduct and requests a shared lock on myOrder ; Session B holds an exclusive lock on myOrder and requests a shared lock on myProduct . Both wait forever, forming a deadlock.

SQL Server detects the deadlock, chooses a victim (usually the transaction with the lowest cost), aborts it, and rolls back its changes.

Deadlock mitigation tips

Keep transactions short to reduce lock hold time.

Access resources in a consistent order across transactions.

Set deadlock priority if one transaction should survive.

Use appropriate isolation levels to limit lock escalation.

By following these practices you can minimize the chance of deadlocks in SQL Server workloads.