Transactions — Understand transactions conceptually (BEGIN/COMMIT/ROLLBACK) (intro) - SQL

SECTION 1

Historical Context & Motivation

Before relational databases matured, application programmers faced a harrowing challenge: what happens when a system crashes halfway through a series of related updates? Consider a banking transfer that debits one account but fails before crediting another—the money effectively vanishes. Early file-based data systems offered no built-in mechanism to prevent such partial-update anomalies, forcing developers to write fragile, ad-hoc recovery code. The concept of a database transaction arose precisely to solve this problem: bundling a group of operations into an indivisible unit that either succeeds entirely or fails entirely, leaving no trace of intermediate states.

1970 • Codd's Relational Model

Edgar F. Codd published his seminal paper on the relational model at IBM, establishing the theoretical groundwork for structured data management and implicitly raising questions about consistency guarantees during concurrent access.

1976 • Gray's Transaction Concept

Jim Gray formalized the notion of a transaction and introduced the concepts of locking and recovery that would later become the foundation of the ACID properties, earning him the Turing Award in 1998.

1983 • ACID Properties Named

Theo Härder and Andreas Reuter coined the acronym ACID (Atomicity, Consistency, Isolation, Durability) in their influential paper, giving the database community a precise vocabulary for transaction guarantees.

1992 • SQL-92 Standard

The SQL-92 (SQL2) standard formalized transaction control statements—BEGIN, COMMIT, and ROLLBACK—as part of the language specification, ensuring portable transaction semantics across compliant database systems.

The central question that transactions address is deceptively simple: how can a database system guarantee that a group of operations either all take effect or none of them do, even in the face of hardware failures, software crashes, or concurrent access by multiple users? This question sits at the intersection of correctness, reliability, and performance, and it remains one of the most important concepts in systems design today.

SECTION 2

Core Principles & Definitions

A transaction is a logical unit of work consisting of one or more SQL statements that the database engine treats as a single, indivisible operation. The fundamental guarantee is all-or-nothing execution: if any statement within the transaction fails, the entire batch of changes is undone, restoring the database to its prior consistent state. Three control statements govern this lifecycle—BEGIN opens the transaction boundary, COMMIT makes all changes permanent, and ROLLBACK discards all changes and reverts the database.

Atomicity

All operations within a transaction succeed together or fail together. There is no intermediate state visible to the outside world—hence the analogy to an indivisible atom.

Consistency

A transaction moves the database from one valid state to another. All integrity constraints, foreign keys, and check conditions are satisfied both before and after execution.

Isolation

Concurrent transactions execute as though they were run serially. Intermediate results of one transaction are invisible to others, preventing read anomalies and race conditions.

Durability

Once a transaction is committed, its effects survive subsequent system crashes. The database engine uses write-ahead logging (WAL) to ensure changes persist even if power is lost immediately after COMMIT.

✦

KEY TAKEAWAY

Think of a transaction like sending a certified letter through the post office. You fill out the form (BEGIN), the postal worker processes everything (the SQL statements execute), and at the end you have two choices: sign and send (COMMIT), or tear up the form and walk away (ROLLBACK). At no point does the letter exist in a half-sent state visible to anyone else—it either gets delivered in full or it was never sent.

SECTION 3

The Transaction Lifecycle — Visual Explanation

The upper flow shows the three control statements. BEGIN opens a transaction boundary, SQL statements execute within it, and either COMMIT (green) makes changes permanent or ROLLBACK (red) discards them. The lower timeline shows that intermediate states are invisible to other connections—the database moves directly from one consistent state to another.

The diagram above captures the essential contract of a transaction. Notice that the dashed purple box in both the flow and the timeline represents the in-progress phase—changes exist tentatively in memory and in the transaction log, but they are not yet visible to other database sessions. This isolation guarantee is what prevents phenomena like dirty reads, where one transaction observes uncommitted data from another. Only after the COMMIT command succeeds does the database engine make the changes durable and globally visible. Conversely, issuing ROLLBACK instructs the engine to undo every modification made since the matching BEGIN, restoring the database to its prior consistent state as if nothing happened.

SECTION 4

How Transactions Work Under the Hood

While the SQL surface area for transactions is just three keywords, the mechanisms that enforce ACID guarantees are surprisingly sophisticated. Two key subsystems collaborate inside the database engine: the write-ahead log (WAL) and the lock manager. Understanding these gives you the conceptual foundation for reasoning about transaction behavior in real systems.

Write-Ahead Logging (WAL)

Before any data page is modified on disk, the database engine first writes a log record describing the intended change to a sequential, append-only log file. This is the 'write-ahead' principle: the log entry is always flushed to stable storage before the corresponding data page. If the system crashes after the log is written but before the data page is updated, the recovery subsystem can replay (redo) the logged operations. Conversely, if a transaction is rolled back, the log entries provide the inverse operations needed to undo the changes. The WAL guarantees both atomicity (undo incomplete transactions on crash) and durability (redo committed transactions whose data pages were not yet flushed).

Lock-Based Concurrency Control

When transaction T₁ reads or writes a row, the lock manager acquires a shared lock (S) or exclusive lock (X) on that resource. Multiple transactions may hold shared locks simultaneously (allowing concurrent reads), but an exclusive lock blocks all other access. This protocol—known as two-phase locking (2PL)—ensures serializability: the growing phase acquires locks as needed, and the shrinking phase releases them only after COMMIT or ROLLBACK. The trade-off is that aggressive locking can lead to deadlocks, which the database resolves by aborting (rolling back) one of the conflicting transactions.

ℹ

Implicit vs. Explicit Transactions

Many database systems operate in autocommit mode by default, where every individual SQL statement is implicitly wrapped in its own transaction. When you explicitly write BEGIN, you are overriding autocommit to group multiple statements into a single transaction boundary. In PostgreSQL, BEGIN and START TRANSACTION are synonymous; MySQL uses START TRANSACTION as well (though BEGIN also works). SQL Server uses BEGIN TRANSACTION.

SECTION 5

Transaction Scenarios — Success, Failure, and Crash

To solidify the concept, let us trace through three canonical scenarios that every database professional encounters: a successful commit, an explicit rollback triggered by application logic, and a crash recovery scenario where the database engine must clean up autonomously.

Three side-by-side scenarios illustrate how the transaction boundary protects data integrity. Scenario 1 (green) shows a normal commit. Scenario 2 (red) shows an application-initiated rollback. Scenario 3 (amber) shows a crash mid-transaction, where the WAL enables automatic recovery on restart.

The third scenario is particularly important to internalize. Application developers do not write special crash-recovery code; the database engine handles it automatically. When the engine restarts after an unexpected shutdown, it scans the write-ahead log, identifies any transactions that were in progress but never committed, and rolls them back. Committed transactions whose data pages had not yet been flushed to disk are replayed (redone) from the log. This crash recovery protocol is what makes the durability and atomicity guarantees practical, not merely theoretical.

SECTION 6

Worked Example — Bank Transfer

The classic textbook example for transactions is a bank transfer: moving $500 from Alice's checking account to Bob's savings account. Without a transaction wrapper, a failure between the debit and the credit would leave the bank's books inconsistent. Let us walk through the SQL step by step.

Transferring $500 from Alice to Bob

1
Step 1 — Open the Transaction Boundary

We issue BEGIN; to tell the database engine that the following statements form a single atomic unit. From this point on, no changes will be visible to other sessions until we explicitly commit.

BEGIN; — Transaction opened, autocommit suspended.
2
Step 2 — Debit Alice's Account

We subtract $500 from Alice's balance. The engine acquires an exclusive lock on Alice's row, writes the old value to the WAL, and updates the in-memory page.

UPDATE accounts SET balance = balance - 500 WHERE owner = 'Alice' AND acct_type = 'checking'; — 1 row affected (change is tentative).
3
Step 3 — Credit Bob's Account

We add $500 to Bob's balance. A second exclusive lock is acquired on Bob's row. If either this statement or the previous one fails (e.g., insufficient funds enforced by a CHECK constraint), the application should issue ROLLBACK.

UPDATE accounts SET balance = balance + 500 WHERE owner = 'Bob' AND acct_type = 'savings'; — 1 row affected (change is tentative).
4
Step 4 — Verify and Commit

If the application confirms both updates succeeded, we issue COMMIT. The engine flushes the commit record to the WAL, releases all locks, and makes both changes visible to other sessions simultaneously. If anything went wrong, we would issue ROLLBACK instead, and neither account balance would change.

COMMIT; — Both changes are now permanent and globally visible. The total money across both accounts is unchanged—consistency preserved.

⚠

What If We Had Omitted the Transaction?

In autocommit mode, each UPDATE would be its own implicit transaction. If the system crashed after debiting Alice but before crediting Bob, Alice would lose $500 with no record of where it went. The explicit BEGIN … COMMIT wrapper prevents this: the crash would trigger a WAL-based rollback of the debit, leaving both accounts at their original balances.

SECTION 7

Strengths, Limitations, and Trade-offs

Aspect	Strength	Limitation / Trade-off
Atomicity	Guarantees all-or-nothing semantics, preventing partial updates even during crashes.	WAL overhead adds latency to every write; fsync on COMMIT can be expensive on high-throughput workloads.
Isolation	Prevents dirty reads and other concurrency anomalies, simplifying application logic.	Strict isolation (SERIALIZABLE) can reduce throughput dramatically; weaker levels trade correctness for speed.
Locking	Ensures serializability via two-phase locking; well-understood correctness proofs exist.	Deadlocks can occur when transactions acquire locks in different orders; long transactions hold locks longer, reducing concurrency.
Simplicity	Three keywords (BEGIN/COMMIT/ROLLBACK) give developers a clean, declarative API for correctness.	Developers must still reason about transaction scope—overly broad transactions degrade performance; too-narrow ones miss consistency needs.

Strengths and trade-offs of SQL transaction semantics

✦

KEY TAKEAWAY

Transactions are to databases what version control is to codebases. Just as Git lets you bundle related changes into a commit and revert with git reset if something goes wrong, SQL transactions let you bundle related writes and roll back on failure. The key engineering decision is choosing the right transaction granularity: too coarse and you block other users for too long; too fine and you lose the all-or-nothing guarantee across logically related operations.

SECTION 8

Connection to Advanced Transaction Theory

The basic BEGIN/COMMIT/ROLLBACK model is just the entry point into a rich area of database systems theory and practice. As you progress, you will encounter several extensions and refinements that build directly on the conceptual foundation established here.

Introductory Concept	Advanced Extension
ROLLBACK (all-or-nothing)	SAVEPOINTs — partial rollback to a named point within a transaction without abandoning the entire unit of work.
Isolation (binary: isolated or not)	Isolation Levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Each level permits progressively fewer anomalies at the cost of concurrency.
Lock-based concurrency control	MVCC (Multi-Version Concurrency Control) — readers never block writers and vice-versa; used by PostgreSQL, Oracle, and MySQL/InnoDB.
Single-database transactions	Distributed Transactions & 2PC — coordinating commits across multiple databases or microservices using the two-phase commit protocol.

From introductory to advanced transaction concepts

Modern distributed systems—especially those following microservice architectures—often replace traditional ACID transactions with patterns like the saga pattern, which orchestrates a sequence of local transactions with compensating actions instead of global rollback. Understanding the classical transaction model is essential before tackling these alternatives, because sagas are fundamentally a relaxation of ACID guarantees designed to improve availability and scalability at the cost of stronger consistency. The CAP theorem and the BASE model provide the theoretical framework for reasoning about these trade-offs.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

A developer writes three INSERT statements in sequence without wrapping them in BEGIN/COMMIT. In autocommit mode, the first two INSERTs succeed, but the third fails due to a constraint violation. What is the state of the database? Why does this outcome differ from what would happen if all three statements were inside a single explicit transaction?

Show answer

In autocommit mode, each statement is its own implicit transaction. The first two INSERTs are individually committed and persist in the database; only the third INSERT is rolled back. The database now contains two of the three intended rows—a partial result. Had the developer wrapped all three in BEGIN … COMMIT, the constraint violation on the third INSERT would cause a ROLLBACK of the entire transaction, removing all three inserts and leaving the database in its original state. This illustrates atomicity: the transaction either succeeds completely or has no effect at all.

Question 2: BASIC

Write the SQL for a transaction that inserts a new customer record into a customers table and then inserts an initial order into an orders table referencing that customer. Include the BEGIN and COMMIT statements.

Show answer

The correct SQL would be: BEGIN; followed by INSERT INTO customers (customer_id, name, email) VALUES (101, 'Jane Doe', 'jane@example.com'); then INSERT INTO orders (order_id, customer_id, amount) VALUES (5001, 101, 79.99); and finally COMMIT;. Wrapping both statements in a transaction ensures that if the orders INSERT fails (for example, due to a foreign key violation because the customer INSERT also failed), neither record persists. This maintains referential integrity.

Question 3: INTERMEDIATE

An e-commerce application processes an order in four steps: (1) reduce inventory count, (2) charge the customer's payment method, (3) insert an order record, (4) insert shipment record. The payment gateway returns an error on step 2. Explain what the application should do and what happens to the inventory change from step 1.

Show answer

The application should detect the payment error and immediately issue ROLLBACK; since all four steps are enclosed within a single transaction. The ROLLBACK reverts the inventory reduction from step 1, restoring the stock count to its original value. No order or shipment record exists. This is the atomicity guarantee in action: even though step 1 modified data in the database, that change was tentative and invisible to other sessions (isolation). The ROLLBACK uses the undo information stored in the write-ahead log to reverse the inventory UPDATE. The application can then return an appropriate error message to the user without worrying about data inconsistency.

Question 4: APPLIED

A ride-sharing application must simultaneously update a driver's status to 'on_trip', create a trip record, and update the rider's status to 'in_ride'. During peak hours, thousands of such transactions execute concurrently. Describe how wrapping these operations in a transaction prevents data anomalies, and identify one potential performance concern that could arise from aggressive transaction usage.

Show answer

Without a transaction, a crash after updating the driver's status but before creating the trip record would leave the driver permanently marked as 'on_trip' with no corresponding trip—a ghost assignment. The transaction ensures that all three updates are committed atomically or none are. Isolation prevents another session from seeing a driver marked 'on_trip' without a trip record, which could cause the dispatcher algorithm to skip that driver incorrectly. The performance concern is lock contention: if the driver row and rider row are both exclusively locked for the duration of the transaction, and the trip INSERT requires index lock acquisition, other concurrent transactions targeting the same driver or rider must wait. During peak hours, this can create a bottleneck. One mitigation is to keep the transaction scope as narrow as possible—perform all validation and computation outside the BEGIN/COMMIT block and only include the actual writes.

Question 5: CRITICAL THINKING

A colleague argues that since modern databases have autocommit, explicit transactions are unnecessary. Construct a counter-argument by identifying at least two categories of operations where autocommit is insufficient, and explain the conceptual relationship between transaction scope, consistency requirements, and system performance.

Show answer

Autocommit is insufficient in at least two categories. First, multi-table integrity operations such as a bank transfer require two or more updates to be atomic; autocommit would allow partial completion. Second, read-then-write patterns (e.g., check inventory, then decrement) are vulnerable to race conditions under autocommit because another session can modify the data between the read and the write—the transaction's isolation guarantee prevents this. Conceptually, transaction scope defines the consistency boundary: a wider scope captures more related operations under ACID protection but holds locks longer, reducing throughput. A narrower scope improves concurrency but may miss cross-statement consistency requirements. The engineering art is choosing a scope that precisely matches the logical unit of work—neither broader (wasting concurrency) nor narrower (risking inconsistency). This tension between consistency and performance is formalized in isolation levels and is a central theme in advanced database and distributed systems theory.

SUMMARY

Summary — Transactions at a Glance

Summary

A transaction is a logical unit of work that groups one or more SQL statements under ACID guarantees: Atomicity (all-or-nothing execution), Consistency (valid state to valid state), Isolation (concurrent transactions cannot see each other's intermediate work), and Durability (committed changes survive crashes). The three control statements— BEGIN, COMMIT, and ROLLBACK—give developers a simple, declarative interface to these powerful guarantees.

Under the hood, the write-ahead log (WAL) ensures atomicity and durability by recording every change before applying it, while the lock manager enforces isolation through shared and exclusive locks governed by two-phase locking. Choosing the right transaction scope is a key design decision: it must be broad enough to capture all logically related operations but narrow enough to avoid holding locks unnecessarily. From SAVEPOINTs to isolation levels to distributed two-phase commit, every advanced topic in this domain extends the foundational concepts covered here.