SQL traps · Chapter 13 of 16

A deadlock from lock order

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: the deadlock, on purpose

    Write repro.sql. It runs in a fresh, empty database where bill_split.sql has just made the tables wallets and batches and the function settle(name, ids, share). It plays sessions as in case 11, and now a third one, session c, holds a wallet with SELECT ... FOR UPDATE inside a transaction, to stop a batch halfway. Add wallets 1, 2 and 3 with 1000 rupees each. In session c, begin a transaction and lock wallet 3. In session a, run settle('dinner', ARRAY[1, 3, 2], 300): it locks wallet 1 and waits for 3. In session b, run settle('cab', ARRAY[2, 1], 150): it locks wallet 2 and waits for 1. Then commit session c, so a and b wait for each other. End with a step in session a, then a last step in session b with a SELECT that gives one value: how many rows batches has.

    The check runs your file twice, each time in a fresh database: beside the original bill_split.sql it must give 1 (Postgres aborted one of the batches with deadlock detected), and beside a fixed bill_split.sql it must give 2. So the batches really have to cross: a file that only says SELECT 1 fails the second run. Whichever batch Postgres aborts, the count is 1. Run it with node run.mjs in the terminal.

  2. Task 2

    bill_split.sql: every batch settles

    Fix settle in bill_split.sql so batches that share wallets never deadlock. settle(name, ids, share) still means: the first wallet in ids paid the bill, it gets share for every other wallet, and every other wallet pays share. A settled batch adds one row to batches. When two batches share wallets in opposite orders, and when another session holds one of their wallets for a while, both batches wait as needed and then settle, with no error, and the balances afterwards are exact. That includes two batches whose payers are friends in each other's batch.

    Keep the two tables as they are, and keep settle's name and arguments. The check runs its own sessions and wallets, and locking the payer first and only then the friends in order still deadlocks.

  3. Task 3

    bill_split.test.sql: a test that would have caught it

    Write a SQL test file, bill_split.test.sql, that would have caught this bug before the nightly job met it. Use the lab's ok(condition, name) and is(got, want, name): each call is one test, and an error in any step (a deadlock) is a failed test. The check runs your file three times, each in a fresh database where a version of bill_split.sql has just made the tables: beside the original bill_split.sql at least one test must fail; beside the fixed bill_split.sql every test must pass and nothing may raise an error; beside a half fix that locks the payer's wallet first and then sorts the others at least one test must fail.

    So cross two batches with three sessions, as in your repro, choosing them so each batch's payer is a friend in the other batch (for example one paid by wallet 1 that includes wallet 3, and one paid by wallet 3 that includes wallet 1), then test that both batches settled and that the balances are right. Run it with node run.mjs bill_split.test.sql.

What you will learn

  • repro.sql: the deadlock, on purpose
  • bill_split.sql: every batch settles
  • bill_split.test.sql: a test that would have caught it

More in Time

Build "A deadlock from lock order" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.