Skip to main content

PG.L3.LOCK_TIMEOUT_INEFFECTIVE — A lock_timeout that is not smaller than statement_timeout can never fire

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: none. This is a statement about configuration, not about an operation.
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL. MySQL gets no twin — see below.

What goes wrong​

lock_timeout bounds the wait in the lock queue. statement_timeout bounds the whole statement, that wait included. Set the second to a number the first cannot reach — equal is already too large — and the statement is aborted before the lock wait ever gets to its own limit.

// Equal is already too large. The lock wait happens INSIDE the statement, so this pair
// aborts at 5s with the wait still running and the lock clock is never reached.
DB::statement("ALTER DATABASE app SET statement_timeout = '5s'");
DB::statement("ALTER DATABASE app SET lock_timeout = '5s'");

Set it strictly below instead, leaving room for the work itself:

// Strictly below, leaving room for the work itself: a 3s wait is refused as a LOCK
// timeout, and the remaining 12s belong to the statement that then has the lock.
DB::statement("ALTER DATABASE app SET statement_timeout = '15s'");
DB::statement("ALTER DATABASE app SET lock_timeout = '3s'");

Why it is worth a rule and not a footnote​

The configuration reads like a safety net. Both clocks are set, both are non-zero, and both appear in every review of the file. Nothing about the pair announces that one of them is inert.

And the failure afterwards is mislabeled. The statement comes back as a statement timeout, so whoever reads the log concludes the DDL itself is slow and goes looking at the table — when what actually happened is that the migration never got the lock. The two diagnoses send a reader to opposite ends of the problem.

This package recommends both clocks in its own remediation preamble and justifies them by exactly this difference. A rule that recommends a pair and cannot see when the pair is wrong is advice that stops being true the moment somebody follows it with the wrong numbers, so an arm proves the shipped template never produces such a pair.

What "effective" means here​

The values a fresh session inherits — pg_settings.reset_val, which is what the configuration file, the database and the role have settled between them. Not setting: SQLens sets both of these timeouts on its own session before it reads anything, so the session value describes this tool on every server it is ever pointed at.

Which layer named either clock is measured beside it, from pg_db_role_setting, because a reader told the pair is wrong still has to know which of three change paths to take — ALTER DATABASE, ALTER ROLE, or postgresql.conf. Neither clock appearing there means the pair came from the configuration file or the built-in default.

Two states it deliberately does not report​

  • lock_timeout = 0 is unbounded, not ineffective, and it is already the finding of PG.L3.MISSING_LOCK_TIMEOUT. Two findings for one fact is how a report stops being read.
  • statement_timeout = 0 means there is no statement clock to abort first, so a lock clock of any size can fire. That is the arrangement working.

false_positives​

A deploy that opens with its own SET lock_timeout and SET statement_timeout overrides both, so the inherited pair decides nothing for that migration. It still decides for every session that does not set them — psql, a console command, an ad-hoc ALTER — which is why the inherited pair is reported anyway. An instance where every write path really does set its own is the ignore entry, with that reason written beside it. The same applies to a deliberately equal pair on tables that are never contended: the lock wait is always zero there in practice, which is a bet on future contention rather than something this rule can measure.

MySQL gets no twin, and the reason is not symmetry​

max_execution_time applies to read-only SELECTs only, so it cannot abort the DDL this is about. lock_wait_timeout (metadata locks) and innodb_lock_wait_timeout (row locks) are two separate clocks with separate reach, and neither is nested inside an overall statement clock the way these two are. The pairing is not the same shape, so a rule with this id over there would be a different claim wearing the same name.

Remedy​

Change it at the layer the finding names — the good example above is the database level. If neither clock is named there, the pair comes from postgresql.conf or the built-in default, and a migration can also set its own for the length of its own transaction:

DB::statement("SET LOCAL lock_timeout = '3s'");
DB::statement("SET LOCAL statement_timeout = '15s'");

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

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.