Transactions Advanced

By the end of this lesson you'll be able to reason about what happens when two users hit the same data at once — choosing the right isolation level for each job, predicting dirty, non-repeatable and phantom reads, picking optimistic vs pessimistic locking, recovering part of a transaction with savepoints, and explaining why deadlocks happen.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Most examples move money between these two bank accounts . A version column is included for the optimistic-locking section. Keep the starting balances in mind as you read.

A transaction wraps several statements so they either all take effect or none do. You open one with BEGIN , make it permanent with COMMIT , or throw it all away with ROLLBACK . This is the "A" (atomicity) in ACID — the four guarantees (Atomicity, Consistency, Isolation, Durability) a database makes about your data.

A transaction is like a bank transfer at the counter. Debiting Alice and crediting Bob must happen together . If the till jams after the debit, the clerk tears up the slip ( ROLLBACK ) so money never vanishes into thin air. Only when both halves are done does the clerk stamp it ( COMMIT ).

The "I" in ACID is isolation : how much one running transaction is shielded from the changes of others happening at the same time. SQL defines four levels. Turn the dial up and you prevent more anomalies but pay with more waiting, blocking, and retries; turn it down and you go faster but expose yourself to weirder results.

Think of it like noise-cancelling headphones. SERIALIZABLE is full cancellation — total quiet, but it drains the battery. READ UNCOMMITTED is headphones off — zero overhead, but every distraction leaks in.

An anomaly is a surprising result you can only get when transactions overlap. There are three classic ones, and each isolation level is defined by which it forbids:

The weakest level imposes almost no isolation: you can see changes other transactions have made but not yet committed. If they roll back, you acted on a value that was never real — a dirty read .

Now you only ever see committed data, so dirty reads are gone. But every statement takes a fresh look at the database, so reading the same row twice can still return different values: a non-repeatable read . This is PostgreSQL's default and the right starting point for most apps.

Here your transaction takes one consistent snapshot and reuses it: any row you read keeps the same value all the way to COMMIT , killing non-repeatable reads. The SQL standard still permits phantom rows at this level — though PostgreSQL's implementation happens to block those too.

The strongest level guarantees the outcome is identical to running the transactions one at a time in some order — so it blocks all three anomalies, phantoms included. The price: when the database can't preserve that illusion, it aborts a transaction with a serialization failure , and your application must catch that error and retry. Reach for it only when correctness is non-negotiable (financial postings, inventory that must never oversell).

The clearest way to see an anomaly is to lay two sessions side by side and read top to bottom — each row is a moment in time. Here is a non-repeatable read under READ COMMITTED: Session A reads the same row before and after Session B commits a change.

At t6 Session A re-reads and gets a different number than at t2 — the non-repeatable read. Run the very same timeline under REPEATABLE READ and t6 would still return 1000 , because A's snapshot was frozen at t2.

Reaching for READ UNCOMMITTED "for speed." The performance gain is negligible on modern engines, and you risk acting on rolled-back data. Start at READ COMMITTED and only raise the level when a real anomaly bites.

Read the scenario in the comments and fill in the blank with the isolation level that fits. The expected answer (and the reasoning) is in the comments so you can check yourself.

How does a database give each transaction its own snapshot? Two strategies exist. The old approach is locking : a reader takes a shared lock so writers must wait, which is correct but slow under load. The modern approach — used by PostgreSQL and MySQL's InnoDB — is MVCC (Multi-Version Concurrency Control) : keep multiple versions of each row, and show each transaction the version that was current when its snapshot began.

The headline benefit of MVCC is that readers never block writers and writers never block readers . The catch is that superseded row versions ("dead tuples") accumulate and must be cleaned up by VACUUM (PostgreSQL's autovacuum usually handles this for you).

When two transactions might fight over the same row, you choose a strategy for handling the clash. The choice comes down to a single question: how often do you actually expect a conflict?

A savepoint is a named bookmark inside an open transaction. ROLLBACK TO savepoint_name rewinds the work done after that bookmark while keeping everything before it — and crucially, the transaction stays open. It is an "undo" button for one step of a larger operation. RELEASE SAVEPOINT simply forgets a bookmark you no longer need.

Two blanks: create a savepoint, then roll back to it so the bad fee is undone but the payment survives.

A deadlock happens when two transactions each hold a lock the other one needs, so neither can move. The database detects the cycle and breaks it by aborting one transaction (the "victim") with a deadlock detected error; your app should catch it and retry. Read this timeline top to bottom:

* Per the SQL standard. PostgreSQL also prevents phantom reads at REPEATABLE READ. "Possible" means the level allows the anomaly; "Prevented" means it forbids it.

Q: Which isolation level should I use by default?

READ COMMITTED (PostgreSQL's default) is the right starting point for most applications. Only raise it to REPEATABLE READ or SERIALIZABLE when a specific anomaly would corrupt your logic — and be ready to retry on serialization failures.

Q: What's the difference between SERIALIZABLE and just locking everything?

Locking forces transactions to wait. SERIALIZABLE under MVCC lets them run concurrently and only aborts one if the result wouldn't match some serial order. You usually get more throughput, at the cost of having to retry the aborted transaction.

Q: Optimistic or pessimistic — how do I choose?

Estimate how often two writers hit the same row. Rare clashes favour optimistic (a cheap version check, retry on the odd miss). Frequent clashes favour pessimistic ( FOR UPDATE ), so you wait once instead of retrying repeatedly.

Q: Does ROLLBACK TO a savepoint end my transaction?

No. ROLLBACK TO only undoes work done after the savepoint; the transaction stays open and you keep going. A bare ROLLBACK (no TO ) is what discards the entire transaction.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a Postgres playground to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary