Database Transactions and ACID Properties Explained

A payment goes through, but the order status update that's supposed to happen right after it crashes the app server. The customer got charged, but their order shows as "pending" forever, and support has no idea why. This is exactly the class of bug transactions exist to prevent — and understanding ACID isn't academic trivia, it's the difference between "the crash caused a support ticket" and "the crash caused a customer to lose money."

What a transaction actually is

A transaction groups multiple operations so they either all succeed or all fail together — there's no partial state where some happened and others didn't.

BEGIN;

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

COMMIT;

If the process crashes between the two UPDATE statements, COMMIT never runs, and the database rolls back — account 1 never lost the $100. Without a transaction, a crash there leaves one account debited and the other never credited: money that simply vanished from the system.

ACID, with concrete meaning attached to each letter

• Atomicity — the transaction above: all statements succeed, or none do. No partial application.
• Consistency — the database moves from one valid state to another. If a constraint says balances can't go negative, a transaction that would violate it fails entirely, rather than leaving the database in a state that breaks your invariants.
• Isolation — concurrent transactions don't see each other's uncommitted changes. Two customers checking out at the same time shouldn't be able to both "reserve" the last item in stock.
• Durability — once committed, the change survives a crash. This is why databases fsync to disk on commit instead of just holding the write in memory.

Isolation levels: where most real bugs live

Isolation is the property developers actually get wrong in practice, because "isolated" doesn't mean one universal thing — it's a spectrum with real trade-offs:

-- PostgreSQL default: READ COMMITTED
-- Sees other transactions' committed changes, but each statement
-- within your transaction can see different data if something
-- else commits in between.

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Your transaction sees a consistent snapshot for its entire duration,
-- even if other transactions commit changes while yours is running.

A classic bug this catches: you read a row's value, do some calculation, then write an update based on that calculation — but another transaction changed the row in between your read and write. REPEATABLE READ (or explicit row locking with SELECT ... FOR UPDATE) prevents this "lost update" problem; the default READ COMMITTED does not.

Row locking for read-then-write logic

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- other transactions trying to touch this row now wait
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

FOR UPDATE locks the selected row until your transaction commits or rolls back, so two concurrent "buy the last item" requests can't both succeed based on stale stock counts.

Common mistakes

• Wrapping a single UPDATE statement in BEGIN/COMMIT for no reason — single statements are already atomic in virtually every relational database. Transactions matter when you have multiple statements that must succeed or fail together.
• Holding a transaction open across a slow external call (an API request, a file upload) — this holds locks and connections far longer than necessary, and is a common cause of connection pool exhaustion under load.
• Assuming the default isolation level prevents race conditions it doesn't. READ COMMITTED (Postgres and most databases' default) does not prevent lost updates on read-then-write logic.
• Forgetting that a transaction that isn't explicitly committed or rolled back can leave a connection — and its locks — held indefinitely if the client crashes without cleanup.

What I'd actually use

READ COMMITTED for the vast majority of application code — it's the sensible default for a reason. Reach for SELECT ... FOR UPDATE or REPEATABLE READ specifically for read-then-write sequences where a race condition would cause real damage (inventory counts, balance transfers, anything involving money or finite resources). Keep transactions as short as possible — begin them right before the work that needs atomicity, commit immediately after.

Next steps

Find one place in your codebase that reads a value, computes something from it, then writes a new value back — a stock decrement, a balance update, a counter increment. Check whether it's wrapped in a transaction with appropriate locking. If not, that's your highest-value place to add one.