The Desi Architect
Notes / Docker Sandboxes / SQL Transaction Isolation Levels & Concurrency Anomalies
Docker Sandboxes · Tier 0: Start Here · Lab 05

SQL Transaction Isolation Levels & Concurrency Anomalies

Two sessions spend the same Rs 1000 wallet at the same moment, and the database lets both. Make that happen, then fix it three ways. Along the way you meet dirty reads, non-repeatable reads, phantoms, lost updates and write skew, each one run for real.

Time40 minutes
StackPostgreSQL 16Ports 15430
MemoryAbout 60 MB
Before thisLab 04SQL Basics with PostgreSQL
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Say what a transaction is, and what an isolation level decides.
  • Show a dirty read, a non-repeatable read and a phantom read, and say which isolation level stops each in PostgreSQL.
  • Reproduce a lost update and fix it with one UPDATE ... WHERE statement or with SELECT ... FOR UPDATE.
  • Reproduce write skew, and see SERIALIZABLE stop it with error 40001.
  • Explain why code that uses SERIALIZABLE must be ready to retry.

Before you start

Finish Lab 04 (SQL Basics) first. You should be able to read SELECT, UPDATE ... WHERE, and know what BEGIN and COMMIT mean. Docker Desktop must be running.

Lab-only password

The password in this lab is lab_only_password. It exists only on your laptop, for practice. A real database never gets a password like that.