Connections, Timeouts, and Deadlocks

A connection sits idle inside an open transaction. A query runs forever. Two sessions lock each other out permanently. Three real problems, three real safety timeouts.

A connection that opens a transaction and then goes idle — an application that crashed, a developer who stepped away mid-debugging — holds whatever locks that transaction acquired for as long as the connection stays open, blocking anything else that needs them. idle_in_transaction_session_timeout terminates a connection that has been idle inside an open transaction for too long, genuinely closing it rather than leaving it to block the rest of the system indefinitely. statement_timeout and lock_timeout apply the same idea to two narrower situations: a single query running too long, or a single statement waiting too long for a lock someone else is holding.\n\nA deadlock is a different, structural problem no timeout alone prevents: two sessions each hold a lock the other one needs, and neither can proceed — without detection, they would simply wait forever. deadlock_timeout controls how long PostgreSQL waits before actively checking for this specific situation; once found, it always resolves a deadlock by forcibly rolling back one of the two transactions as the "victim," letting the other proceed. This lab reproduces all four scenarios for real — including using psql's own \! shell-escape to create genuine wall-clock idle time inside a real open transaction, since a script sending commands back-to-back would never trigger an idle timeout at all.

idle_in_transaction_session_timeout: Real Idle Time, Genuinely Terminated

SET idle_in_transaction_session_timeout = '5s';
BEGIN;
\! sleep 7   -- psql's own shell-escape creates real wall-clock idle time
SELECT 1;

FATAL:  terminating connection due to idle-in-transaction timeout
server closed the connection unexpectedly

A real 7-second idle gap, created with psql's own \! shell-escape rather than a script sending commands back-to-back (which would never actually go idle) — and the connection is genuinely terminated by the server itself.

statement_timeout: A Real Long-Running Query, Genuinely Cancelled

SET statement_timeout = '1s';
SELECT pg_sleep(10);

ERROR:  canceling statement due to statement timeout

lock_timeout: Real Contention, Genuinely Cancelled

-- Session A holds a real lock for 5 real seconds:
BEGIN; LOCK TABLE sql_lock_demo IN ACCESS EXCLUSIVE MODE; \! sleep 5; COMMIT;

-- Session B, started while A still holds the lock:
SET lock_timeout = '2s';
SELECT * FROM sql_lock_demo WHERE id = 1;

ERROR:  canceling statement due to lock timeout

Session B genuinely waited on the real lock Session A was holding, and was genuinely cancelled after 2 seconds — well before Session A's 5-second hold even finished.

A Real Deadlock, Detected and Reported

-- Session A: UPDATE table_a, then (after a delay) UPDATE table_b
-- Session B, concurrently: UPDATE table_b, then UPDATE table_a  (opposite order)

ERROR:  deadlock detected
DETAIL:  Process 138 waits for ShareLock on transaction 897; blocked by process 141.
Process 141 waits for ShareLock on transaction 896; blocked by process 138.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "sql_deadlock_b"
ROLLBACK

Two real sessions, real process IDs, a genuine circular wait — PostgreSQL detected it and rolled back one transaction (the "victim") automatically, letting the other one commit successfully.

idle_in_transaction_session_timeout

Terminates a database session that has an open transaction but is not currently running any query, once that idle period exceeds the configured duration. This is different from statement_timeout, which only bounds an actively-running query — idle_in_transaction_session_timeout specifically catches a transaction that was opened and then simply abandoned, holding its locks with nothing happening at all.

deadlock_timeout

How long PostgreSQL waits while a session appears blocked on a lock before actively running its (relatively expensive) deadlock detection check. A short deadlock_timeout catches real deadlocks faster but spends more CPU checking for them on every ordinary lock wait; a longer one is cheaper per-wait but leaves a genuine deadlock uncaught for longer. It defaults to 1 second, rarely needing to change.

⏳ Terminate a Real Idle Transaction

Set a short idle_in_transaction timeout, open a transaction, and go genuinely idle longer than the limit using psql's own shell-escape.

psql -U postgres -d beer_db -c "SET idle_in_transaction_session_timeout = '5s';" -c "BEGIN;" -c "\\! sleep 7" -c "SELECT 1;"

student@lab:~$ psql -U postgres -d beer_db -c "SET idle_in_transaction_session_timeout = '5s';" -c "BEGIN;" -c "\\! sleep 7" -c "SELECT 1;" SET SET BEGIN FATAL: terminating connection due to idle-in-transaction timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost

⏱️ Cancel a Real Long-Running Query

Set a short statement_timeout and run a query that would otherwise take far longer.

psql -U postgres -d beer_db -c "SET statement_timeout = '1s';" -c "SELECT pg_sleep(10);"

student@lab:~$ psql -U postgres -d beer_db -c "SET statement_timeout = '1s';" -c "SELECT pg_sleep(10);" SET SET ERROR: canceling statement due to statement timeout

🔐 Cancel a Session Genuinely Waiting on a Real Lock

Have one session hold a real lock, then confirm a second session with lock_timeout set is genuinely cancelled while waiting for it.

psql -U postgres -d beer_db -c "CREATE TABLE sql_lock_demo (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_lock_demo VALUES (1,10);"

(psql -U postgres -d beer_db -c "BEGIN;" -c "LOCK TABLE sql_lock_demo IN ACCESS EXCLUSIVE MODE;" -c "\\! sleep 5" -c "COMMIT;" > /tmp/session_holder.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SET lock_timeout = '2s';" -c "SELECT * FROM sql_lock_demo WHERE id = 1;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_lock_demo (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_lock_demo VALUES (1,10);" SET CREATE TABLE INSERT 0 1 student@lab:~$ (psql -U postgres -d beer_db -c "BEGIN;" -c "LOCK TABLE sql_lock_demo IN ACCESS EXCLUSIVE MODE;" -c "\\! sleep 5" -c "COMMIT;" > /tmp/session_holder.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SET lock_timeout = '2s';" -c "SELECT * FROM sql_lock_demo WHERE id = 1;" SET SET ERROR: canceling statement due to lock timeout LINE 1: SELECT * FROM sql_lock_demo WHERE id = 1; ^

💥 Reproduce a Genuine Deadlock Between Two Sessions

Have two sessions update the same two tables in opposite order, and read PostgreSQL's real deadlock detection error.

psql -U postgres -d beer_db -c "CREATE TABLE sql_deadlock_a (id int PRIMARY KEY, val int);" -c "CREATE TABLE sql_deadlock_b (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_deadlock_a VALUES (1,10);" -c "INSERT INTO sql_deadlock_b VALUES (1,10);"

(psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_deadlock_a SET val = 20 WHERE id = 1;" -c "\\! sleep 2" -c "UPDATE sql_deadlock_b SET val = 20 WHERE id = 1;" -c "COMMIT;" > /tmp/session_a.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_deadlock_b SET val = 30 WHERE id = 1;" -c "UPDATE sql_deadlock_a SET val = 30 WHERE id = 1;" -c "COMMIT;"

sleep 3; cat /tmp/session_a.txt

student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_deadlock_a (id int PRIMARY KEY, val int);" -c "CREATE TABLE sql_deadlock_b (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_deadlock_a VALUES (1,10);" -c "INSERT INTO sql_deadlock_b VALUES (1,10);" SET CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 student@lab:~$ (psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_deadlock_a SET val = 20 WHERE id = 1;" -c "\\! sleep 2" -c "UPDATE sql_deadlock_b SET val = 20 WHERE id = 1;" -c "COMMIT;" > /tmp/session_a.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_deadlock_b SET val = 30 WHERE id = 1;" -c "UPDATE sql_deadlock_a SET val = 30 WHERE id = 1;" -c "COMMIT;" SET BEGIN UPDATE 1 UPDATE 1 COMMIT student@lab:~$ sleep 3; cat /tmp/session_a.txt SET BEGIN UPDATE 1 ERROR: deadlock detected DETAIL: Process 138 waits for ShareLock on transaction 897; blocked by process 141. Process 141 waits for ShareLock on transaction 896; blocked by process 138. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "sql_deadlock_b" ROLLBACK

Lab 3.5.4 complete — Block 3.5 complete. Timeouts and deadlocks, every one genuinely reproduced:\n\n\n idle_in_transaction_session_timeout : ✅ real termination, real idle time\n statement_timeout : ✅ real query, genuinely cancelled\n lock_timeout : ✅ real contention, genuinely cancelled\n Deadlock detection : ✅ genuine circular wait, real error\n

Enable JavaScript to run the live terminal and track your progress.