January 30, 2024
How long to wait until the lock is acquired before cancelling the statement.
Note: Emphasis on the fact that the lock_timeout
setting doesn't affect how
long the lock is held, only how long the transaction is allowed wait in order
to acquire it.
Note 2: The timeout counter starts when the transaction joins the queue of locks. So if there are 3 transactions waiting to acquire the same lock, the 4th one wouldn't wait until all 3 transactions are completed before the lock timeout counter starts.
// sets the lock for the whole session
SET lock_timeout = '1s';
// sets the lock for the current transaction
SET LOCAL lock_timeout = 1000;
ALTER TABLE accounts DROP COLUMN amount;
// ERROR: canceling statement due to lock timeout
Basics: SET lock_timeout 100ms
sets the timeout for the whole session,
whereas SET LOCAL lock_timeout 100ms
sets the timeout for the current
transactions.
If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another SET.
The effects of SET LOCAL last only till the end of the current transaction, whether committed or not. A special case is SET followed by SET LOCAL within a single transaction: the SET LOCAL value will be seen until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.
The effects of SET or SET LOCAL are also cancelled by rolling back to a savepoint that is earlier than the command.
Source: https://www.postgresql.org/docs/16/runtime-config-client.html
statement_timeout
Note: If lock_timeout
is set to the same value of statement_timeout
, the
lock_timeout
value becomes redundant, as statement_timeout
trumps the lock.
Unlike
statement_timeout
, this timeout can only occur while waiting for locks. Note that ifstatement_timeout
is nonzero, it is rather pointless to setlock_timeout
to the same or larger value, since the statement timeout would always trigger first. Iflog_min_error_statement
is set to ERROR or lower, the statement that timed out will be logged.
Source: https://www.postgresql.org/docs/16/sql-set.html