Learn · SQL · Core

SQL Transactions

A transaction groups several changes so they all succeed or all fail — never half. It’s the reason money doesn’t vanish between two accounts. This is your world.

The idea — a bank transfer

Move ₹500 from A to B. That’s two steps: subtract from A, then add to B. If the system crashes between them, ₹500 vanished. A transaction wraps both into one indivisible unit — both, or neither. Watch it:

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
COMMIT;                 -- both saved together, or ROLLBACK undoes both

ACID — in plain words

The money race — why isolation matters

Two withdrawals hit the same ₹1000 account at the same instant, with no locking:

Bug: two ₹800 withdrawals happened on a ₹1000 account, and the balance says ₹200. ₹600 vanished.

The fix — locking: SELECT ... FOR UPDATE makes Thread B wait until A commits, so B reads the fresh ₹200 and correctly rejects the second withdrawal. This exact race is a top payments interview question.

What COMMIT actually promises

When COMMIT returns to you, PostgreSQL has already written your changes to the write-ahead log (WAL) and forced that log to physical disk — before it answers you. The real table pages may get updated lazily, minutes later. If the machine loses power first, PostgreSQL replays the WAL on startup and rebuilds every committed change. That’s durability: COMMIT returning means your payment survives a power cut a millisecond later.

It’s also why COMMIT has real latency — it’s a disk flush. “The database is slow” sometimes just means “the disk under the WAL is slow.”

Autocommit: every plain statement you run without BEGIN is silently its own tiny transaction — begin, run, commit, in one breath. Typing BEGIN is you taking manual control of where the boundary sits.

The three concurrency anomalies — they have names

When isolation is weak, one transaction can see another’s half-done work. Each way this happens has a name, and interviewers love these:

The dirty-read row is worth a pause: many databases historically allowed it at their lowest level. PostgreSQL’s design (every read comes from a committed snapshot) makes it flat-out impossible. That single fact is a strong interview signal.

Isolation levels — honestly

Atomicity, Consistency and Durability are mostly automatic. Isolation is the one you choose — it comes in levels, and each level has a price:

Two honest footnotes textbooks fudge. First: the SQL standard permits phantoms at REPEATABLE READ, but PostgreSQL blocks them anyway — it’s stronger than the standard here. Second: stronger levels don’t make bugs vanish, they turn silent wrong answers into loud serialization_failure errors 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;

The lock in full — FOR UPDATE

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

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

Now the second transaction’s SELECT ... FOR UPDATE just hangs — it waits for the first to commit. Then it reads the post-debit truth, its sufficiency check fails honestly, and it refuses the payout. One payout succeeds, one is refused, zero rupees invented. This is the standard tool for every read-then-write money flow.

Deadlocks — produced, read, fixed

Locks add one new failure mode. Transaction A locks row 1 and wants row 2; transaction B locked row 2 and wants row 1. Both would 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.

Read it like a stack trace: two processes, each waiting on the other’s transaction — a cycle. The victim is rolled back (atomicity working as designed — just retry it); the survivor proceeds. The fix is not “fewer locks,” it’s lock order: if every transaction touching multiple rows locks them in the same order — say ascending id — a cycle is geometrically impossible. Learn this one sentence 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, 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. When a Spring service does read-check-write on money, the code-review question is “what stops two threads passing the check together?” — and now you can answer it at the SQL layer, where the answer actually executes.

Interview-ready — say these from memory

Where you’ll use it — real life

Now YOU do the reps

Next: OS Fundamentals →