How PostgreSQL Implements ACID Transactions

Transaction state, MVCC, and atomicity

In PostgreSQL, BEGIN starts a transaction that groups several statements so they succeed or fail together. COMMIT makes the changes visible to other transactions; ROLLBACK aborts them. For a transfer, subtract money from one account and add it to the other in the same transaction. Commit only after both updates affect the expected rows.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1 AND balance >= 100;

-- Check that exactly one row changed before continuing.

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If a check fails, issue ROLLBACK instead of COMMIT. PostgreSQL also runs statements outside an explicit transaction as individual transactions. PostgreSQL does not restore sequence values, including those generated by serial, after an abort, so gaps in sequence numbers do not show that other changes were partly committed. See PostgreSQL’s transaction identifier documentation.

PostgreSQL uses multiversion concurrency control (MVCC), which keeps row versions so transactions can read data from an appropriate point in time. Each stored row version, called a tuple, records the transaction ID that created it and, when it is updated or deleted, the transaction ID that replaced or removed it. PostgreSQL checks those IDs against each transaction’s commit status and the reader’s snapshot, which records the transactions that were in progress when PostgreSQL took it. These checks determine which row versions the reader can see. A transaction sees its own changes, while other transactions see them only after commit. The MVCC overview describes how this version-based visibility works.

When a transaction aborts, PostgreSQL marks its changes as aborted. Readers treat those versions as invisible. PostgreSQL does not immediately erase them or restore the old row data in place. For an aborted update, the prior version remains available and the new version stays invisible. Later, VACUUM removes row versions that no active transaction needs and makes their space available for reuse. The delay lets concurrent readers continue using older versions safely; VACUUM handles their cleanup.

A savepoint lets a transaction undo work done after that point while keeping its earlier changes. For example, after SAVEPOINT before_optional_change, a client can issue ROLLBACK TO SAVEPOINT before_optional_change if a later optional update fails, then continue and eventually commit the parent transaction. PostgreSQL tracks this nested work as a subtransaction, a smaller transaction within the parent. The parent’s final commit or abort determines whether the subtransaction’s changes persist. See the documentation on subtransactions and savepoints.

BEGIN starts an explicit transaction, COMMIT makes its changes visible to other transactions, and ROLLBACK aborts all changes made since BEGIN. PostgreSQL treats the statements inside a transaction as one unit: either the transaction commits its changes together, or its changes remain invisible. The transaction control documentation describes these commands.

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

If either update fails, issue ROLLBACK to discard the transaction’s changes. After many errors, PostgreSQL marks the transaction as failed; further statements receive an error until the client rolls back the transaction or rolls back to a savepoint. In autocommit mode, which is the default for many clients, each statement runs in its own transaction unless the client explicitly starts one.

Consistency through constraints and application rules

PostgreSQL keeps declared database rules true when a transaction commits, but it cannot infer every rule that makes data valid for a particular application. A constraint is a rule PostgreSQL checks against data changes. For example, NOT NULL requires a value, CHECK tests a condition on a row, UNIQUE prevents duplicate values, a primary key uniquely identifies each row, and a foreign key requires a matching row in another table. These rules protect the database even when changes come from different services or direct SQL.

For example, a table can require a positive account balance with a CHECK constraint. Because a CHECK condition passes when its result is unknown due to a NULL, pair it with NOT NULL if missing balances are invalid. Use a foreign key to ensure an order references an existing customer. The constraint documentation specifies that CHECK constraints are designed for conditions on the row being checked; use constraints such as UNIQUE or FOREIGN KEY for supported cross-row or cross-table rules.

Some business rules need custom database logic. A trigger is a function PostgreSQL runs automatically in response to an event such as an insert or update. Constraint triggers can also defer their checks until later in the transaction. A deferred constraint is checked at the end of the transaction rather than immediately after each statement, allowing related changes to temporarily violate the rule while the transaction is still in progress. PostgreSQL allows deferral for foreign-key constraints. PostgreSQL also allows it for unique, primary-key, and exclusion constraints. CHECK and NOT NULL checks are immediate.

Database constraints work best for invariants that must hold for every stored row, such as “each order has a customer” or “each product has a unique identifier.” Application code still needs to enforce rules that depend on the current user, external services, or changing business policy. For instance, a database can verify that an order references a customer, while the service decides whether that customer is permitted to place the order. A successful transaction preserves the rules PostgreSQL has been given; it does not certify that the application’s full business decision was correct.

Isolation levels, snapshots, and concurrent transactions

When PostgreSQL transactions read and change the same data, the isolation level determines which committed changes each transaction sees and whether PostgreSQL allows potentially conflicting results to commit. PostgreSQL uses multiversion concurrency control (MVCC): an update creates a new row version, and each query reads versions visible in its snapshot, a view of which transactions had committed at a particular point. This lets ordinary reads proceed without waiting for many writes, while conflicting writes can still wait for locks. (MVCC in PostgreSQL)

Read Committed uses a new snapshot for each statement

Read Committed, PostgreSQL’s default, gives each statement its own snapshot. If one transaction runs two SELECT statements, the second can see changes that another transaction committed between those statements. This avoids reading uncommitted data, but it allows nonrepeatable reads, where the same query returns different values, and phantom rows, where a repeated query finds new matching rows.

Repeatable Read keeps one snapshot for the transaction

Repeatable Read uses a stable snapshot for the transaction, so later queries continue to see the same committed data they saw when the snapshot was established. PostgreSQL also prevents phantom reads at this level. However, stable reads alone do not prevent every anomaly.

For example, suppose two doctors are on call and a rule requires at least one to stay on call. Each of two transactions reads that both doctors are on call, then turns a different doctor off call. Because they update separate rows, both can commit under Repeatable Read, leaving no doctor on call. Together, the transactions create a serialization anomaly because no one-at-a-time execution could produce the combined result.

Serializable detects dangerous combinations

Serializable checks whether transactions depend on one another in a way that could produce a result impossible if PostgreSQL ran them one at a time. PostgreSQL uses predicate locks to track relevant reads for this purpose; those locks detect conflicts rather than blocking the transactions. If PostgreSQL detects a dangerous combination, it aborts a transaction with a serialization failure. The client must retry the entire transaction, including its reads and decision-making, because repeating only the failed statement would reuse decisions based on the old snapshot. (PostgreSQL isolation levels)

Locks handle conflicting writes

MVCC reduces read/write blocking, but it does not remove locks. When transactions update or delete the same row, one can wait for the other to finish. To coordinate a read followed by a change to a particular existing row, a transaction can use SELECT ... FOR UPDATE; this locks the selected row against conflicting updates or deletes until the transaction ends. Explicit row locks do not protect a condition involving rows that do not yet exist. For rules spanning multiple rows, use Serializable isolation or an appropriate database constraint rather than assuming a row lock covers the whole condition.

WAL, commit, and crash durability

When a PostgreSQL transaction commits, write-ahead logging (WAL) records its changes in a sequential log so the database can recover them after a crash. PostgreSQL writes the relevant WAL records, including a commit record, to durable storage before it acknowledges a durable commit. This ordering lets PostgreSQL confirm the transaction without first writing every changed table and index page to disk. The WAL overview describes this write-ahead rule.

A checkpoint writes modified data pages to disk and marks a recovery position in the WAL. It limits how far back crash recovery needs to scan; it does not replace the WAL flush required for a durable commit. After a crash, PostgreSQL starts from the latest checkpoint and replays later WAL records, redoing changes that had not yet reached the data files. See the checkpoint documentation for how checkpoints shape recovery.

These guarantees depend on settings and storage behavior. With synchronous_commit enabled, PostgreSQL waits for the transaction’s WAL to be flushed before returning success. Setting synchronous_commit = off lets PostgreSQL acknowledge a commit before that flush completes. A crash can then lose recently acknowledged transactions, although this setting does not carry the same corruption risk as disabling fsync. Setting fsync = off removes protections PostgreSQL uses to ensure data reaches durable storage; an operating-system crash or power failure can then cause database corruption. The WAL settings documentation describes these trade-offs. The storage hardware and operating system must also honor flush requests for PostgreSQL’s durability guarantees to hold.

A locally durable commit does not guarantee that a standby server has received the transaction. To wait for a synchronous standby, configure synchronous replication and choose an appropriate synchronous_commit mode. With on, PostgreSQL waits for the standby to flush the commit record; with remote_apply, it also waits for the standby to replay the change so queries there can see it. The asynchronous commit documentation explains why acknowledging work before a WAL flush changes crash-loss exposure.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com