Skip to main content

DEPLOY.PREFLIGHT.LOCK_BLOCKER — Something is already holding a table this migration is about to lock

  • Category: safety
  • Severity: derived — the check picks one per finding, so the catalog names none. A session holding one of this deploy's own target tables is reported high; the concurrent-build arm below is reported medium.
  • Level: 0
  • Downtime class: none in the catalog — a deploy check reports on the STATE of a target, not on a DDL operation. Both findings below carry blocking themselves, and that is what a report prints (downtime=blocking on the console): the finding is about a queue forming behind a lock request
  • Stability: stable
  • Suites: deploy
  • Applies to: PostgreSQL

What it reports​

A session that has been running longer than the threshold and is holding a lock on a table the pending migration is about to take ACCESS EXCLUSIVE on.

Three things have to line up, and the check establishes them in that order.

Something is pending. With an empty pending set the result is pass and nothing is read at all. A deploy with no migrations collides with nothing, and reading the activity views to say so would spend budget on a question nobody asked.

The pending set takes ACCESS EXCLUSIVE somewhere. The statement kinds that do are listed rather than derived from downtime_class — that axis answers what an operation costs, and two operations with the same cost can take different locks:

ALTER TABLE   ADD COLUMN     ADD CONSTRAINT    DROP CONSTRAINT
DROP TABLE    ALTER COLUMN   ADD PRIMARY KEY   RENAME
TRUNCATE      DROP COLUMN    ADD FOREIGN KEY

Their table targets become the focus set, deduplicated, under the canonical name the capture already resolved — schema-qualified only where the migration wrote it that way. A target that is not a table never reaches it, because the filter is the target's own object type: the constraint a DROP CONSTRAINT names and the column a DROP COLUMN names are both dropped, neither being a relation the activity views can report. And the table a foreign key merely points at is dropped too: PostgreSQL takes SHARE ROW EXCLUSIVE on it, which is not what this finding claims — the finding says the deploy is about to take ACCESS EXCLUSIVE on the relation it names, and naming the referenced side there would put a false sentence in front of a reader.

Something is sitting on one of them. The check opens nothing itself; it asks the run's activity reader, which on PostgreSQL reads pg_stat_activity for how long each session has been running and what state it is in, and pg_locks for which of the focus tables a session currently holds a granted lock on. A long-running session whose held relation is in the focus set becomes a finding.

The focus set is what makes that last step reachable at all. pg_stat_activity carries no relation, so a reading asked without one comes back with every relation null and the match can never succeed however blocked the instance is. Both spellings are handled where the lock is looked up: an ordinary Schema::table('users', …) writes a bare name, and the bare form is resolved through the server's own search_path visibility rather than by prefixing a guessed schema.

What the message says, and what it will not say​

Session pid=<pid> has been <state> on `<relation>` for <seconds> s, and this deploy is about to
take an ACCESS EXCLUSIVE lock on that table. …

The session id, its state in the server's own vocabulary, the relation, and the duration to one decimal place. Never the query text. A production statement carries literals, and a report that reproduces them has moved user data into a file somebody pastes into a ticket.

The state is the field worth reading first, because the two alarming ones look nothing alike. A long active query is somebody's slow report. A long idle in transaction is a connection somebody forgot to close, and that is the one that silently freezes maintenance across the whole database.

Why it matters​

The ALTER will not fail. It will queue — and because PostgreSQL grants lock requests in order, every read arriving behind it queues too. The table stops answering while nothing anywhere reports an error.

That is the whole reason this check exists in the deploy gate rather than in a linter. Nothing about the migration is wrong, nothing about the blocking session is wrong, and the collision is only visible in the minute the two overlap.

The concurrent-build arm, DEPLOY.PREFLIGHT.CONCURRENT_INDEX_BLOCKER​

A second shape, reported under its own id at medium, and it is a separate branch rather than a filter on the one above for the reason that makes it worth having.

It fires when the pending set builds an index concurrently and any session has been running longer than the threshold — on any table, in any schema. A concurrent build waits for every older transaction to finish, not only the ones touching its own table, so a long query on an unrelated schema delays it just as effectively. Reporting only the blockers on its own target would call an instance clean for a build that cannot start. While it waits it holds SHARE UPDATE EXCLUSIVE, for as long as the oldest of those transactions lives.

CONCURRENTLY is read off the statement's SQL rather than off a flag, because it is a modifier on CREATE INDEX rather than a statement kind of its own. The finding counts the sessions; it does not name them, because the build waits for all of them.

The threshold, and when to move it​

A session has to have been running longer than sqlens.preflight.long_running_ms to be considered at all. The shipped value is 5000, and it is deliberately low: a transaction open for five seconds is unremarkable at noon and is exactly the thing to know about in the minute before a migration.

Raise it on an instance where long reads are normal — a reporting replica, an analytics schema, a nightly export. That is a real answer to a real false positive, and it is why this is a setting rather than a constant: the alternative a project reaches for otherwise is switching the check off, which takes the genuine blockers with it. Zero and negative are refused rather than read as report everything — the configuration validator reports the key as out of range, and the preflight itself falls back to the shipped 5000 rather than stopping a deploy over a tuning knob.

What to do about it​

Decide, then act — and the decision is wait or end it, never fix it.

The check does not make that call and says so in its own message: nothing here says the blocking session is wrong to be running, only that the migration is about to collide with it. A long analytics query is a legitimate thing for a database to be doing.

  1. Read the state and the age the finding printed. idle in transaction for minutes is a leaked connection and ending it costs nobody anything. active is work somebody is waiting for.
  2. Wait, or end the session — and for the concurrent-build arm, remember that the session you have to reach may be on a table this deploy never touches.
  3. Run the gate again. This is a snapshot, so a fresh reading is what tells you the queue is clear, not the time on the clock.

When it cannot answer​

Three states are reported as undetermined with a named reason rather than as a pass, all of them prefixed activity_unreadable:

  • The run has no activity reader. What is holding locks on the tables this migration is about to alter is unknown, and that is not the same as nothing holding them.
  • The activity views could not be read. The driver's own message is attached.
  • The reading came back empty AND incomplete — the important one. pg_stat_activity does not refuse a role that is not a member of pg_monitor; it answers with every session listed and the durations null, which produces exactly the empty list a quiet server produces. So an empty reading is only allowed to mean nothing is blocking when the reading itself was complete. Otherwise the reason names what was missing — and since undetermined blocks by default, the gate stops.

What it does not claim​

  • It is a snapshot. A session holding a lock now may release it before the migration asks for one, and one that is idle now may take a lock a second later.
  • It sees the relations the migration NAMES. A lock taken on a relation reached indirectly — through a foreign key, a trigger, a view — is not anticipated here.
  • The referenced table of a foreign key is not looked at. A long transaction on it can still delay the statement. Reporting that needs a second, weaker claim, and this check does not make it.
  • The blocker's own lock mode is not filtered. Any granted lock on a focus table is enough — and it is meant to be, because ACCESS EXCLUSIVE is the one mode that conflicts even with a plain reader.
  • One finding per session, not per table. A session sitting on two of the deploy's tables is reported once, naming one of them — the deploy is blocked either way.
  • It is not a verdict on the blocker, and it never quotes the query, so it cannot tell you what the session is for. That is a question for whoever owns it.
  • On MySQL it produces no result at all — not a pass, not a skip. The runner asks whether the check applies before running anything, and the answer is pgsql only. The MySQL counterpart is DEPLOY.PREFLIGHT.METADATA_LOCK_BLOCKER.