Lesson 1

Transactions and Locking in SQL Server

Transactions and locking are foundational to how SQL Server protects data correctness under concurrency. A transaction groups one or more Transact-SQL statements into a single unit of work so the database can guarantee predictable outcomes when something fails, when multiple users change the same data, or when reads occur while writes are in progress. This module introduces transaction fundamentals, isolation levels, locking behavior, deadlocks, and modern concurrency options that let you balance throughput with consistency in SQL Server 2025.

Why Transactions Matter

In production systems, the database is rarely serving one statement at a time. Hundreds or thousands of sessions can be reading and writing concurrently. Transactions provide the boundary SQL Server uses to decide when changes become visible to other sessions and whether a multi-step operation should be made permanent or undone.

A transaction begins explicitly (or implicitly), performs work, and then ends with a COMMIT (make changes durable) or a ROLLBACK (undo the work). This is the practical meaning of ACID in SQL Server:

Atomicity: all statements succeed as a unit or the unit is undone.
Consistency: constraints and rules remain valid at transaction boundaries.
Isolation: concurrent transactions do not corrupt each other’s view of data.
Durability: committed changes survive failures (subject to the configured durability model).

Learning Objectives

After completing this module, you will be able to:

Identify and define transactions
Define isolation levels and explain their tradeoffs
Identify and describe nested transactions and their practical impact
Describe locking behavior and how deadlocks occur
Create local and distributed transactions and know when to avoid distributed scope
Handle errors safely to prevent partial updates and orphaned work

The next lesson begins with transaction scope and basic transaction control statements.

Transaction Scope and Control

SQL Server treats a transaction as the boundary for consistency and recovery. Within the boundary, SQL Server may hold locks, maintain row versions, log changes, and enforce constraints. The developer’s job is to keep transactions correct and short: long-running transactions are a common cause of blocking, lock escalation, and deadlocks.


BEGIN TRANSACTION;

-- One unit of work: either everything succeeds, or nothing does.
UPDATE dbo.Account
SET Balance = Balance - 100
WHERE AccountId = 101;

UPDATE dbo.Account
SET Balance = Balance + 100
WHERE AccountId = 202;

COMMIT;

The module will also emphasize safe rollback patterns using TRY...CATCH and transaction state checks so that failures do not leave sessions in an unusable transaction state.

Concurrency: Locks, Blocking, and Row Versioning

Concurrency control answers a simple question: how can many sessions read and write the same tables at the same time without producing incorrect results? SQL Server uses two primary mechanisms:

Lock-based concurrency: shared locks for reading, exclusive locks for writing, plus intent locks and range locks where needed.
Row-versioning concurrency: readers can access consistent versions while writers proceed, reducing blocking in many workloads.

You will see how these mechanisms interact with isolation levels. Locking is normal and necessary, but excessive blocking is usually a design or tuning signal: missing indexes, long transactions, poor access patterns, or inappropriate isolation choices.

Isolation Levels: Choosing the Right Guarantees

Isolation controls what anomalies a transaction may observe (dirty reads, non-repeatable reads, phantom rows) and how much contention it can create. SQL Server offers lock-based isolation levels and row-versioning options that can substantially change runtime behavior.

READ UNCOMMITTED

READ UNCOMMITTED allows statements to read rows modified by other transactions that have not yet committed. At this level, SQL Server does not acquire shared locks for reads and is not blocked by exclusive locks in the typical way, which can reduce blocking. The tradeoff is correctness: the session can see changes that later roll back (dirty reads), and the result set can shift during execution (rows can appear, disappear, or change values).

This isolation level is commonly associated with the NOLOCK table hint. It should be treated as an optimization of last resort for non-critical reporting queries where approximate answers are acceptable and the risks are explicitly understood.

In many systems, a better approach to reducing reader/writer contention while preserving correctness is to use row versioning:

READ COMMITTED with Read Committed Snapshot: enable READ_COMMITTED_SNAPSHOT at the database level so readers can use row versions.
SNAPSHOT isolation: transactions read a consistent point-in-time view, reducing blocking and providing repeatable reads within the transaction.

Additional Topics Covered in This Module

Transactions and locking are broader than BEGIN/COMMIT and isolation keywords. This module also introduces related topics that influence correctness and performance in SQL Server 2025:

Implicit vs. explicit transactions: how session settings affect transaction boundaries and troubleshooting.
Lock escalation: why SQL Server may promote many fine-grained locks into fewer coarse locks, and what that means for blocking.
Deadlocks: how cycles occur, how SQL Server chooses a victim, and how to redesign access patterns to prevent them.
Isolation anomalies: dirty reads, non-repeatable reads, and phantoms—what they look like and which isolation levels prevent them.
Optimistic concurrency design: when row-versioning makes reads scale better than locking.
Distributed transactions: when they are required, why they are expensive, and how modern architectures often reduce the need for them.
Error handling and XACT_STATE: how to guarantee that failures do not leave partially applied changes.
Operational tooling: using DMVs to observe blocking chains, lock waits, and transaction log pressure during incidents.

By the end of the module, you should be able to reason about the tradeoffs between consistency and concurrency, choose an isolation level intentionally, and implement transactions that remain correct under real-world multi-user load.