Transactions, ACID & Atomic Operations

A transaction is a failure and concurrency boundary. Inside it, PostgreSQL can make several database changes become visible together or make none of them visible. That guarantee is powerful but deliberately narrow: it does not make an HTTP request, a published message, an email, or a second independent database part of the same atomic event.

The senior-level skill is therefore not reciting “ACID.” It is placing the transaction boundary around one business invariant, choosing the right atomic database primitive, understanding the selected isolation level, and designing correct recovery for failures whose outcome is uncertain.

The transaction boundary #

PostgreSQL starts an implicit transaction around every standalone statement. An explicit block groups multiple statements:

BEGIN;

UPDATE accounts
SET balance_cents = balance_cents - 5_000
WHERE account_id = 17;

UPDATE accounts
SET balance_cents = balance_cents + 5_000
WHERE account_id = 42;

COMMIT;

Before COMMIT, other transactions do not see these changes. ROLLBACK discards them. A database error inside an explicit PostgreSQL transaction marks the transaction failed; subsequent ordinary commands are rejected until the client rolls back the transaction or rolls back to a savepoint.

BEGIN;
SAVEPOINT before_optional_item;

-- If this statement fails:
INSERT INTO order_items(order_id, sku, quantity)
VALUES (9001, 'optional-addon', 1);

ROLLBACK TO SAVEPOINT before_optional_item;
COMMIT;

A savepoint provides partial rollback inside one transaction. It does not commit the earlier work, shorten its snapshot lifetime, or release every lock: locks acquired after the savepoint are released when rolling back to it, while earlier locks remain until the outer transaction ends.

ACID, precisely #

Atomicity: all database effects or none #

Atomicity means a transaction does not expose a committed partial state. If the second account update fails, the first update can be rolled back with it. It does not mean:

• multiple client statements outside one transaction act as one unit;
• external side effects roll back with the database;
• every PostgreSQL object is transactional—sequence increments are visible immediately and are not reclaimed by rollback;
• the client always knows whether COMMIT took effect after a connection loss.

Atomicity exists at several different scales:

Consistency: declared invariants survive #

Consistency is not a magic property that makes data “correct.” It means every committed database state satisfies the invariants actually enforced by schema constraints and correct transaction logic.

Put invariants as close to the data as their scope permits:

CREATE TABLE accounts (
    account_id bigint PRIMARY KEY,
    balance_cents bigint NOT NULL CHECK (balance_cents >= 0)
);

CREATE TABLE transfers (
    transfer_id uuid PRIMARY KEY,
    from_account_id bigint NOT NULL REFERENCES accounts(account_id),
    to_account_id bigint NOT NULL REFERENCES accounts(account_id),
    amount_cents bigint NOT NULL CHECK (amount_cents > 0)
);

NOT NULL, CHECK, UNIQUE, exclusion, and foreign-key constraints are checked by the database under concurrency. An application-side “check-then-write” is only an observation and can race unless the final write is protected by a constraint, lock, or serializable execution.

Some invariants span multiple rows and cannot be expressed as a simple CHECK. They require an atomic statement, locked rows, a carefully designed trigger, or Serializable isolation. PostgreSQL does not infer the business rule.

Isolation: which concurrent histories are allowed #

Isolation controls what interleavings can be observed, not whether the transaction syntax is valid. PostgreSQL's default Read Committed level allows some results that would be impossible under serial execution. Repeatable Read provides one stable snapshot but can still admit serialization anomalies. Serializable rejects a transaction when it cannot preserve an equivalent serial ordering.

Isolation is therefore a contract between database and application:

1. the database detects or prevents the anomalies promised by the level;
2. the application uses atomic statements or locks where weaker levels permit races;
3. the application retries the whole transaction when PostgreSQL aborts it with a retryable concurrency error.

The mechanics and anomaly matrix are developed in MVCC & Isolation Levels.

Durability: acknowledged commit survives the promised failures #

PostgreSQL implements crash durability with write-ahead logging (WAL): the WAL records needed to redo a change reach durable storage before the corresponding data page may be written. At commit, PostgreSQL normally waits until the transaction's WAL is flushed, and recovery replays it after a crash.

The guarantee depends on configuration and hardware:

• fsync = on is the normal crash-safe setting; disabling it can corrupt the cluster after an operating-system or hardware failure;
• synchronous_commit = off can acknowledge a transaction before its WAL is durably flushed, so a crash may lose recently acknowledged transactions, although the cluster remains internally consistent;
• a local durable commit is not the same as a commit replicated to another failure domain; synchronous replication policy determines that boundary.

Durability is a stated failure model, not a synonym for “data can never be lost.” Backups, replication, storage integrity, and disaster recovery address different failures.

Prefer one atomic statement over read–modify–write #

The dangerous shape is:

read current value
decide in application code
write a replacement value

Two clients can read the same old value and overwrite each other's decisions. When possible, move the condition and mutation into one statement.

Conditional update #

UPDATE accounts
SET balance_cents = balance_cents - 5_000
WHERE account_id = 17
  AND balance_cents >= 5_000
RETURNING balance_cents;

PostgreSQL acquires the needed row lock, rechecks the predicate against the current row version after a concurrent updater finishes, and returns either one row or none. The affected-row count is part of the result: zero rows means the account was absent or the balance condition failed. This avoids a separate pre-check race.

Atomic increment #

UPDATE counters
SET value = value + 1
WHERE counter_id = 'invoice';

The right-hand side is evaluated against the row version PostgreSQL is updating. By contrast, sending a stale application value such as SET value = 1042 can overwrite a concurrent increment.

Insert-or-update under a unique constraint #

INSERT INTO inventory(sku, available)
VALUES ($sku, $received_quantity)
ON CONFLICT (sku) DO UPDATE
SET available = inventory.available + EXCLUDED.available
RETURNING sku, available;

INSERT ... ON CONFLICT DO UPDATE provides an atomic insert-or-update outcome, even under high concurrency. The unique index is not merely a performance feature: its enforcement is integrated with insertion so competing inserts cannot both pass a racy pre-check. MERGE has different concurrency semantics and does not promise that one of insert or update must occur.

Compare-and-swap for optimistic concurrency #

UPDATE documents
SET body = $new_body,
    version = version + 1
WHERE document_id = $id
  AND version = $expected_version
RETURNING version;

If no row is returned, another writer changed the document. The application must reload and consciously merge, reject, or retry. This pattern detects lost updates without holding a row lock while a user thinks.

Multi-row invariants need a deliberate protocol #

Suppose a transfer must never overdraw an account and must create one immutable transfer record. A robust transaction uses a deterministic lock order and a conditional debit:

BEGIN;

SELECT account_id
FROM accounts
WHERE account_id IN ($from_id, $to_id)
ORDER BY account_id
FOR UPDATE;

UPDATE accounts
SET balance_cents = balance_cents - $amount
WHERE account_id = $from_id
  AND balance_cents >= $amount
RETURNING balance_cents;

-- Continue only if the debit returned one row.
UPDATE accounts
SET balance_cents = balance_cents + $amount
WHERE account_id = $to_id;

INSERT INTO transfers(
    transfer_id, from_account_id, to_account_id, amount_cents
) VALUES ($transfer_id, $from_id, $to_id, $amount);

COMMIT;

The unique transfer_id supplies idempotency, the conditional debit enforces the balance rule, and sorted acquisition reduces deadlocks. Missing accounts or a zero-row debit must cause rollback. Application code must check each expected row count; SQL success with zero affected rows may be a business failure.

A transaction cannot atomically commit an HTTP call #

This sequence is unsafe:

1. commit order in PostgreSQL
2. publish OrderCreated to a broker

A crash between the steps loses the event. Reversing the order can publish an event for a transaction that later rolls back. Holding the database transaction open across the network call adds lock time but still does not make the two systems atomic.

The transactional outbox writes both domain state and an outbox record in one PostgreSQL transaction:

BEGIN;

WITH paid_order AS (
    UPDATE orders
    SET status = 'paid'
    WHERE order_id = $1 AND status = 'pending'
    RETURNING order_id
)
INSERT INTO outbox(event_id, topic, aggregate_id, payload)
SELECT $event_id, 'order-paid', order_id, $payload
FROM paid_order;

COMMIT;

The CTE emits no event when the guarded state transition updates no row; the application must treat that zero-row outcome explicitly. A relay later publishes committed outbox rows. A crash can cause duplicate publication, so consumers use event_id or another idempotency key to make redelivery harmless. The resulting contract is usually at-least-once delivery plus idempotent effects, not exactly-once execution of arbitrary external side effects.

Retries are part of the transaction contract #

PostgreSQL uses stable five-character SQLSTATE codes. Code should inspect the code, not localized message text.

A transaction retry must rerun all decisions and reads because the original snapshot and its conclusions are obsolete. Retrying only the failed final statement can preserve a decision based on stale data.

repeat with bounded attempts and jitter:
  begin
  read and decide again
  perform all writes
  commit
  on 40001 or 40P01:
    rollback if the connection is usable
    retry the complete function

Bound attempts, add jitter under contention, emit metrics, and ensure anything outside PostgreSQL is idempotent. A generic retry loop around every database error can repeat invalid input, overload the database, or duplicate effects.

The ambiguous-commit problem #

If the server commits and the connection dies before the acknowledgement reaches the client, the client cannot infer the outcome from the broken connection. Blindly repeating a payment or transfer may duplicate it.

Give the business operation a stable identifier supplied by the caller:

INSERT INTO transfers(transfer_id, from_account_id, to_account_id, amount_cents)
VALUES ($stable_operation_id, $from, $to, $amount)
ON CONFLICT (transfer_id) DO NOTHING
RETURNING transfer_id;

After reconnecting, query by that identifier. The operation either already exists with the expected parameters or can be attempted safely. A generated sequence number is not a substitute when the client needs to recognize its same logical request across retries.

Two-phase commit is a specialized coordination mechanism #

PostgreSQL supports PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED for an external transaction manager coordinating multiple transactional resources. After prepare, the transaction is detached from the session and remains recoverable for the later decision.

That is not a general application transaction pattern:

• prepared transactions continue holding locks;
• they prevent VACUUM from reclaiming some old tuple versions;
• an abandoned prepared transaction can survive sessions and restarts;
• PostgreSQL recommends leaving max_prepared_transactions = 0 unless an external transaction manager reliably resolves every prepared transaction.

Two-phase commit trades availability and operational simplicity for an atomic global decision among participating transactional systems. It still cannot make an arbitrary non-transactional side effect roll back.

Transaction design review #

For each business operation, an architect should be able to answer:

1. Invariant: what must never be false after commit?
2. Owner: which constraint, atomic statement, lock, or isolation level enforces it?
3. Boundary: which database changes are in the transaction, and which external effects are outside it?
4. Contention: which rows and indexes can concurrent operations touch, and in what order?
5. Outcome: which row counts and returned values distinguish success from a no-op or business rejection?
6. Retry: which SQLSTATE codes are transient, and can the whole operation be replayed safely?
7. Ambiguity: how is success discovered after connection loss during commit?
8. Duration: can any network call, user interaction, or large computation keep the transaction and its locks open?
9. Durability: what failure has the commit acknowledgement promised to survive?
10. Operations: which timeouts, logs, and metrics reveal stuck or aborted transactions?

Primary PostgreSQL references #

• Transactions tutorial
• Transaction isolation
• Write-ahead logging
• INSERT and ON CONFLICT
• Constraint definitions
• Two-phase transactions
• PostgreSQL error codes
• Transactional outbox pattern