Skip to main content

DEPLOY.PREFLIGHT.METADATA_LOCK_BLOCKER — a session is sitting on a table this migration is about to change

  • Category: safety
  • Severity: high
  • Level: 0
  • Downtime class: blocking — the finding this check emits carries it. A metadata lock a DDL will queue behind is precisely a fact about concurrent traffic, which is what this axis measures
  • Stability: stable
  • Suites: deploy
  • Applies to: MySQL — the check answers appliesTo() for the mysql driver and for nothing else

What it reports​

A session that has been running longer than this run's threshold on one of the tables the pending migration is about to change.

The check builds that table list itself, from the pending statements the deploy already canonicalized. A statement is considered only if its kind is one that takes an exclusive metadata lock at its boundaries — AlterTable, DropTable, TruncateTable, AddColumn, AlterColumn, DropColumn, AddConstraint, AddPrimaryKey, AddForeignKey, DropConstraint, CreateIndex, DropIndex, Rename. Of that statement's targets it keeps the ones that are tables and that the statement acts on, not the ones it merely points at.

Those table names then go into the activity reading as its focus, together with the run's longRunningMs threshold — 5,000 ms unless the project configures another. Handing the targets in is what makes the match possible at all: a long-running session carries a relation only for the tables the caller named, so a reading asked without a focus comes back with every relation null and nothing can ever match, however blocked the instance is.

On MySQL that reading comes from the server's own state views. The long transactions come from information_schema.INNODB_TRX; which of the focus tables each session currently holds a granted metadata lock on comes from performance_schema.metadata_locks, joined to performance_schema.threads so that the lock's OWNER_THREAD_ID lines up with the connection id INNODB_TRX speaks.

One finding is emitted per matching session, and it says which thread, in which state, on which table, and for how long:

Thread <session> has been <state> on `<table>` for <seconds> s, and this deploy is about to change
that table. […] Nothing here says that thread is wrong to be running; it says the migration is about
to collide with it.

Why it matters​

InnoDB's online DDL is online in the middle and not at the ends. It needs a brief EXCLUSIVE metadata lock to start and another one to finish, and a transaction open on the target table blocks exactly those two moments — whatever the algorithm, because the two ends are exclusive in all of them.

While the ALTER waits for its metadata lock, every new statement touching that table queues behind it. The table stops answering, and the ALTER itself is doing nothing wrong. MySQL grants metadata locks in order, so one long transaction blocks the migration and every reader queued behind it: the finding is about the queue, not only about the one session it names.

That is why the finding carries the blocking downtime class — what it describes is a lock that blocks concurrent access for its duration. The check as a catalog entry carries none, for the reason in the metadata above: a deploy check reports on the state of a target rather than on a DDL operation of its own.

What to do about it​

The finding does not tell you to kill anything. It hands you the three facts the decision needs, and the decision is will this session still be there when the migration starts:

  • Which session. The label is the connection id, because that is the number SHOW PROCESSLIST and KILL accept — performance_schema identifies a session by THREAD_ID, which is a different numbering space and correlates with nothing else in the same snapshot. This finding always names a connection: the long runners come from INNODB_TRX, which speaks connection ids, and the held-lock reading drops every row whose PROCESSLIST_ID is null. The reader's thread spelling — held by something you cannot KILL, which it uses rather than fabricating an id — therefore belongs to its lock-wait rows, not to this check.
  • What it is doing. The state comes through in the server's own vocabulary, and it is what separates the two very different cases: a long active query is somebody's slow report, while a long idle transaction is a connection somebody forgot to close.
  • How long it has been running. Against the threshold this run was configured with.

Then act on that: let the session finish, end it, or move the deploy — and re-run the preflight, because the reading is a snapshot and the answer is only about the moment it looked.

If the result is undetermined rather than fail, the reason names what to fix and the result blocks exactly as a failure does — anything that is not a pass blocks. sqlens:predeploy is fail-closed, and --allow-undetermined is the one door out of that: it proceeds when every blocker is a check that could not answer, and never when one of them found something. Three shapes reach that state:

  • no activity reader at all for this run, so what is holding a metadata lock is unknown — that is not the same as nothing holding one
  • the activity views could not be read, with the server's own refusal carried through verbatim rather than paraphrased, so it points at the right grant
  • the reading came back empty AND incomplete, with every gap named — the instrument, and why it was not readable

Enabling performance_schema needs a server restart, not a grant. Which privileges each state view wants, and what each one does without them, is on the deploy readers page and in what sqlens:predeploy needs.

Why an empty reading is not automatically a pass​

This is the whole check, and the reason it exists separately from its PostgreSQL counterpart.

performance_schema can be off, and its metadata_locks instrument is disabled by default in several distributions. Neither produces an error: the views are present, they answer, and they answer with an empty set. So no blocker and the instrument that would have told me is switched off arrive as the very same answer.

The check therefore asks whether the reading is safe to read silence from before it reports anything clean. An empty reading with a named gap is undetermined; an empty reading with no gaps is a pass. Reporting a pass off the empty list alone would be the single failure this package exists to prevent.

What produces that named gap is worth knowing exactly, because it is narrower than the paragraph above. The activity reading asks the server one question — select @@performance_schema — and names the gap when the answer is off; that is the shape this check turns into undetermined. It does not read setup_instruments, so the other case, performance_schema on with the metadata_locks instrument alone disabled, leaves no gap behind and this check passes on it. See the limit below.

What it does not claim​

  • It is a snapshot, like its PostgreSQL counterpart: the blocking session may be gone by the time the migration runs.
  • It sees the tables the statements ACT ON. The referenced side of a foreign key is deliberately excluded: MySQL takes a shared metadata lock there, which is not what this finding claims, and the finding says this deploy is about to CHANGE the table it names. The cost is named rather than hidden — a long transaction on the referenced table can still delay the statement, and this check does not look for that.
  • A constraint is not a relation the activity views can report, so a constraint name never enters the reading.
  • It does not prove that the metadata_locks instrument is on. The gap it can name is performance_schema being off as a whole, asked as a server variable. A server running with performance_schema on and only the metadata-lock instrument disabled answers with an empty set and no gap, so the check passes — the one silence this reading cannot tell from calm. Before reading a pass here as proof that nothing is sitting on the table, ask the server yourself: select ENABLED from performance_schema.setup_instruments where NAME = 'wait/lock/metadata/sql/mdl'.
  • A statement that takes no exclusive metadata lock is not considered. Plain DML is not in the list of kinds above, so a backfill never makes a table look like a blocked ALTER.
  • Nothing is reported when nothing is pending. With no pending migration the check passes without reading anything, because there is no table for a session to collide with.
  • One session holding two of the deploy's tables produces one finding naming one of them. The deploy is blocked either way.
  • It says nothing about a session on a table this migration does not touch. A long-running session is reported only when its relation is one of the tables the pending statements will change.