Sign in Get started

Free course · 20 lessons · Intermediate

Transactions, Locking & Concurrency in Code

Real production bugs and their fixes: transaction boundaries, framework pitfalls, long transactions, pool exhaustion, isolation levels, lost updates, write skew, SELECT … FOR UPDATE, SKIP LOCKED job queues, optimistic locking, constraints and upserts, advisory locks, deadlocks, gap locks, migration lock queues and hot rows — with runnable simulations and code for Postgres, MySQL, Spring, Django/SQLAlchemy, Laravel and Prisma.

Start the course

1. Transactions in Application Code

How transactions actually start, commit and leak in real application code — and what that costs under load.

  1. Transaction Boundaries and Autocommit — Why a signup that writes three rows can leave half an account behind, and how to draw transaction boundaries in every major framework.
  2. Framework Transaction Pitfalls — The @Transactional, atomic() and $transaction mistakes that silently commit what should have rolled back.
  3. Long Transactions and Calling APIs Inside Them — Why a checkout that calls a payment provider inside a transaction can freeze every other checkout.
  4. Connection Pools and Transactions — How pool exhaustion really happens, how to size a pool, and what PgBouncer's transaction mode breaks.

2. Isolation Levels and Anomalies

What concurrent transactions can see, the bugs each isolation level allows, and how to fix them in code.

  1. Isolation Levels in Postgres vs MySQL — Read Committed vs Repeatable Read in practice: why the same report gives different answers on different databases.
  2. Lost Updates: The Read-Modify-Write Bug — Two buyers, one item left, two orders confirmed — and four correct ways to fix it.
  3. Write Skew and Phantoms — When two transactions each check a rule, both pass, and together break it: on-call rosters and double-booked rooms.
  4. SERIALIZABLE in Practice: Retrying Serialization Failures — Using Postgres SERIALIZABLE safely: which errors to retry, how to retry the whole transaction, and what it costs.

3. Locking Strategies

Pessimistic locks, job queues on the database, optimistic versions, constraints and advisory locks — with the trade-offs of each.

  1. Pessimistic Locking with SELECT … FOR UPDATE — Lock the rows you are about to change: seat booking, NOWAIT for instant feedback, and how lock scope differs between Postgres and MySQL.
  2. Job Queues on the Database with SKIP LOCKED — How to let many workers pull jobs from one table without processing a job twice — and when SKIP LOCKED is the wrong tool.
  3. Optimistic Locking with Version Columns — Detect conflicting edits instead of blocking: version columns, JPA @Version and HTTP If-Match.
  4. Constraints and Upserts as Concurrency Control — Let the database enforce uniqueness: check-then-insert races, unique indexes, ON CONFLICT and ON DUPLICATE KEY.
  5. Advisory Locks for Run-Once Jobs — Stop a nightly billing job from running on three servers at once, with Postgres advisory locks and MySQL GET_LOCK.

4. Deadlocks and Contention

Why deadlocks happen, how to read the database's deadlock report, MySQL gap-lock surprises, lock queues during migrations, and hot rows.

  1. How Deadlocks Happen (and Lock Ordering) — Two transfers in opposite directions lock each other out; the database kills one. Fix it with a consistent lock order.
  2. Reading Deadlock Reports — Turn Postgres logs and SHOW ENGINE INNODB STATUS into the two code paths that collided.
  3. MySQL Gap Locks and Insert Deadlocks — Why 'lock it, then insert it if missing' deadlocks under InnoDB Repeatable Read, and the patterns that don't.
  4. Lock Queues, Timeouts and Safe Migrations — How a one-second ALTER TABLE took the site down — and how lock_timeout prevents it.
  5. Hot Rows and High-Traffic Counters — When thousands of requests update one row: likes counters, global sequences and balances — and how to spread the load.

5. Capstone Case Studies

Apply everything to two realistic incidents: a flash sale that must never oversell, and a deadlock storm after a deploy.

  1. Capstone: Never Oversell a Flash Sale — Design inventory reservation for a limited-edition drop: 5,000 units, 50,000 buyers in the first minute.
  2. Capstone: Postmortem of a Deadlock Storm — Investigate a real-looking incident from logs and code, find the root cause, and write the fix and the follow-ups.