Transactions

Overview

A transaction treats related data operations as a unit of work. In a bank transfer, committing the debit and credit separately creates a failure window in which money can disappear or be added twice. Grouping the changes establishes a basis for committing both or cancelling both.

A database transaction may contain several statements. Commit confirms the work; rollback cancels changes still in progress. Whether a user-visible order corresponds to exactly one database transaction depends on where the application places its boundaries.

Committing changes together leaves the question of concurrent execution. Two people can reserve the same seat, or transactions updating different rows can jointly violate a rule. Transactions address both the unit of recovery and the permitted interactions between overlapping work. The first question concerns atomicity; the second concerns isolation.

This article approaches transactions through the problem of defining a recoverable unit of data processing, rather than through one database's BEGIN syntax. SQL isolation levels and MVCC are concepts encountered when implementing and refining that unit.

Timeline

• 1976: K. P. Eswaran, J. N. Gray, R. A. Lorie, and I. L. Traiger formalized the relationship among transactions, consistency, schedules, and locks in The Notions of Consistency and Predicate Locks in a Database System.¹

• 1981: Jim Gray's The Transaction Concept: Virtues and Limitations described transactions as state transformations with atomicity, consistency, and durability. It organized existing concepts and implementations while discussing unresolved problems such as subtransactions and long-lived work.²

• 1983: Theo Härder and Andreas Reuter's Principles of Transaction-Oriented Database Recovery provided a common framework for comparing recovery techniques and grouped atomicity, consistency, isolation, and durability under ACID.³

• 1995: Hal Berenson and colleagues analyzed ambiguities and omitted cases in SQL isolation definitions, including snapshot isolation, in A Critique of ANSI SQL Isolation Levels.⁴

• 2012: Dan R. K. Ports and Kevin Grittner published their experience implementing Serializable Snapshot Isolation in PostgreSQL 9.1. This is the paper's publication year.⁵

Shared data and serializability (1976)

When users share data, the correctness of each operation and the correctness of their combined execution can differ. Reading and reducing stock can look sound on its own, yet another transaction interleaving its reads and writes changes the outcome.

Eswaran, Gray, Lorie, and Traiger's 1976 paper connects shared-data consistency with execution schedules. It analyzes the locking rules under which interleaved work has the same result as serial execution, and examines protection of predicate-defined sets as well as physical records.¹ In this account, a transaction is broader than a single SQL command. Later recovery and isolation work continues the questions of what constitutes one operation and which executions are acceptable.

Serializability requires successful transactions' effects to match some serial order. Implementations can overlap work while meeting that requirement by waiting for or aborting conflicting transactions.

State transformations and recovery boundaries (1981)

Jim Gray's 1981 paper distinguishes three categories of actions to explain recovery scope. Unprotected actions need not be undone or redone after an abort; protected actions, such as database changes, must support undo or redo. Real actions, such as dispensing cash, cannot be undone and are therefore deferred until commit.² This distinction also helps explain later questions about connecting remote service calls and message delivery to local rollback.

Recovery needs to know which changes belong together and which work has committed. Transaction boundaries and commit records therefore provide evidence for recovery as well as controlling execution.

ACID and a common language for recovery (1983)

Härder and Reuter's 1983 paper compares logs, checkpoints, and strategies for propagating changes to storage within a common conceptual framework. WAL requires the relevant log records to reach durable storage before the changed data pages are written to storage.³⁶ Schemas and applications must express business constraints. A database cannot infer the intended result of incorrect arithmetic.

The 1983 account shows a common vocabulary taking shape for comparing different recovery implementations.

SQL isolation levels and write skew (1995)

SQL isolation levels have been described through anomalies such as dirty reads, nonrepeatable reads, and phantoms. Hal Berenson and colleagues' 1995 analysis makes the distinction between a stable snapshot and serializability explicit.⁴ Transactions can read the same snapshot, update different rows, and jointly violate a constraint. Write skew shows why avoiding writes to the same row is insufficient to establish correctness.

Implementing serializability over snapshots (2012)

Dan R. K. Ports and Kevin Grittner's 2012 paper describes the isolation guarantee, conflict tracking, and costs of PostgreSQL 9.1's SSI implementation. In its on-call example, two transactions each read that two doctors are on duty, then update only their own doctor's row to leave. Under snapshot isolation, these disjoint writes can leave nobody on call. SSI tracks read/write dependencies and aborts potentially nonserializable executions; false positives can also cause a serializable execution to be aborted.⁵

MVCC manages versions; isolation rules determine which versions are visible and which conflicts are rejected. PostgreSQL 18 gives Read Committed, Repeatable Read, and Serializable different observable behavior. A serialization failure requires the caller to reconsider the entire failed transaction.⁷

Business operations across services

When work spans services, the database transaction and the user-visible operation can have different lifetimes. Orders and stock in one database can share a local transaction; an operation that also sends email or makes a payment through another service needs an additional contract.

Distributed transactions coordinate commitment among participants. A saga organizes multiple steps and compensating actions. An outbox records a business change and its pending event in the same local database transaction. Gray's recovery boundary helps explain these service-architecture choices: comparison begins with the scope for which one database's rollback is responsible.

A small rollback example

In this PostgreSQL example, the stock change and order record share one transaction boundary. It checks cancellation of both writes; a concurrent order API would require further implementation.

CREATE TEMP TABLE stock (id integer PRIMARY KEY, quantity integer NOT NULL);
CREATE TEMP TABLE orders (id integer PRIMARY KEY, item_id integer NOT NULL);
INSERT INTO stock VALUES (1, 1);

BEGIN;
UPDATE stock SET quantity = quantity - 1 WHERE id = 1;
INSERT INTO orders VALUES (100, 1);
ROLLBACK;

SELECT quantity FROM stock WHERE id = 1;
SELECT count(*) FROM orders;

When psql executes the file statement by statement, the final queries return stock 1 and an order count of 0 because both changes were rolled back. Using COMMIT at the same point would commit both changes.