Transactions & ACID

Status: 🟩 COMPLETE Last updated: 2026-06-19 Plain-English tagline: All-or-nothing operations. A transaction is a set of changes that succeed or fail together — never half-finished. ACID is the four properties that make this possible. Why banks trust databases.

In plain English

Imagine transferring $100 between bank accounts:

Debit $100 from George’s account
Credit $100 to Sarah’s account

If step 1 succeeds but step 2 fails (server crash, network glitch, anything), George just lost $100 that never showed up anywhere. Catastrophic.

A transaction wraps these steps into one logical unit. Either both succeed, or neither does. There’s no in-between state. If anything goes wrong, the database rolls back, and it’s as if nothing happened.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'george';
UPDATE accounts SET balance = balance + 100 WHERE id = 'sarah';
COMMIT;

If the system crashes between the two UPDATEs, no commit happened. Postgres rolls back automatically on next startup. Your data stays consistent.

ACID is the acronym for the four properties that make this work:

Atomicity — all-or-nothing
Consistency — data always satisfies rules
Isolation — concurrent transactions don’t see each other’s half-done state
Durability — once committed, it survives crashes

These properties are the bedrock of “I trust the database with my money / my users / my critical data.” Relational databases like Postgres take ACID seriously. Many NoSQL systems make trade-offs.

For your typical webapp: you rely on ACID without thinking about it most of the time. But knowing how transactions work matters when:

Multi-step operations must succeed together
Concurrent updates risk stepping on each other
Performance optimization requires understanding isolation

Why it matters

Data integrity. Without transactions, a server crash mid-operation leaves corrupt data.
Concurrency safety. Two users updating the same record simultaneously — transactions prevent lost updates and weird intermediate states.
Easier reasoning. “Either it all happened or nothing did” is a much simpler mental model than “some of it might have happened.”
Required for serious applications. Anything dealing with money, inventory, identity, scheduling — needs transactions.

ACID in detail

Atomicity

All operations in a transaction succeed together, or all fail together. No partial commits.

BEGIN;
DELETE FROM posts WHERE author_id = 'george';
DELETE FROM users WHERE id = 'george';
COMMIT;

If the second DELETE fails, the first is rolled back too. Either George and his posts are both gone, or both still exist. Never inconsistent.

Consistency

Data always satisfies all defined rules — constraints, foreign keys, types, custom checks. A transaction that would violate a rule is rejected.

-- Foreign key constraint: post.author_id must match users.id
INSERT INTO posts (author_id, title) VALUES ('nonexistent', 'Hi');
-- ERROR: insert violates foreign key constraint

The database enforces this. Your app code doesn’t have to constantly re-check; the DB ensures rules hold.

Isolation

Concurrent transactions don’t see each other’s intermediate state. From transaction A’s perspective, transaction B either hasn’t started or has fully completed — never “in progress.”

Postgres supports multiple isolation levels (Read Committed, Repeatable Read, Serializable) that trade isolation strength for concurrency.

Durability

Once a transaction is committed, the changes survive crashes, power failures, and restarts. The database writes to disk (the WAL — Write-Ahead Log) before confirming commit.

If your server crashes 1 second after COMMIT, the data is still there on disk. On restart, Postgres replays the WAL to recover.

This is why “the DB is the source of truth” works. ACID is what makes it trustable.

Transaction syntax

-- Postgres / standard SQL
BEGIN;
-- ... your statements ...
COMMIT;
 
-- Or roll back if anything goes wrong:
BEGIN;
-- ... statements ...
ROLLBACK;

In application code, your DB library handles BEGIN/COMMIT/ROLLBACK. You usually wrap a function call.

// Postgres pg library
await client.query('BEGIN');
try {
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [from]);
  await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [to]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
}

With Prisma:

await prisma.$transaction([
  prisma.account.update({ where: { id: fromId }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: toId }, data: { balance: { increment: 100 } } })
]);

With Supabase, transactions are typically done via stored procedures (since the REST API doesn’t naturally support multi-statement transactions):

-- Define a function that does the transfer atomically
CREATE OR REPLACE FUNCTION transfer(from_id uuid, to_id uuid, amount numeric) RETURNS void AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;
 
-- Call via Supabase SDK
const { data } = await supabase.rpc('transfer', { from_id, to_id, amount });

The function runs atomically within Postgres.

Savepoints — partial rollback

Inside a transaction, you can create savepoints to roll back to:

BEGIN;
INSERT INTO users (name) VALUES ('Alice');
SAVEPOINT after_alice;
INSERT INTO users (name) VALUES ('Bob');
-- Something goes wrong with Bob
ROLLBACK TO SAVEPOINT after_alice;
-- Alice's insert is still active; Bob's is gone
INSERT INTO users (name) VALUES ('Charlie');
COMMIT;
-- Result: Alice and Charlie are inserted; Bob isn't.

Useful for handling specific failures within a larger transaction. Most apps don’t need this.

Isolation levels

Postgres supports four isolation levels (from SQL standard):

Level	What it prevents
Read Uncommitted	(Nothing in Postgres — behaves like Read Committed)
Read Committed (default)	Dirty reads (seeing uncommitted data)
Repeatable Read	Non-repeatable reads (same query returning different results within a transaction)
Serializable	All anomalies — behaves as if transactions ran one at a time

Higher levels = more isolation, more locking, less concurrency.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ...
COMMIT;

For most webapps, the default Read Committed is fine. Reach for Serializable when you have genuine concurrency anomalies.

Read Committed (default)

Within a transaction, each statement sees the latest committed state at the time it runs.

If you SELECT a row, then SELECT it again later in the same transaction, you might see different values (if another transaction updated and committed in between). That’s the “non-repeatable read” anomaly.

Fast, generally fine for webapps.

Repeatable Read

Within a transaction, all statements see the same snapshot of the database. The same SELECT returns the same data regardless of others’ commits.

Heavier; can fail with “serialization failure” if Postgres can’t maintain the illusion. Your app retries.

Serializable

Transactions behave as if they ran one at a time. Strongest guarantee.

In Postgres, this is enforced via Serializable Snapshot Isolation (SSI) — it allows concurrent execution but rolls back transactions if it can’t prove they’re equivalent to some serial order.

Use for: financial / accounting code where weird interleavings cause real problems.

A concrete example: avoiding lost updates

A typical bug:

// Bad: lost update
const account = await db.accounts.find(id);  // balance: 100
account.balance += 50;
await db.accounts.update(id, { balance: account.balance });  // sets to 150

If another process does the same simultaneously, both see balance 100, both set to 150 — but the user got +50 twice. Lost update.

Fix 1: atomic update (no transaction needed):

await db.accounts.update(id, { balance: { increment: 50 } });
// SQL: UPDATE accounts SET balance = balance + 50 WHERE id = $1

This is atomic. Two concurrent calls both increment correctly.

Fix 2: optimistic locking:

UPDATE accounts SET balance = $new, version = version + 1
WHERE id = $id AND version = $expected_version;
-- If row count = 0, someone else updated; retry

Fix 3: pessimistic locking:

BEGIN;
SELECT * FROM accounts WHERE id = $id FOR UPDATE;
-- Now no one else can modify this row until we commit/rollback
UPDATE accounts SET balance = balance + 50 WHERE id = $id;
COMMIT;

For most apps, atomic updates are the simplest. Reach for locking when needed.

When transactions matter most

Money / accounting — every transfer is a multi-step operation that must be atomic
Inventory — decrement stock + create order must succeed together
User signup — create user + create profile + create initial settings, atomically
Booking systems — check availability + reserve slot, atomically (otherwise double-booking)
Any multi-row operation where partial completion = inconsistent state

For read-only operations, transactions are overkill. For writes that touch one row, atomic operations suffice. For multi-step writes, transactions are essential.

ACID vs BASE

Distributed NoSQL systems often relax ACID for scalability. They follow BASE principles:

Basically Available — system stays responsive
Soft state — state may change over time
Eventual consistency — given enough time, all replicas converge

Trade-off: scale and availability vs. strict correctness.

For webapps that care about correctness (most webapps), ACID is the right choice. Postgres delivers.

Common gotchas

Forgetting to wrap multi-step writes in a transaction. Half-finished operations corrupt data.
Long-running transactions. Hold locks; block VACUUM; harm concurrency. Keep transactions short.
Forgetting to commit. Transaction leaves connection in “idle in transaction” state. Eventually times out. Other transactions wait.
Forgetting ROLLBACK in catch. Caught error but didn’t roll back. Connection stays in transaction state.
Implicit transactions varying by tool. Some clients auto-commit; some don’t. Be explicit.
Deadlocks. Two transactions each waiting for a lock the other holds. Postgres detects and kills one. App should retry.
Serialization failures. Repeatable Read / Serializable can fail with “could not serialize access due to concurrent update.” App should retry.
Locks held longer than expected. SELECT FOR UPDATE locks until commit. Long-held locks block others.
Cross-DB transactions. Postgres transactions are within ONE database. For multi-DB consistency, use sagas, two-phase commit (complex), or accept eventual consistency.
Trying to do BEGIN inside a Supabase RPC. Functions are already in a transaction (the surrounding statement). Nested begin/commit is unusual.
Forgetting that DDL is transactional in Postgres. Schema changes (CREATE TABLE, etc.) are inside transactions and can roll back. Good for migrations.
Race conditions despite transactions. If you SELECT then UPDATE without explicit locking, two transactions can race. Use SELECT ... FOR UPDATE or atomic updates.
Read-modify-write outside transaction. Reading data, changing in app, writing back — without transaction, prone to lost updates.
Postgres’ default isolation may surprise. Read Committed is the most common default. If you need stricter, set explicitly.
MVCC and “I just inserted but the count went down.” Different transactions see different snapshots. Confusing but correct.
Triggers don’t roll back side effects outside the DB. A trigger that calls an external API can’t undo if the transaction rolls back. Be careful with side effects in triggers.
Idempotency in retries. If you retry a failed transaction, the operation should be idempotent (running it twice is safe). Use unique keys, idempotency tokens.
WAL not flushed on synchronous_commit off. Postgres can be configured to acknowledge commits before flushing to disk for speed. Trades durability for performance. Default (on) is safer.
PgBouncer transaction pooling breaks some features. Prepared statements, advisory locks behave oddly with transaction-mode pooling. Read pooler docs.
BEGIN; ... COMMIT from app code without proper error handling. A thrown exception between BEGIN and COMMIT may leave the connection in transaction state. Use try/finally / DB library’s built-in transaction helpers.

Sources

PostgreSQL — Transactions
PostgreSQL — Isolation Levels
Designing Data-Intensive Applications (Kleppmann) — chapter on transactions
Use the Index, Luke! — Chapter on Locks
Jim Gray — original ACID paper (1981)

Tech & AI, Explained

Explorer

transactions-and-acid