Postgres in depth · Chapter 52 of 65
Who is blocking whom
By the end of this chapter you can see every lock in the server, find which session is blocking which, explain why one ALTER TABLE can freeze a whole table, and write migrations that give up instead of queueing. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
At 11 am the course catalogue stopped loading. No error, no crash: every page just waited. Sana had a support ticket per minute. The cause was a one-line migration, ALTER TABLE courses ADD COLUMN subtitle text, which takes a millisecond on its own. Priya wants to know how a millisecond of work froze the site for ten minutes, and how to see it happening. Chapter 39 locked rows for seat bookings; this chapter is about the locks on whole tables that every statement takes, and the queue they form.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
my-locks.sql: the locks a read takes
Inside one transaction, read the number of courses, then list the table locks your own session holds on courses and its indexes: each relation's name (as text), the lock mode, and whether it was granted.
Return three columns: relation, mode and granted. Any order.
Careful: locks last until the transaction ends, so run both queries in one transaction; keep only relation locks whose name starts with courses.
- Task 2
blockers.sql: who is waiting for whom?
Sana says the course editor is frozen. In the Check's copy, one session has updated the React from Zero course in an open transaction, and a second session waits to update the same row. List every session of this database that waits for another: its pid, the pid of each session it waits for, and the statement it is stuck on.
Return three columns: waiting_pid, blocking_pid and waiting_query, one row per waiting and blocking pair. Any order.
- Task 3
lock-timeout.sql: a migration that gives up
Priya's migration adds a nullable text column trailer_url to courses. If a long transaction is reading courses, the ALTER TABLE waits, and every new query on courses queues behind it. Make the migration give up if it cannot get its lock within 5 seconds at most, so it can be retried.
Afterwards: with a reader in the way, the file fails with a lock timeout; with nobody in the way, courses has the trailer_url column.
Careful: the setting limits the wait for a lock, not the whole statement; set it for this session only.
- Task 4 · interview question
Interview: long-transactions.sql, the open transactions
"Vacuum is stuck and a migration is waiting: what do you check first?" The transactions that are open. List the other sessions of this database that are inside a transaction: their pid, state and wait_event_type, the oldest transaction first.
Return three columns: pid, state and wait_event_type, ordered by when each transaction started.
Careful: leave out your own session (it is in a transaction too) and other databases.
What you will learn
- Table lock modes: which statements take which lock, and which conflict.
- pg_locks: every lock held or awaited, live.
- pg_blocking_pids and pg_stat_activity: who waits for whom.
- The lock queue: why a waiting lock blocks the ones behind it.
- lock_timeout and long transactions: safe migrations.
More in Inside Postgres
Build "Who is blocking whom" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.