Chapter 7 — Transactions & ACID

The why

A user transfers ₹10,000 from savings to checking. The money leaves the savings account. The server crashes. The money never arrives in checking. The user just lost ₹10,000. Transactions exist so this never happens.

Transactions are the "all or nothing" guarantee of database systems. They're the reason you can trust a database with money, medical records, and mission-critical data. Without transactions, every operation is a gamble.

Core concepts

ACID — the four guarantees

Every relational database promises ACID. Knowing what each letter actually means in practice is the difference between debugging a data corruption bug for 2 hours and for 2 weeks.

// Without transactions — the worst case:
async function transfer(fromId, toId, amount) {
  // Step 1: deduct
  await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
  // 💥 Server crashes here! Money deducted but never credited.

Isolation levels — from fastest (least safe) to safest (slowest)

PostgreSQL supports four isolation levels defined by the SQL standard. In practice, PostgreSQL's implementation is stricter than the standard.

Dirty read: Transaction A reads uncommitted data written by Transaction B. If B rolls back, A used data that never existed. PostgreSQL prevents this even at Read Uncommitted.

Non-repeatable read: Transaction A reads a row. Transaction B updates that row and commits. Transaction A reads again and sees different data. "The row changed while I was looking at it."

-- Non-repeatable read (Read Committed):
-- T1: BEGIN; SELECT balance FROM accounts WHERE id = 1; -- returns 1000
-- T2: UPDATE accounts SET balance = 900 WHERE id = 1; COMMIT;
-- T1: SELECT balance FROM accounts WHERE id = 1; -- returns 900! Different!

Phantom read: Transaction A runs the same query twice. Between the two queries, Transaction B inserts a new row that matches A's WHERE clause. The second query returns a "phantom" row that wasn't there the first time.

-- Phantom read (Read Committed):
-- T1: SELECT * FROM orders WHERE status = 'pending'; -- returns 5 rows
-- T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
-- T1: SELECT * FROM orders WHERE status = 'pending'; -- returns 6 rows! The 6th is a phantom.
-- (PostgreSQL's Repeatable Read prevents this; Read Committed allows it.)

Serialization anomaly: The result of running concurrent transactions is impossible to achieve if you ran them one at a time in any order. The most insidious concurrency bug — no individual row conflict, but the final state is logically wrong.

When to use each isolation level

SELECT FOR UPDATE — explicit row locking

Sometimes you need to read a row and guarantee nobody else modifies it between your read and your write. SELECT ... FOR UPDATE locks the selected rows until your transaction ends.

BEGIN;
 
-- Lock this seat so no other transaction can book it
SELECT * FROM seats
WHERE flight_id = 42 AND seat_number = '14A'
FOR UPDATE;  -- 🔒 Locked until COMMIT/ROLLBACK
 
-- Now safe: no other transaction can read+lock this row
-- Check if available, then book it
UPDATE seats SET booked_by =

SET lock_timeout = '2s';
SELECT * FROM seats WHERE flight_id = 42 AND seat_number = '14A' FOR UPDATE;

Deadlocks

A deadlock happens when two transactions each hold a lock the other needs. Neither can proceed. PostgreSQL detects deadlocks and kills one transaction automatically.

Transaction A: locks row 1, then tries to lock row 2
Transaction B: locks row 2, then tries to lock row 1
 
A waits for B to release row 2.
B waits for A to release row 1.
→ DEADLOCK. PostgreSQL kills one transaction after ~1 second.

The fix: Always lock resources in the same order. If both transactions lock row 1 first, then row 2, no deadlock. Also: keep transactions short. The longer a transaction holds locks, the higher the chance of deadlock.

-- Safe: consistent locking order
-- Always lock the smaller user_id first
SELECT * FROM accounts WHERE user_id = LEAST($1, $2) FOR UPDATE;
SELECT * FROM accounts WHERE user_id = GREATEST($1, $2) FOR UPDATE;
--

// In application code: always retry on deadlock
async function transferWithRetry(fromId, toId, amount, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const client

Real-world example: How Stripe handles idempotency with transactions

Stripe's API accepts an Idempotency-Key header on every request. If the same key comes in twice, Stripe returns the original result — even if the first request crashed mid-processing. This is implemented with database transactions:

-- Simplified Stripe idempotency pattern:
BEGIN;
 
-- Check if we've already processed this key
SELECT response FROM idempotency_keys WHERE key = $1 FOR UPDATE;
 
-- If found, return cached response
-- If not found, process the charge
INSERT INTO charges (amount, currency, customer_id)
VALUES ($2, $3, $4)

The FOR UPDATE lock ensures that if two requests with the same idempotency key arrive simultaneously, one blocks until the other commits — then the second one finds the cached response and returns it instead of double-charging. No distributed lock, no Redis, just PostgreSQL.

Common mistakes

Production notes

• Set idle_in_transaction_session_timeout. Kills transactions that have been idle too long (open but not running queries). Prevents accidental lock accumulation.
• Set lock_timeout. If a query waits for a lock longer than this, it fails instead of hanging indefinitely. Much better than a 30-second timeout at the application level.
• Monitor pg_stat_activity for waiting queries. SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; — shows you what's blocking what.
• Use pg_locks to debug locking issues. It shows every lock currently held, what holds it, and what's waiting.

Performance notes

• Keep transactions short. The longer a transaction, the more locks accumulate, the more transactions queue up waiting for locks. This is the #1 cause of production database slowdowns at scale.
• SELECT FOR UPDATE blocks readers too — in some isolation levels. In Repeatable Read and Serializable, a SELECT FOR UPDATE blocks other SELECT FOR UPDATEs AND regular SELECTs waiting for the same rows. In Read Committed, regular SELECTs skip locked rows.
• Batch updates in chunks. UPDATE ... WHERE ... LIMIT 1000 inside a loop with brief pauses is safer than UPDATE on 10M rows in one transaction.

Security notes

• Transactions don't prevent SQL injection. Parameterized queries do. A transaction can atomically execute malicious SQL just as easily as safe SQL.
• Row-level locks can be used for DoS. If an attacker can trigger SELECT FOR UPDATE on popular rows (e.g., a user with millions of followers), they can block legitimate operations. Implement lock_timeout and rate limiting.

Exercises

Beginner

1. Write a transaction that transfers 500 between two accounts. Test it: verify that a ROLLBACK restores both balances. Verify that a COMMIT permanently changes both.
2. Open two psql sessions. In Session A, BEGIN; UPDATE accounts SET balance = 0 WHERE id = 1; (no COMMIT). In Session B, SELECT * FROM accounts WHERE id = 1;. What happens? Now COMMIT in Session A and query again in Session B. Explain.

Intermediate

1. Simulate a deadlock: Session A locks row 1 then tries to lock row 2. Session B locks row 2 then tries to lock row 1. Observe PostgreSQL detect and resolve the deadlock. Then fix it with consistent lock ordering.
2. Write a seat booking system: seats table, SELECT FOR UPDATE, check availability, book or return "already taken". Test with concurrent booking attempts for the same seat.

Advanced

1. Implement a transaction with Repeatable Read isolation that detects when inventory has changed between read and write. If the snapshot has changed (serialization failure), retry with exponential backoff.
2. Implement a transaction-based idempotency key system similar to Stripe's. Test with concurrent identical requests and verify no double-processing.

Interview questions

Beginner

1. What is a database transaction? A unit of work that is atomic (all or nothing), consistent (valid state transitions), isolated (doesn't interfere with other transactions), and durable (survives crashes). ACID.

2. What happens if a transaction fails partway through? The entire transaction is rolled back — all changes made so far are undone. The database returns to the state it was in before the transaction began.

3. What is a deadlock? Two or more transactions each hold a lock the other needs, creating a cycle where neither can proceed. The database detects this and aborts one transaction to break the cycle.

Senior

4. Explain the difference between Read Committed and Repeatable Read isolation. Read Committed: each statement sees only committed data, but two SELECTs in the same transaction can see different data if another transaction committed in between. Repeatable Read: the entire transaction sees a snapshot of the database as it was when the transaction began. Any changes committed by other transactions after the snapshot are invisible.

5. When would you use Serializable isolation? For operations where logical correctness depends on the illusion of sequential execution: financial ledgers, booking/auction systems, inventory systems where overbooking is unacceptable. Serializable isolation is ~2–5x slower but guarantees no concurrency anomalies.

6. How do you prevent deadlocks in application code? 1) Always acquire locks in a consistent order (sort IDs, always lock table A before table B). 2) Keep transactions short — the less time you hold locks, the lower the chance of collision. 3) Set lock_timeout so transactions fail rather than hang. 4) Always retry transactions that fail due to deadlock (PostgreSQL error code 40P01).

Summary

Transactions are the foundation of data integrity. ACID guarantees — Atomicity, Consistency, Isolation, Durability — are what make databases trustworthy. Read Committed is the default and right for 95% of queries. Use Repeatable Read or Serializable for read-then-write operations where correctness matters (money, inventory, bookings). SELECT FOR UPDATE explicitly locks rows for safe read-modify-write cycles. Deadlocks happen — detect, retry with backoff, and always lock resources in a consistent order.

Quick recall

• ACID: Atomic (all or nothing), Consistent (valid states), Isolated (no interference), Durable (survives crashes).
• Default: Read Committed. For money/bookings: Repeatable Read or Serializable.
• SELECT FOR UPDATE locks rows. Use consistent lock ordering. Set lock_timeout.
• Never call external APIs inside a transaction. Keep transactions short.
• Deadlock → retry with exponential backoff (PostgreSQL error 40P01).
• Always ROLLBACK on error. Always use BEGIN/COMMIT/ROLLBACK explicitly.

Quiz

1. What does Atomicity guarantee? All operations in a transaction succeed or none do. No partial writes visible to any other transaction.
2. What isolation level does PostgreSQL use by default? Read Committed.
3. What's the difference between a deadlock and a lock wait? A lock wait is one transaction waiting for another to release a lock. A deadlock is a cycle — A waits for B, B waits for A. Lock waits resolve when the holder commits. Deadlocks require the database to abort one transaction.
4. What does SELECT FOR UPDATE do? Locks the selected rows until the transaction ends, preventing other transactions from modifying or (depending on isolation level) reading them.
5. Why shouldn't you make HTTP calls inside a database transaction? The HTTP call could take seconds or minutes. The transaction holds locks the entire time, blocking all other transactions. It can cause cascading timeouts.
6. How does PostgreSQL detect deadlocks? It maintains a Wait-For Graph of which transactions are waiting for which locks. If it detects a cycle, it aborts one transaction (usually the one that has done less work).
7. What error code does PostgreSQL return for a deadlock? 40P01 (deadlock_detected). Application code should catch this and retry.