ACID Transactions

A transaction bundles multiple reads and writes into a single unit — either the whole thing takes effect, or none of it does. ACID is the classic acronym for the guarantees a transactional database gives you: Atomicity, Consistency, Isolation, Durability.

Fig. 6 — The four ACID guarantees

Click a letter to see what it actually guarantees.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'a';
UPDATE accounts SET balance = balance + 100 WHERE id = 'b';
COMMIT;

If the process crashes between the two UPDATEs, atomicity guarantees the first one gets rolled back too — you never end up with money debited from one account and not credited to the other.

---

The four letters, each as a real failure it prevents

Letter	The guarantee	The bug you get without it
Atomicity	All statements in the transaction commit, or none do.	An order row is created but the order_items insert fails — now you have a paid order with nothing in it.
Consistency	The transaction moves the DB from one valid state to another (constraints hold).	A transfer leaves total money in the system higher than before because a CHECK (balance >= 0) was never declared.
Isolation	Concurrent transactions don’t see each other’s half-finished work.	Two people book the last seat because each read “1 seat left” before the other wrote “0”. (See Isolation Levels.)
Durability	Once COMMIT returns, the data survives a crash or power loss.	The database says “payment recorded”, the server loses power, and on reboot the payment is gone.

---

Where you actually reach for a transaction

Any time two or more writes only make sense together. Some everyday examples:

Money movement. Debit one account, credit another. Partial success is a disaster in either direction.

Order placement. Insert the orders row, insert N order_items rows, decrement products.stock_count, insert a payments row. If the stock decrement fails because something else sold the last unit, the whole order must evaporate.

User signup. Insert users, insert a profiles row, insert default notification_settings, add the user to an org_members table. A user who exists but has no profile row will crash every page that assumes the join.

“Claim” operations. Mark a job in a queue as processing and record which worker took it — in one atomic step — so two workers can’t grab the same job.

Outbox pattern. In the same transaction that writes your business data, insert a row into an outbox table describing the event to publish. A separate process reads the outbox and publishes to Kafka/SQS. This is how you get “the DB write and the message are never out of sync” without a distributed transaction.

---

Rollback: what triggers it, and doing it on purpose

A transaction ends in ROLLBACK (discarding every change) when:

• Any statement raises an error the app doesn’t catch (constraint violation, deadlock victim, type error).
• The client disconnects before COMMIT.
• The app explicitly runs ROLLBACK — a legitimate tool, not just an error path:

BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC-123';

-- application checks the new value
SELECT qty FROM inventory WHERE sku = 'ABC-123';
-- qty came back negative → we oversold

ROLLBACK;  -- pretend the whole attempt never happened

Savepoints — partial rollback

For long transactions where one optional step is allowed to fail:

BEGIN;
INSERT INTO import_batch (id) VALUES (42);

SAVEPOINT row_1;
INSERT INTO records (batch_id, data) VALUES (42, '...bad row...');
-- error!
ROLLBACK TO SAVEPOINT row_1;   -- undo just this row, keep the batch

INSERT INTO records (batch_id, data) VALUES (42, '...good row...');
COMMIT;

Bulk importers and “insert what you can, report the rest” jobs lean on this.

---

Transaction scope: the mistakes that cause outages

Don’t call external services inside a transaction. BEGIN; UPDATE ...; <HTTP call to Stripe that takes 4s>; COMMIT; holds row locks for 4 seconds per request. Under load the table effectively serializes. Do the external call first, then open a short transaction to record the result.

Don’t hold a transaction open across user “think time.” Opening a transaction when a user starts editing a form and committing when they click Save can lock rows for minutes. Use optimistic locking (a version column) instead.

“Transaction per web request” is usually too coarse. A request that reads some data, renders, then writes doesn’t need the reads inside the write transaction. Scope the transaction to the writes that must be atomic.

Beware the idle-in-transaction connection. A BEGIN with no matching COMMIT/ROLLBACK (a bug, or an unhandled exception) leaves a connection holding locks and blocking vacuum indefinitely. Set idle_in_transaction_session_timeout.

---

Retries and idempotency

Databases abort transactions that hit a deadlock or a serialization conflict — the app is expected to retry. That’s fine for pure DB work, but dangerous if the transaction had a side effect:

A payment transaction charges the card, then hits a deadlock on COMMIT and aborts. The app retries and charges the card a second time.

The fixes:

• Do side effects outside the transaction, keyed by an idempotency key so a replay is a no-op.
• Wrap retry loops only around transactions that are purely database writes.
• Cap retries (e.g. 3 attempts with backoff) so a persistently conflicting transaction doesn’t spin forever.

-- Idempotent claim: the UNIQUE constraint makes a duplicate attempt harmless
INSERT INTO payments (idempotency_key, order_id, amount)
VALUES ('req-abc-123', 42, 1999)
ON CONFLICT (idempotency_key) DO NOTHING;

---