SQL 07 — Transactions & Isolation

Two psql windows, one balance row, and you are going to watch ₹300 vanish — then make the same race impossible with three extra words of SQL. Everything @Transactional ever did for you in Spring happens at this layer, and this is the module where you stop trusting the annotation and start understanding the machine under it. This is fintech-interview territory, and you’ll have run every scenario with your own hands.

The Goal

By the end of this module you can:

• Drive transactions live in psql — BEGIN, COMMIT, ROLLBACK — and say what COMMIT actually promises
• Map ACID onto real SplitEase money moments, not textbook definitions
• Reproduce the concurrency anomalies — dirty read, non-repeatable read, phantom — in two terminals, and say which PostgreSQL allows at which level
• Choose an isolation level honestly: what each blocks, what each costs
• Lock rows with SELECT ... FOR UPDATE to kill the check-then-debit race — the fintech scenario
• Manufacture a deadlock, read the actual error, and state the fix in one sentence

The Lesson

All or nothing — the half-saved expense, at the layer where it lives

In Spring Boot 05 you met the horror story: SplitEase saves the expense row, crashes before the shares, and the books show ₹3,000 spent with nobody owing anything. @Transactional was the fix. Here is what that annotation actually sends to PostgreSQL:

BEGIN;

INSERT INTO expense (group_id, description, amount_paise, paid_by)
VALUES (1, 'dinner', 300000, 1);

-- the app crashes HERE, before the shares are written

Open psql and run exactly that, then — in a second psql window — look for the expense:

SELECT * FROM expense WHERE description = 'dinner';
-- (0 rows)

The second window can’t see it. The insert exists only inside your open transaction. Now back in the first window:

ROLLBACK;

Gone, completely — as if it never happened. That’s a transaction: a group of statements that becomes real all together at COMMIT, or not at all. If the app dies mid-transaction, PostgreSQL rolls back automatically. The half-saved expense is not “prevented” by Spring; it’s prevented by this mechanism, which Spring merely switches on.

One thing psql hides well: autocommit. Every statement you’ve typed in this whole track without BEGIN was silently its own tiny transaction — begin, run, commit, in one breath. BEGIN is you taking manual control of where the boundary sits.

What COMMIT actually promises

When COMMIT returns, PostgreSQL has appended your changes to the write-ahead log (WAL) and forced that log to physical disk — before answering you. The actual table pages may be updated lazily, minutes later, because if the machine loses power first, PostgreSQL replays the WAL at startup and reconstructs every committed change. That’s the durability contract: COMMIT returning means your dinner expense survives a power cut one millisecond later. It is also why COMMIT has real latency — a disk flush — and why “the database is slow” sometimes means “the disk under the WAL is slow.”

ACID, mapped to money

A, C, D are mostly automatic. Isolation is the negotiable one — it comes in levels, the levels have prices, and choosing is your job. The rest of this module is isolation.

Two sessions, three anomalies

Concurrency bugs have names. Each is a specific way one transaction sees another’s work mid-flight:

The dirty-read row deserves a pause: many databases historically allowed it at their lowest level. PostgreSQL’s MVCC design (every transaction reads from committed snapshots) makes it impossible, full stop. You’ll reproduce the second anomaly yourself in Build This — it’s the one that bites real apps daily.

Isolation levels in PostgreSQL, honestly

Two honest footnotes the textbooks fudge. First: the SQL standard permits phantoms at REPEATABLE READ, but PostgreSQL’s snapshot implementation blocks them anyway — PostgreSQL is stronger than the standard here, and interviewers who know PostgreSQL know this. Second: stronger levels don’t make bugs disappear, they convert silent wrong-answers into loud serialization_failure errors that your code must catch and retry. Correctness moves from “hope” to “error handling.”

Switching level is per-transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... your statements ...
COMMIT;

Locks without fear — the check-then-debit race

Now the scenario this module exists for. SplitEase adds settlement wallets; Asha has ₹500 and two payouts of ₹400 fire at the same moment — double-clicked button, two app instances, doesn’t matter. The app logic is the obvious one: read the balance, check it’s enough, write the new balance. Run concurrently with no locking:

1. Transaction A reads balance = 50000. Enough for 40000. Proceeds.
2. Transaction B reads balance = 50000. Also enough. Also proceeds.
3. A writes balance = 10000, commits.
4. B writes balance = 10000, commits.

Final balance: ₹100. Money actually paid out: ₹800 against a ₹500 wallet — ₹300 materialized out of a race condition. No error, no log line, nothing. This is the lost update / check-then-act bug, and it is the single most expensive bug class in fintech.

Isolation levels alone don’t express “nobody else may even read-for-writing this row until I’m done.” Locks do. UPDATE already takes a row lock implicitly — the problem is your read took no lock, so both transactions sailed past the check. The fix is to make the read grab the same lock the write would:

BEGIN;
SELECT balance_paise FROM wallet WHERE friend_id = 1 FOR UPDATE;
-- row is now locked; anyone else's FOR UPDATE or UPDATE on it WAITS
UPDATE wallet SET balance_paise = 10000 WHERE friend_id = 1;
COMMIT;   -- lock released

flowchart TD
    A["Txn A: SELECT FOR UPDATE locks the Asha row, sees 50000"] --> B["Txn B: SELECT FOR UPDATE on the same row"]
    B --> C["B blocks and waits"]
    A --> D["A debits to 10000 and commits"]
    D --> E["B unblocks and reads the fresh value 10000"]
    E --> F["10000 is less than 40000 so B aborts the payout"]

B is forced to wait, then sees the post-debit truth, and its check fails — correctly. One payout succeeds, one is refused, zero rupees invented. FOR UPDATE is the standard tool for every read-then-write money flow; you’ll run this race both ways in Build This.

Deadlocks — produced, read, fixed

Locks introduce one new failure mode. A locks Asha’s row and wants Rohit’s; B has locked Rohit’s row and wants Asha’s. Both wait forever — except PostgreSQL detects the cycle within about a second, picks a victim, and kills it:

ERROR:  deadlock detected
DETAIL:  Process 5141 waits for ShareLock on transaction 770; blocked by process 5172.
         Process 5172 waits for ShareLock on transaction 769; blocked by process 5141.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "wallet"

Read it like a stack trace: two processes, each waiting on the other’s transaction — a cycle. The victim’s transaction is rolled back (atomicity working as designed: retry it); the survivor proceeds. The fix is not “fewer locks,” it’s lock order: if every transaction that touches multiple wallets locks them in the same order — say, ascending friend_id — a cycle is geometrically impossible. One sentence, learn it for life: deadlocks come from inconsistent lock order; the fix is a consistent one.

How this surfaces in Spring

Everything above is what @Transactional actually configures. The annotation opens BEGIN when the method starts, COMMIT when it returns, ROLLBACK on a runtime exception — the boundary you drew by hand in psql, drawn by a proxy. Isolation is literally a property on it:

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void settleMonth(long groupId) { ... }

And SELECT ... FOR UPDATE surfaces as @Lock(LockModeType.PESSIMISTIC_WRITE) on a repository method. The week 5 takeaway: when a Spring service does read-check-write on money, the question in code review is “what stops two threads passing the check together?” — and now you can answer it at the SQL layer, where the answer actually executes.

See It Move

Watch the second read in transaction A — the same SELECT, and whether it returns the same number is the entire difference between the two modes.

Step through it and notice:

• In READ COMMITTED mode, A’s second read sees B’s committed write — the value changed inside A’s transaction. That is the non-repeatable read, happening in front of you.
• In REPEATABLE READ mode, A’s snapshot was frozen at its first query — B’s commit is invisible until A ends. Same interleaving, different truth.
• B never sees A’s uncommitted work in either mode — dirty reads don’t exist here at any level.
• Neither mode is “the right one”: READ COMMITTED gives freshness, REPEATABLE READ gives consistency-within-the-transaction. Reports want the snapshot; quick lookups want fresh.

Check The Concept

How This Shows Up At Work

• The double-payout incident. A user double-clicks Withdraw, two requests pass the balance check together, the company pays twice. This exact bug has cost real fintechs crores, and the postmortem fix is always the same three words: SELECT FOR UPDATE (or its optimistic-locking cousin). You can now reproduce it on demand in two terminals — most engineers who fixed one have never actually watched one happen.
• The month-end deadlock storm. Settlement batch A walks wallets in id order; a refund job walks them in reverse. All quiet until volume spikes, then deadlock detected floods the logs. The engineer who asks “what order does each job lock rows in?” closes the incident in an hour.
• The interview ladder. “What’s the default isolation level in PostgreSQL and what does it allow?” is the warm-up; “how would you make a balance debit safe under concurrency?” is the real question; walking the FOR UPDATE blocking sequence from memory is the offer. Bonus point that signals depth: PostgreSQL can’t dirty-read, and its REPEATABLE READ blocks phantoms.
• The code review catch. A PR reads a row, checks a condition in Java, then writes — no lock, no version column. The comment “this check-then-act races; what holds the row between read and write?” is one of the highest-value sentences a backend reviewer can type.

Build This

The crown jewel of this module: open two psql terminals side by side, both connected to splitease_sql. Call them A (left) and B (right). Every experiment is a choreography — type each row of the tables in order, top to bottom.

1. Setup (either terminal) — a settlement wallet per friend, ₹500 each:

CREATE TABLE wallet (
  friend_id     BIGINT PRIMARY KEY REFERENCES friend(id),
  balance_paise BIGINT NOT NULL
);
INSERT INTO wallet SELECT id, 50000 FROM friend;
SELECT * FROM wallet ORDER BY friend_id;

You should see four rows of 50000. (Asha is friend_id = 1 below — adjust if your ids differ.)

2. Experiment 1 — watch a non-repeatable read happen.

   #	Terminal A types	Terminal B types	What happens
   1	BEGIN;	—	A opens a transaction at default READ COMMITTED
   2	SELECT balance_paise FROM wallet WHERE friend_id = 1;	—	A sees 50000
   3	—	UPDATE wallet SET balance_paise = 99000 WHERE friend_id = 1;	B runs in autocommit — committed instantly
   4	SELECT balance_paise FROM wallet WHERE friend_id = 1;	—	A sees 99000 — same transaction, same query, different answer
   5	COMMIT;	—	The non-repeatable read, witnessed

   If A had been computing “Asha’s share of the month” across several queries, rows 2 and 4 just poisoned the report mid-calculation.

3. Experiment 2 — fix it with REPEATABLE READ. Reset first: UPDATE wallet SET balance_paise = 50000 WHERE friend_id = 1;

   #	Terminal A types	Terminal B types	What happens
   1	BEGIN ISOLATION LEVEL REPEATABLE READ;	—	A will read from one frozen snapshot
   2	SELECT balance_paise FROM wallet WHERE friend_id = 1;	—	50000 — the snapshot is taken now
   3	—	UPDATE wallet SET balance_paise = 77000 WHERE friend_id = 1;	B commits a change
   4	SELECT balance_paise FROM wallet WHERE friend_id = 1;	—	Still 50000. B’s commit is invisible to A’s snapshot
   5	COMMIT; then re-run the SELECT	—	Now 77000 — the snapshot ended with the transaction

4. Experiment 3a — the check-then-debit race, unlocked: lose ₹300. Reset: UPDATE wallet SET balance_paise = 50000 WHERE friend_id = 1; Both terminals play “pay out ₹400 if the balance covers it”:

   #	Terminal A types	Terminal B types	What happens
   1	BEGIN;	—	Payout A starts
   2	SELECT balance_paise FROM wallet WHERE friend_id = 1;	—	50000 — enough, A decides to pay
   3	—	BEGIN;	Payout B starts
   4	—	SELECT balance_paise FROM wallet WHERE friend_id = 1;	50000 — B also sees enough. No lock was taken; nothing stopped this
   5	UPDATE wallet SET balance_paise = 10000 WHERE friend_id = 1;	—	A writes 50000 − 40000
   6	COMMIT;	—	A’s payout is final
   7	—	UPDATE wallet SET balance_paise = 10000 WHERE friend_id = 1;	B writes ITS computed 50000 − 40000
   8	—	COMMIT;	Final balance 10000. Paid out: 80000. ₹300 is gone and no error fired

5. Experiment 3b — same race, FOR UPDATE: correctness. Reset: UPDATE wallet SET balance_paise = 50000 WHERE friend_id = 1;

   #	Terminal A types	Terminal B types	What happens
   1	BEGIN;	BEGIN;	Both payouts start
   2	SELECT balance_paise FROM wallet WHERE friend_id = 1 FOR UPDATE;	—	50000, and A now holds the row lock
   3	—	SELECT balance_paise FROM wallet WHERE friend_id = 1 FOR UPDATE;	B hangs. The cursor just sits there — it’s waiting for A’s lock. This is the design working
   4	UPDATE wallet SET balance_paise = 10000 WHERE friend_id = 1;	(still waiting)	A debits
   5	COMMIT;	(B instantly returns 10000)	Lock released; B finally reads — and reads the truth
   6	—	ROLLBACK;	10000 < 40000: B’s check fails, B refuses the payout. One payout, zero invented rupees

6. Experiment 4 — manufacture a deadlock. Reset both wallets: UPDATE wallet SET balance_paise = 50000 WHERE friend_id IN (1, 2);

   #	Terminal A types	Terminal B types	What happens
   1	BEGIN;	BEGIN;	Two settlements begin
   2	UPDATE wallet SET balance_paise = balance_paise - 1000 WHERE friend_id = 1;	—	A locks row 1
   3	—	UPDATE wallet SET balance_paise = balance_paise - 1000 WHERE friend_id = 2;	B locks row 2
   4	UPDATE wallet SET balance_paise = balance_paise + 1000 WHERE friend_id = 2;	—	A wants row 2 — blocks behind B
   5	—	UPDATE wallet SET balance_paise = balance_paise + 1000 WHERE friend_id = 1;	B wants row 1 — cycle complete. Within ~1 second one terminal prints ERROR: deadlock detected
   6	COMMIT; (if A survived)	ROLLBACK; (the victim)	Read the DETAIL lines: two processes, each blocked by the other’s transaction

   Now say the fix out loud: both transactions touch rows 1 and 2 — if both had locked them in ascending friend_id order, step 5 could never form a cycle. Re-run the choreography with B doing row 1 first and watch it merely wait instead of die.

7. Final check — the books, after all experiments:

SELECT f.name, w.balance_paise FROM wallet w JOIN friend f ON f.id = w.friend_id ORDER BY w.friend_id;

  name   | balance_paise
---------+---------------
 Asha    |         10000
 Rohit   |         50000
 Darshan |         50000
 Meera   |         50000
(4 rows)

Asha’s 10000 is from experiment 3b — the payout that went through because the lock made the second one fail honestly.

8. Break it on purpose — the forgotten COMMIT. In A: BEGIN; UPDATE wallet SET balance_paise = 0 WHERE friend_id = 2; — and then just leave it. In B: UPDATE wallet SET balance_paise = 1 WHERE friend_id = 2; — B hangs, indefinitely. No error, no timeout by default. This is the most common “the database is frozen” ticket in real life: an open transaction holding a lock while its owner is at lunch. Fix: ROLLBACK; in A, watch B instantly complete, then reset Rohit to 50000.

Where to Practice

Check Yourself

1. What does ROLLBACK do to statements already executed inside the transaction, and who runs it if the app crashes instead?
2. What exactly has happened on disk when COMMIT returns — and what may not have happened yet?
3. Why is a dirty read impossible in PostgreSQL even at its lowest isolation level?
4. Define a non-repeatable read in one sentence, and name the cheapest level that prevents it.
5. What new failure mode do you accept when you move to REPEATABLE READ or SERIALIZABLE, and what must your code do about it?
6. In the check-then-debit race, why does raising the isolation level alone not fix it the way FOR UPDATE does?
7. Walk the FOR UPDATE choreography: what does the second transaction experience, and why is what it eventually reads the key to correctness?
8. What causes a deadlock, and what is the one-sentence permanent fix?

Explain it out loud: Narrate the ₹300 disappearance to an empty chair — both transactions, the two reads of 50000, why no error fired, and then replay it with FOR UPDATE: who blocks, when the lock releases, what the blocked transaction reads, and why it refuses to pay. If you can’t perform both versions from memory, re-run experiments 3a and 3b — that choreography is the most interview-valuable thing in this entire track.

Still Unclear?

I reproduced a non-repeatable read in PostgreSQL at READ COMMITTED using two
psql sessions. Explain what MVCC snapshots each of my statements used and
exactly when each snapshot was taken. Then explain how BEGIN ISOLATION LEVEL
REPEATABLE READ changes the snapshot timing. Use a timeline of my two
sessions, not generic definitions.

Compare SELECT FOR UPDATE (pessimistic) with an optimistic version column
for the wallet debit problem: when does each win, what does each cost under
high contention, and what does Spring Data JPA offer for both? Then give me
three scenarios and make me choose, challenging weak reasoning.

My settlement job and my refund job both update multiple wallet rows and I
am seeing deadlock detected errors in production logs. Interview me about
both jobs lock acquisition order, help me find the inversion, and then make
me design the ordering convention and the 40001 retry wrapper. Do not write
the code until I have stated the invariant correctly.

Why AI Can’t Do This For You

AI will write you a textbook-perfect explanation of ACID and even a correct FOR UPDATE query. What it cannot do is suspect the race in the first place — read an innocent-looking service method that checks a balance in Java and writes it back, and feel the gap between the read and the write where two threads slip through together. That suspicion comes only from having watched the ₹300 vanish with your own eyes in two terminals, which is precisely what you did today.

And when production throws deadlock detected at month-end, the AI sees neither your logs nor the lock order of the two jobs that collided. The engineer who has manufactured a deadlock on purpose reads the DETAIL lines like a map, asks the one right question — “who locks what, in what order?” — and closes the incident. Concurrency judgment cannot be generated on demand; it is built from witnessed races, and you now have four of them in your hands.

Module done? Add it to today’s tracker