Locks and Deadlocks

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Locks and Deadlocks
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

PostgreSQL takes implicit locks to preserve consistency. `SELECT ... FOR UPDATE` can protect a read-then-write decision, but lock duration should remain short. If two transactions lock resources in opposite order, a deadlock can form; PostgreSQL aborts one participant to break the cycle.

Connect it to a real scenario

For seat inventory, lock the row before decrementing availability. Across the application, acquire rows in a consistent order such as ascending ID, set sensible timeouts, and retry aborted transactions.

Consider row locks for read-then-write decisions
Acquire locks in a consistent order
Retry deadlock victims

Try the working example

sql

BEGIN;
SET LOCAL lock_timeout = '2s';

SELECT seats_left
FROM app.course_sessions
WHERE session_id = 7
FOR UPDATE;

UPDATE app.course_sessions
SET seats_left = seats_left - 1
WHERE session_id = 7 AND seats_left > 0
RETURNING seats_left;

COMMIT;

You should see

Concurrent bookings serialize the update and avoid negative inventory.

5-minute try-it

In two terminals, lock two rows in opposite order to trigger and observe a deadlock.

One important caution

Treating a lock wait as a generic application hang without locating the blocker prolongs incidents.

PostgreSQL — Explicit Locking — PostgreSQL Global Development Group

Easy traps

Treating a lock wait as a generic application hang without locating the blocker prolongs incidents.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

In two terminals, lock two rows in opposite order to trigger and observe a deadlock.

You'll know it worked when: Concurrent bookings serialize the update and avoid negative inventory.