Skip to main content

PG.L3.MISSING_LOCK_TIMEOUT — Without it, the migration waits and everything queues behind it

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

The failure this prevents is not the one people expect. It is not that your ALTER TABLE is slow — it is that your ALTER TABLE is waiting, and PostgreSQL's lock queue is fair.

A strong-lock operation that cannot take its lock immediately gets in line behind whatever is holding it. Every request that arrives afterwards gets in line behind you. So a long-running report that would have finished on its own now blocks the migration, and the migration blocks the entire application — including the queries that would have been perfectly fine.

A twenty-millisecond ADD COLUMN can take a site down for the length of somebody's analytics query.

What lock_timeout does​

It bounds the waiting, not the work. If the lock cannot be acquired within the timeout, the statement aborts — your migration fails, the queue never forms, and the application keeps serving.

A failed deploy is a better outcome than a stalled one, and this is the setting that chooses between them. That is the entire argument for the rule.

Statements beyond ALTER TABLE that count​

A trigger, a policy or a rule on an existing table, a view or a sequence that already exists, and most forms of ALTER INDEX take locks that stop reads or writes. Measured on PostgreSQL 18.4: CREATE TRIGGER takes SHARE ROW EXCLUSIVE on its table; DROP TRIGGER, the policy statements and CREATE RULE take ACCESS EXCLUSIVE on it; CREATE OR REPLACE VIEW, ALTER VIEW and DROP VIEW take ACCESS EXCLUSIVE on the view; ALTER SEQUENCE takes SHARE ROW EXCLUSIVE on the sequence; and ALTER INDEX … SET TABLESPACE holds ACCESS EXCLUSIVE on the index while it moves the files. A DROP SCHEMA … CASCADE, and any other DROP that cascades, takes the objects it reaches along with their locks. All of them count. A trigger, policy or rule on a table the same migration creates does not, like any other lock on that table.

Measured and not counted: ALTER INDEX … RENAME and ALTER INDEX … SET (…), COMMENT ON, GRANT and REVOKE, CREATE VIEW, CREATE SEQUENCE, CREATE FUNCTION, CREATE EXTENSION, ALTER TYPE … ADD VALUE, and a DROP SCHEMA without CASCADE, none of which blocks a read or a write.

What does not count as a strong lock​

ALTER TABLE … VALIDATE CONSTRAINT, the second step after NOT VALID, takes SHARE UPDATE EXCLUSIVE. No read and no write waits behind that lock, so a migration that only validates constraints draws no finding. An ALTER TABLE that validates a constraint and does anything else in the same statement takes the stronger lock and still counts.

The same holds for a change of a maintenance storage parameter — fillfactor, toast_tuple_target, parallel_workers and the vacuum and autovacuum family, each also under toast., set or reset. PostgreSQL changes those under SHARE UPDATE EXCLUSIVE too. Any other parameter counts: user_catalog_table, for one, takes ACCESS EXCLUSIVE.

Flagged​

DB::statement('ALTER TABLE orders ADD COLUMN notes text');

Preferred​

DB::statement("SET lock_timeout = '3s'");
DB::statement("SET statement_timeout = '30s'");
DB::statement('ALTER TABLE orders ADD COLUMN notes text');

Three seconds is a starting point, not a rule. The number that matters is "shorter than your users will tolerate", and it is usually smaller than people first write.

Retry rather than raise the number​

When a migration times out, the answer is to run it again — ideally in a loop with a short backoff — not to widen the window. A longer lock_timeout does not make the lock easier to get; it makes the queue longer when you fail to get it.

The two timeouts do different jobs​

lock_timeout bounds how long you wait for a lock. statement_timeout bounds how long you hold one once you have it. Neither substitutes for the other, which is why PG.L3.MISSING_STATEMENT_TIMEOUT is a separate rule and both are expected.

Configuring what SQLens expects​

The expected settings are configurable, so a project that bounds its sessions elsewhere — in the connection options, in PgBouncer, in a wrapper script — can say so:

'pgsql' => ['expected_timeouts' => ['lock_timeout', 'statement_timeout']],

Sources​

An outage on the record​

A foreign key migration queued behind a long-running read, and with no lock timeout it simply waited — holding the queue open behind it while ordinary queries timed out. About fifteen seconds of unplanned API downtime. A timeout would have made the migration fail fast, which is the whole argument for setting one.

See outages on the record for the write-up and the rule that would have avoided taking the lock at all.

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • timeout_preamble — Bound how long the statement may wait and how long it may run before it is given up on.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.