What is the difference between lock_timeout and statement_timeout?
lock_timeout cancels a statement that waits too long to acquire a lock. statement_timeout cancels any statement whose total running time exceeds the limit, including time spent working.
Explain it simply
At a ration shop you might decide "I will wait at most 10 minutes in the queue" (lock_timeout), and separately "the whole visit, queue and shopping, must not exceed 30 minutes" (statement_timeout). One limits waiting for something to become free, the other limits everything.
Both are off (0) by default, which means a stuck query can run forever.
Example
SET lock_timeout = '2s';
SET statement_timeout = '30s';
UPDATE orders SET status = 'x' WHERE id = 1;
-- ERROR: canceling statement due to lock timeout
-- (someone else holds the row lock for more than 2 s)
SELECT pg_sleep(60);
-- ERROR: canceling statement due to statement timeout
-- Set for a role so every app connection gets it
ALTER ROLE web_app SET statement_timeout = '15s';Also useful: idle_in_transaction_session_timeout to drop sessions that open a transaction and then go quiet. Reporting users can have a longer statement_timeout than the web app.
What to say in the interview
"lock_timeout bounds the wait for a lock, statement_timeout bounds the total runtime. I set sensible values per role, short for the web app, and a lock_timeout in migrations so they fail fast instead of blocking traffic."
Common mistakes
- Leaving both at zero on an application role.