Soumitra SahaMay 25, 2025

Transactions and ACID Properties Without the Jargon

I walk through what a PostgreSQL transaction actually guarantees, break down each ACID property with a plain analogy, and explain isolation levels and the pitfalls that trip up most teams.

The word “transaction” is one of those terms that sounds understood right up until something breaks. You know BEGIN and COMMIT go together. You know a rollback undoes changes. But then you have two API endpoints hitting the same table concurrently and the numbers still come out wrong — even though both used transactions. The BEGIN and COMMIT were there. So what happened?

The answer is almost always isolation. A transaction isn’t just a wrapper that groups statements together. It’s a contract the database makes with you about consistency, visibility, and durability. Understanding exactly what that contract covers — and what it doesn’t — is what turns BEGIN/COMMIT from boilerplate into a tool you can reason about.

I’m going to walk through what a transaction actually guarantees at the contract level, explain each ACID property with an analogy you can picture, show how isolation levels change what your queries can see, and cover the two failure modes that cause the most confusion once you reach production.

Series: Part 4 of 4 — the PostgreSQL series finale. Start with Part 1: PostgreSQL for Developers · Previous: Database Indexing: Why Your Queries Get Slow

What a transaction actually is

Without a transaction block, PostgreSQL runs in autocommit mode — every individual statement is its own transaction. An UPDATE commits immediately on success and rolls back on its own when it fails. That’s fine for single operations, but it falls apart the moment you need two or more statements to succeed or fail together.

A transaction groups those statements into a single unit with one core promise: either all of them are applied permanently, or none of them are. The database treats the group as indivisible.

The classic example is a bank transfer. You debit one account and credit another. If the server crashes after the debit but before the credit, one account loses money that never arrived anywhere. With both updates inside a transaction, PostgreSQL guarantees they land together or not at all.

A transaction also controls what other connections can see while your statements are running. The exact rules for that visibility are what isolation levels govern, and that’s where most developers have gaps.

The four ACID properties, explained plainly

ACID stands for Atomicity, Consistency, Isolation, and Durability. Each word describes one specific part of what the database promises.

Atomicity is the all-or-nothing guarantee. Every statement inside a BEGIN/COMMIT block succeeds together, or the whole thing rolls back. Think of countersigning a contract: either all signatures land or none of them count. If your process dies mid-transaction, PostgreSQL uses its write-ahead log (WAL) to undo the partial work on restart.

Consistency means the transaction brings the database from one valid state to another. Your constraints still hold after it commits — foreign keys, UNIQUE constraints, CHECK constraints, NOT NULL columns. A transaction that would violate any of them is rejected and rolled back. This isn’t something you configure; the database’s constraint system enforces it automatically.

Isolation controls how much one transaction can see of another’s in-progress work. This is the property with actual knobs. A transaction at the strictest isolation level sees data as if it were the only transaction running. A looser level trades some correctness guarantees for throughput. The four levels in the SQL standard map onto three distinct behaviors in PostgreSQL — more on that in the next section.

Durability means that once a transaction commits, that data is permanent. It survives a server crash, a power cut, a restart. PostgreSQL writes the commit record to the WAL before confirming success to the client. Until that write lands, the commit hasn’t happened from the database’s perspective.

The property I see misunderstood most often is Isolation. People tend to treat it as binary: either you have it or you don’t. The levels change what’s actually guaranteed.

Check this before moving on

You can explain why Atomicity alone doesn’t prevent two concurrent transactions from reading each other’s committed changes
You understand that Consistency enforcement comes from the database’s constraint system, not just the transaction wrapper
You know Durability depends on the WAL flush completing before PostgreSQL confirms the commit

Isolation levels: the part most tutorials skip

PostgreSQL supports four isolation levels from the SQL standard, but only three have distinct behaviors. Read Uncommitted behaves identically to Read Committed in PostgreSQL — dirty reads (reading another transaction’s uncommitted data) aren’t possible at any level, because PostgreSQL’s MVCC (Multiversion Concurrency Control) architecture prevents them by design.

Isolation level	Dirty read	Nonrepeatable read	Phantom read	Serialization anomaly
Read Uncommitted	Not possible (PostgreSQL)	Possible	Possible	Possible
Read Committed	Not possible	Possible	Possible	Possible
Repeatable Read	Not possible	Not possible	Not possible (PostgreSQL)	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

A nonrepeatable read is when you run the same SELECT twice inside one transaction and get different results, because another session committed a change between the two reads. Read Committed allows this. Repeatable Read prevents it.

A phantom read is when a second SELECT returns different rows entirely — not different values in existing rows, but rows that weren’t there before — because another session inserted or deleted records between your two queries. The SQL standard says Repeatable Read allows phantoms, but PostgreSQL prevents them too at that level, because it uses snapshot isolation underneath.

A serialization anomaly occurs when two concurrent transactions produce results inconsistent with any sequential ordering. Only the Serializable level prevents this.

Read Committed — the default — gives each statement a fresh snapshot of committed data as of the moment it runs. Two SELECTs in the same transaction can see different data if something committed between them. For most straightforward operations in a REST API, this is the right level: simple, fast, no retry logic needed.

Repeatable Read gives you a snapshot at the start of the transaction. Every query inside sees the database as it was when the transaction began — nothing committed by another session during your transaction is visible. Use this when a transaction needs a consistent view across multiple queries: a multi-table report, an export that reads balance and line items, any calculation where you need the data to hold still.

Serializable adds monitoring on top of Repeatable Read. PostgreSQL tracks read/write dependencies across concurrent serializable transactions using Serializable Snapshot Isolation (SSI) and rolls one back with SQLSTATE 40001 if it detects a pattern that couldn’t have happened in a sequential execution. This is the strictest guarantee, and it comes with a cost: your application code must retry aborted transactions. Don’t reach for it by default, but do reach for it when correctness matters more than throughput and the alternatives involve complicated explicit locking.

BEGIN, COMMIT, ROLLBACK, and SAVEPOINT in SQL

The basic pattern:

BEGIN;

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

COMMIT;

If either statement fails, calling ROLLBACK undoes both. One thing that trips developers up: when a statement inside an active transaction errors, PostgreSQL puts the transaction into an aborted state immediately. You can’t run more statements — every one returns ERROR: current transaction is aborted, commands ignored until end of transaction block. You must ROLLBACK before the connection is usable again.

To set an isolation level, pass it to BEGIN directly:

BEGIN ISOLATION LEVEL REPEATABLE READ;

Or use SET TRANSACTION immediately after BEGIN:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Savepoints let you roll back part of a transaction without aborting the whole thing:

BEGIN;

INSERT INTO orders (user_id, total) VALUES (42, 150.00);

SAVEPOINT order_created;

INSERT INTO order_items (order_id, sku, qty) VALUES (1, 'WIDGET-01', 3);

-- If inserting the item fails, roll back only that step:
ROLLBACK TO SAVEPOINT order_created;

-- The order row is still intact; only the item insert was undone.
COMMIT;

Savepoints are useful inside long procedures where partial failures are recoverable without discarding prior work.

What goes wrong: long transactions and deadlocks

Long-held transactions are one of the most common sources of production trouble. An open transaction holds row-level locks on everything it’s modified. Other transactions trying to write to those rows will wait — indefinitely, unless a timeout fires. If your application opens a transaction, does some processing, calls an external service, then commits, you’re holding those locks for the entire duration of that external call.

The PostgreSQL setting idle_in_transaction_session_timeout lets you kill connections that sit idle inside an open transaction for too long. Setting it to 30000 (30 seconds) terminates stuck connections before they pile up into a lock queue. It’s worth enabling in production.

The database indexing post covers how to read EXPLAIN ANALYZE output — if you’re inside long transactions, that’s often the right next step to check whether slow queries are contributing to lock hold time.

Deadlocks happen when two transactions each hold a lock the other needs. Transaction A locks row 1 and waits for row 2. Transaction B has locked row 2 and is waiting for row 1. Neither can move forward.

PostgreSQL detects deadlocks automatically and aborts one of the two transactions with:

ERROR: deadlock detected
DETAIL: Process 17234 waits for ShareLock on transaction 7831; blocked by process 17289.

The surviving transaction completes normally. The aborted one needs to be retried by the application.

The practical prevention is consistent lock ordering: if every transaction that touches accounts 1 and 2 always acquires the row for account 1 before account 2, two such transactions can never deadlock each other. They might queue, but they won’t cycle. Where ordering can’t be guaranteed upfront, build in retry logic for the deadlock detected error.

Choose your isolation level deliberately

The most useful reframe I found in the PostgreSQL concurrency documentation: the default isolation level is a choice the database made for you when you didn’t specify otherwise. Read Committed is a sensible default for most workloads, but “sensible default” and “correct for this transaction” aren’t the same thing.

If your transaction reads a value, applies some logic, and writes a result back, you’re implicitly assuming the value didn’t change between the read and the write. Read Committed doesn’t guarantee that. Repeatable Read does. That distinction is what separates a race condition you haven’t triggered yet from one you never will.

Start with Read Committed. Move to Repeatable Read when a transaction needs consistent reads across multiple queries. Add retry logic and reach for Serializable only when you need correctness guarantees that neither of the lower levels provides. Check the SQL joins post if multi-table reads inside a transaction are producing unexpected results — sometimes what looks like an isolation issue is actually a join condition.