| Lesson 2 | What is a SQL Server transaction? |
| Objective | Define transactions and explain how they are used in SQL Server. |
Define Transactions and How They Are Used in SQL Server
A SQL Server transaction is a unit of work that groups one or more statements so they succeed or fail together. Transactions are the mechanism SQL Server uses to protect data integrity when an operation spans multiple statements, when failures occur mid-operation, or when many users read and write concurrently. In practical terms, transactions prevent partial updates: if any step fails, SQL Server can roll back the entire unit of work to the state that existed before the transaction began.
Purpose of Transactions
Transactions exist to make database changes reliable. Many real-world operations require multiple statements—such as transferring funds, allocating inventory, or inserting a parent row plus several child rows. If the operation is not transactional, a failure after step 2 of 5 can leave the database in an invalid state (for example, a debit without a matching credit).
With a transaction, SQL Server can guarantee one of two outcomes:
- Commit: all intended changes are made durable and become visible according to isolation rules.
- Rollback: all changes made by the transaction are undone, restoring the database to its prior consistent state.
How Transactions Are Started and Ended
SQL Server recognizes transaction boundaries through three core commands:
- BEGIN TRANSACTION: marks the start of a unit of work.
- COMMIT TRANSACTION: completes the unit of work and makes changes permanent.
- ROLLBACK TRANSACTION: cancels the unit of work and undoes changes.
The statements executed between BEGIN TRANSACTION and COMMIT/ROLLBACK are commonly described as being “wrapped” in a
transaction.
Transaction Modes in SQL Server
SQL Server supports multiple ways to manage transaction boundaries. In practice, the differences matter when you troubleshoot blocking, handle errors, or work with code that mixes application logic and database logic.
- Autocommit: the default behavior. Each individual statement is its own transaction. If it succeeds, it commits automatically; if it fails, it rolls back automatically.
-
Implicit transactions: SQL Server automatically starts a transaction when certain statements run, but you must explicitly end it with
COMMITorROLLBACK. This is less common in new application design but appears in some legacy codebases. -
Explicit transactions: you explicitly open a transaction with
BEGIN TRANSACTIONand explicitly close it withCOMMITorROLLBACK. This is the most common approach for multi-step units of work that must be all-or-nothing.
A Practical Example
The example below shows a transfer-like pattern. The statements must succeed together. If the second update fails, the first update must not remain applied.
BEGIN TRANSACTION;
UPDATE dbo.Account
SET Balance = Balance - 100
WHERE AccountId = 101;
UPDATE dbo.Account
SET Balance = Balance + 100
WHERE AccountId = 202;
COMMIT;
In later lessons, you will extend this pattern to include robust error handling. In production code, you typically combine explicit transactions with
TRY...CATCH and check the transaction state before committing.
ACID: The Standard Test for Transaction Reliability
A database transaction is expected to satisfy the ACID properties: Atomicity, Consistency, Isolation, and Durability. These properties explain why transactions are trusted as the correctness boundary for database work.
- Atomicity: the unit of work is all-or-nothing. If any statement fails, the transaction can be rolled back so none of its effects remain.
- Consistency: the database moves from one valid state to another. Constraints, referential integrity, and business rules must remain enforceable at the transaction boundary.
- Isolation: concurrent transactions do not interfere in ways that produce incorrect results. SQL Server controls isolation through isolation levels and (optionally) row versioning, which you will study later in this module.
- Durability: once committed, changes persist even if a failure occurs. SQL Server achieves durability through its transaction log and recovery mechanisms.
ACID is commonly used as a baseline when evaluating database engines and application architectures. In SQL Server, the transaction log is central to both durability and recovery, while locking and/or row versioning are central to isolation and concurrency.
Historical note: the acronym ACID was introduced in the early 1980s in foundational database research and is now a standard way to describe transactional reliability. Most mainstream relational database systems—including SQL Server—are designed around these properties by default.
How Transactions Relate to Locking
Transactions and locks are tightly connected. SQL Server uses locks (and, in some configurations, row versions) to isolate concurrent work. The longer a transaction stays open, the longer it may hold locks or retain row versions. This is why well-designed systems keep transactions as short as possible while still preserving correctness.
The next lessons build on this foundation by introducing isolation levels, blocking, and deadlocks—so you can design transaction scopes that remain correct and performant under real-world concurrency.