Atomicity in DB Systems (Postgres specific)

September 8, 2026

On this page

"Atom" comes from the Greek atomos, meaning indivisible. In a database, atomicity is the promise that a transaction is exactly that: a group of operations that either all happen or none of them do. There is no valid state in which only half of a transaction has been applied.

That sounds like a small promise. It isn't. It's the reason you can write five statements across four tables and treat them as one thing, and it's the reason a power cut in the middle of a write doesn't leave you hand-repairing rows at 3 a.m.

This post covers atomicity from the outside in: what it guarantees, how you use it in PostgreSQL, where it quietly doesn't protect you, and then how Postgres actually implements it under the hood with tuple versions, the commit log and the write-ahead log.

ACID in one breath

Atomicity is one of four properties a transactional database guarantees:

  • Atomicity: all or nothing.
  • Consistency: a transaction moves the database from one valid state to another, respecting constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE and CHECK.
  • Isolation: concurrent transactions don't see each other's half-finished work.
  • Durability: once a commit is acknowledged, it survives a crash.

Many NoSQL systems relax these in favour of the BASE model (Basically Available, Soft state, Eventually consistent), trading strict guarantees for availability and scale. Postgres sits firmly on the ACID side, and atomicity is the property everything else leans on.

The problem: the half-finished transfer

The classic example, and the one every article on this topic reaches for, is a bank transfer. Moving 100 from account A to account B is two writes:

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

Run as two independent statements, plenty can go wrong between them:

  • the server crashes or loses power,
  • the client's connection drops,
  • the second statement hits a constraint violation,
  • the application throws an exception before sending it.

In each case, A has been debited and B has not been credited. The 100 has vanished into thin air.

Real business workflows are worse, because they span more steps. Placing an order might mean inserting the order, decrementing stock, recording a payment, updating the customer's balance and creating a shipment. Fail at step three and you've sold an item you never charged for. Atomicity turns that whole chain into a single unit.

Transactions in PostgreSQL

Wrap the statements in a transaction block:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

Until COMMIT succeeds, none of the changes are visible to anyone else, and if anything fails along the way, you (or Postgres) issue ROLLBACK and it's as if none of it happened:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- something goes wrong, or we change our mind
ROLLBACK;
-- account A is untouched

The same applies to a multi-table workflow:

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (10, 2500);
UPDATE products SET stock = stock - 1 WHERE id = 55;
INSERT INTO payments (order_id, amount) VALUES (currval('orders_id_seq'), 2500);
COMMIT;

Every statement is already a transaction

If you don't write BEGIN, Postgres runs in autocommit mode: each statement is wrapped in its own implicit transaction. So a single statement is always atomic on its own. An UPDATE that touches a million rows and fails on row 999,999 changes nothing. What autocommit can't do is make two statements atomic together. That's what BEGIN ... COMMIT is for.

DDL is transactional too

Unlike several other databases, Postgres lets you put most schema changes inside a transaction:

BEGIN;
ALTER TABLE accounts ADD COLUMN currency text NOT NULL DEFAULT 'NPR';
UPDATE accounts SET currency = 'USD' WHERE region = 'US';
COMMIT;

If the UPDATE fails, the new column disappears along with it. That makes migrations far safer. A few commands can't run inside a transaction block at all, such as CREATE INDEX CONCURRENTLY, VACUUM and CREATE DATABASE, and Postgres will refuse them if you try.

What happens when a statement fails mid-transaction

This is where Postgres differs from what many people expect, and it's worth seeing for yourself:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
INSERT INTO accounts (account_id, balance) VALUES ('A', 0);
-- ERROR:  duplicate key value violates unique constraint "accounts_pkey"

UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block

COMMIT;
-- ROLLBACK

Three things to notice:

  1. After the first error, the transaction is aborted. Postgres doesn't silently skip the bad statement and carry on. Every following statement is rejected.
  2. The only way out is to end the block. ROLLBACK is the honest way to do it.
  3. If you send COMMIT anyway, Postgres doesn't commit. The command tag that comes back is ROLLBACK, and the first UPDATE is discarded.

So the scenario "what if the second UPDATE fails and we commit anyway?" can't leave you with half a transfer in Postgres. The real danger is in application code that ignores the error, or doesn't check whether COMMIT actually committed, and then tells the user the transfer went through.

Savepoints: partial rollback

Sometimes you want to undo part of a transaction and keep going. That's what savepoints are for:

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (10, 2500);
SAVEPOINT order_created;

INSERT INTO payments (order_id, amount) VALUES (currval('orders_id_seq'), 2500);
-- payment provider rejected the card

ROLLBACK TO SAVEPOINT order_created;
UPDATE orders SET status = 'payment_failed' WHERE id = currval('orders_id_seq');
COMMIT;

ROLLBACK TO SAVEPOINT throws away everything after the savepoint, including an aborted state caused by an error, and the transaction continues from there. The order is kept and marked as failed; the payment row never existed. RELEASE SAVEPOINT discards the marker without rolling anything back.

Under the hood each savepoint is a subtransaction with its own transaction ID. They're cheap but not free: each backend caches only a small number of subtransaction IDs in shared memory, and workloads that open lots of them in one transaction (some ORMs and PL/pgSQL EXCEPTION blocks do this implicitly) can hit noticeable slowdowns once that cache overflows. Use them where you need them, not reflexively.

Atomicity is not correctness

Here's a trap that shows up in a lot of example code, including some of the articles I read for this post:

BEGIN;
UPDATE accounts SET balance = balance - 100
  WHERE account_id = 'A' AND balance >= 100;
UPDATE accounts SET balance = balance + 100
  WHERE account_id = 'B';
COMMIT;

The AND balance >= 100 guard looks like it prevents overdrafts. But if A only has 50, the first UPDATE matches zero rows, and an UPDATE that matches nothing is not an error. The second statement runs, B gets 100, and the transaction commits. Money was created out of nowhere, and atomicity did exactly its job: every statement succeeded, so all of them were applied.

Atomicity guarantees all-or-nothing for the operations you issued. It can't know that "debited zero rows" means "the transfer should fail". You have to turn that condition into an error. Two good ways:

Let a constraint do it. Put the rule in the schema and drop the guard from the WHERE clause:

CREATE TABLE accounts (
  account_id text PRIMARY KEY,
  balance    numeric NOT NULL CHECK (balance >= 0)
);

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"

Now an overdraft is an error, the transaction aborts, and atomicity takes it from there.

Check the row count. For "account doesn't exist" cases a constraint can't catch, check that each statement actually did something. In PL/pgSQL, FOUND is set after every UPDATE:

CREATE FUNCTION transfer(from_id text, to_id text, amount numeric)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE account_id = from_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'source account % not found', from_id;
  END IF;

  UPDATE accounts SET balance = balance + amount WHERE account_id = to_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'destination account % not found', to_id;
  END IF;
END;
$$;

Check after each statement. A single row-count check at the end only tells you about the last command, and can't tell you which account was missing.

Atomicity in application code

Most transactions aren't typed into psql; they're driven by application code, and that's where atomicity is most often broken by accident. Three anti-patterns come up again and again:

-- 1. No transaction at all: two independent autocommits
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 2. Committing too early: the credit runs outside the transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
COMMIT;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 3. Ignoring errors: the app keeps going after a failed statement
--    and reports success without checking what COMMIT returned

A good driver makes the right thing the default. With Rust's sqlx, a transaction rolls back automatically if it's dropped without being committed, so an early return through ? can't leave half a transfer behind:

async fn transfer(pool: &PgPool, from: &str, to: &str, amount: i64) -> Result<(), Error> {
    let mut tx = pool.begin().await?;

    let debited = sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE account_id = $2")
        .bind(amount)
        .bind(from)
        .execute(&mut *tx)
        .await?;
    if debited.rows_affected() != 1 {
        return Err(Error::AccountNotFound(from.into())); // tx dropped: ROLLBACK
    }

    let credited = sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE account_id = $2")
        .bind(amount)
        .bind(to)
        .execute(&mut *tx)
        .await?;
    if credited.rows_affected() != 1 {
        return Err(Error::AccountNotFound(to.into()));
    }

    tx.commit().await?; // only now is anything visible to others
    Ok(())
}

Whatever your language, the shape is the same: begin, do the work, check results, commit at the very end, and make sure every error path ends in a rollback.

Under the hood: how Postgres actually does it

Plenty of explanations stop at "the database rolls back on failure". But atomicity isn't a single feature called ROLLBACK. In Postgres it falls out of three mechanisms working together: MVCC tuple versions, the commit log, and the write-ahead log. Brandur Leach's excellent walkthrough of the Postgres source is the best deep dive I found, and most of this section follows it.

Why not just lock everything?

Imagine a toy database that stores everything in a CSV file. With one client it's fine. With two, they overwrite each other's changes. You could put an exclusive lock around every read and write, or funnel every operation through a single queue, but that serialises everything and doesn't scale.

Postgres instead uses multi-version concurrency control (MVCC). Writers don't overwrite rows in place. They create new versions of rows, and each reader decides which versions it's allowed to see. That one design choice is what makes rollback nearly free.

Every row version knows who created and deleted it

Postgres calls a stored row version a tuple. Every tuple's header carries two transaction IDs:

  • xmin: the transaction that inserted this version.
  • xmax: the transaction that deleted (or locked) it, or 0 if nobody has.

They're hidden system columns, but you can select them:

SELECT xmin, xmax, * FROM names;
 xmin  | xmax  | id |   name
-------+-------+----+----------
 27926 | 27928 |  1 | Hyperion
 27927 |     0 |  2 | Endymion

Row 1 was inserted by transaction 27926 and deleted by 27928. Row 2 is still live.

An UPDATE is a delete plus an insert: Postgres writes a brand-new tuple with xmin set to the updating transaction, and stamps the old tuple's xmax with the same ID. Indexes don't store visibility information at all; they point at tuples in the heap by a tuple ID, and the visibility check happens when the heap tuple is read.

Now look at what a rollback means in this world. If transaction 27930 inserts a row and then aborts, the tuple is still physically there with xmin = 27930. Nobody erases it. It just becomes invisible, because transaction 27930 is known to have aborted. Later, VACUUM (or an opportunistic in-page cleanup during a query) reclaims the space once no open snapshot could possibly need it.

That's the key insight: Postgres doesn't undo work on rollback. It just never makes the work visible. Aborting a transaction that touched a million rows is about as cheap as aborting one that touched a single row.

Transaction IDs are handed out lazily

You can see your own transaction ID:

BEGIN;
SELECT pg_current_xact_id_if_assigned();  -- NULL: no ID yet
INSERT INTO names (name) VALUES ('Kassad');
SELECT pg_current_xact_id_if_assigned();  -- 27931
COMMIT;

BEGIN does almost nothing. A transaction only gets an ID when it first writes, because that's when other transactions need to be able to tell its changes apart. Read-only transactions never get one at all. IDs are 32-bit counters under the hood that eventually wrap around, which is a large part of why VACUUM has to "freeze" old tuples, but that's a topic for another post.

The commit log: the single source of truth

If a tuple only records which transaction created it, something has to record whether that transaction committed. That's the commit log, stored in pg_xact/ in the data directory (called pg_clog before Postgres 10).

Despite the name, it's really a bitmap. Every transaction gets two bits:

00  in progress
01  committed
10  aborted
11  sub-committed   (a subtransaction whose parent hasn't finished yet)

Four transactions fit in a byte, so one 8 KB page covers 32,768 of them. You can ask it directly:

SELECT pg_xact_status('27930');  -- 'aborted'
SELECT pg_xact_status('27931');  -- 'committed'

Because every visibility decision ultimately consults this log, the answer is the same whether a transaction committed three hours ago or three milliseconds before a crash. Flipping a transaction's two bits from "in progress" to "committed" is the moment it becomes real.

Snapshots decide what you can see

When a transaction runs a query, it takes a snapshot: a picture of which transactions had finished at that moment. A snapshot has three parts:

  • xmin: the oldest transaction still running. Everything below it has finished.
  • xmax: one past the newest transaction that had completed. Everything at or above it is treated as still running, even if it commits later.
  • xip: the list of transaction IDs in between that were still in progress.
SELECT pg_current_snapshot();
-- 27940:27945:27940,27943

To decide whether a tuple is visible, Postgres asks two questions about its xmin (and similarly its xmax):

  1. Was that transaction already finished according to my snapshot? (Below xmin, or between xmin and xmax and not in xip.)
  2. If so, did it commit or abort? Look it up in the commit log.

A tuple is visible only if its creator committed before the snapshot and its deleter (if any) didn't. A half-finished transaction's tuples fail check 1. An aborted one's fail check 2. Either way, you never see partial work.

Commit-log lookups aren't free, so after the first reader confirms a tuple's creator committed, it sets hint bits such as HEAP_XMIN_COMMITTED on the tuple itself. Later readers can skip the lookup entirely.

The exact moment of commit

Here's the order of operations when you send COMMIT, simplified from CommitTransaction in the Postgres source:

COMMIT
  │
  ├─ 1. Write a commit record to the WAL and flush it to disk (fsync)
  │       ← from here on, the commit survives a crash
  │
  ├─ 2. Set the transaction's bits in pg_xact to "committed"
  │
  ├─ 3. Remove the transaction from the shared "proc array" and
  │      advance latestCompletedXid under an exclusive lock
  │       ← from here on, new snapshots see the changes
  │
  └─ 4. Release locks and reply to the client

The ordering matters. The WAL is flushed before the commit log is updated, so Postgres never marks something committed that it couldn't reproduce after a crash. And the client hears "COMMIT" only after all of it, so the database never reports a commit that didn't happen.

Savepoints get the same careful treatment. Subtransactions are first marked sub-committed, and only then is the parent marked committed. If the server crashes in between, the sub-committed children don't count, because their parent never committed. Even within a single commit-log page, the child bits are written before the parent bit, so a torn page write can't show a committed parent with uncommitted children.

The write-ahead log and crash recovery

The write-ahead log (WAL) is what lets all of this survive a crash. The rule is simple: a change is recorded in the WAL, and the WAL is flushed to disk, before the corresponding data page is allowed to be written.

It's a common simplification to describe the WAL as "write the intent, then on COMMIT write the data files". That's not quite how Postgres works, and the difference matters for understanding atomicity:

  • Changes are made to data pages in shared memory (the buffer cache) immediately, and each change produces a WAL record describing how to redo it.
  • The modified pages are written to the data files later, in the background, by the checkpointer and background writer, not at commit time.
  • At commit, only the WAL has to be flushed. That's one sequential write, which is why commits are fast.

When Postgres restarts after a crash, it finds the last checkpoint (a point where all dirty pages were known to be on disk) and replays every WAL record after it. This brings the data files back to exactly the state they were in at the crash, including tuples written by transactions that never committed.

And here's where MVCC pays off again: Postgres doesn't need an "undo" pass. A transaction that was in flight at the crash has no commit record in the WAL, so its commit-log status is never set to committed, and it's treated as aborted. Its tuples sit there, invisible, until vacuum removes them. The WAL in Postgres is a redo-only log; atomicity on recovery comes from the commit log, not from rewinding changes.

A couple of optimisations fall out of this design:

  • A transaction that never got an ID (read-only) writes nothing to the WAL or the commit log on commit.
  • Abort records are written to the WAL but not immediately flushed. If the server crashes before they reach disk, recovery treats the transaction as aborted anyway, so nothing is lost by skipping the fsync.
  • To protect against a page being only half-written during a crash, the first change to each page after a checkpoint writes the whole page image into the WAL (full-page writes).

Atomicity vs durability: synchronous_commit

It's worth separating the A from the D. If you set:

SET synchronous_commit = off;

Postgres acknowledges a commit before flushing its WAL record. A crash can now lose the last fraction of a second of commits. That's a durability trade-off. Atomicity is untouched: a lost transaction is lost entirely, and you'll never come back from a crash to find half of one. For high-volume, low-value writes (metrics, logs, session data) that's often a trade worth making.

Atomicity's neighbours

Atomicity on its own says nothing about what concurrent transactions see. That's isolation, and Postgres offers three levels:

  • Read Committed (the default): each statement sees data committed before that statement started.
  • Repeatable Read: the whole transaction sees one snapshot taken at its first query.
  • Serializable: transactions behave as if they ran one at a time; Postgres aborts one if that's not possible.

Set it on the transaction itself:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 'A';  -- 500
-- another session updates A to 600 and commits
SELECT balance FROM accounts WHERE account_id = 'A';  -- still 500
COMMIT;

(A SET TRANSACTION ISOLATION LEVEL issued before BEGIN does nothing but print a warning, because there's no transaction yet for it to apply to.)

The same snapshot machinery from the previous section powers all three levels. That's the elegant part: one set of mechanisms (tuple versions, snapshots, the commit log) gives Postgres both atomicity and isolation.

Takeaways

  • Atomicity means a transaction is all-or-nothing. Every statement is already atomic by itself; use BEGIN ... COMMIT to make several statements atomic together.
  • In Postgres, an error aborts the whole transaction. Further statements are rejected and COMMIT turns into ROLLBACK.
  • Use savepoints for partial rollback, but don't sprinkle them everywhere.
  • Atomicity doesn't check your logic. An UPDATE that matches zero rows succeeds. Push rules into constraints or check row counts so failures become errors.
  • Under the hood, Postgres never overwrites rows. New tuple versions plus xmin/xmax mean a rollback just leaves work invisible.
  • The commit log in pg_xact is the single source of truth for whether a transaction committed. Flipping its bits is the commit.
  • The WAL is redo-only and flushed before the commit is acknowledged. After a crash, unfinished transactions are simply treated as aborted.

SQL lets you write BEGIN and COMMIT and stop thinking about it. It's worth knowing, at least once, how much careful engineering sits behind those two words.

Further reading