Querious Help 5

Server Locks view — MySQL

Who is blocking whom, right now.

When your application feels it's waiting for an unusually long time, it's possible that queries are waiting on locks. The Locks view shows every InnoDB row and table lock currently held or waited on, and which session is blocking which.

The Locks view
The Locks view

Requirements #

The view requires MySQL 8.0 or later, with performance_schema enabled and readable by your user.

Refreshing #

By default the Locks view refreshes automatically so you can watch how problems develop. Use the Pause button in the bottom bar to stop it and study the current locks in more detail.

The Charts #

At the top of the Locks view, current locks are summarized by Status (waiting vs granted), Mode, and Type (table vs record). The segmented control at the right switches between donuts and horizontal bars.

Status:

  • Granted: The number of locks currently held by a session.
  • Waiting: The number of locks requested by a session that can't proceed until another session releases what it holds.

Mode:

  • Each slice is one lock mode that currently exists, using the names listed under Mode in the table below.

Type:

  • Table locks cover a whole table. Most are intention locks, which only announce that a session holds or wants row locks inside, and rarely cause trouble on their own.
  • Record locks cover individual index records, or the gaps between them. This is where real contention happens.

The Lock List #

The Lock List shows each lock, sorted so that blockers come first, with the longest Duration at the top. Beneath each blocker are the sessions waiting on it, so a blocking chain reads from top to bottom. Locks that aren't involved in any contention come after them.

Column What it shows
Conn The processlist ID of the connection holding or waiting for the lock (the number KILL takes). An amber idle badge means it holds a lock but isn't running a statement.
Blocked By For a waiting lock, the connection holding what it wants.
Object The schema and table.
Mode The InnoDB lock mode as MySQL reports it, such as X, S, IX, IS, X,GAP, S,REC_NOT_GAP, X,INSERT_INTENTION, or AUTO_INC. The Mode chart labels these Exclusive, Shared, Intent Excl., Intent Share, Excl. Gap, Share Gap, Excl. Record, Share Record, Insert Intent, Share Insert, and Auto-Inc.
Status Waiting, Blocking (granted, with sessions queued behind it), or Granted.
Waiters How many sessions are queued behind this lock. Deal with blockers with large numbers first.
Duration How long the connection has been in its current state, so a running statement's runtime or an idle connection's idle time, as a bar relative to the longest in the list.
Query The statement the connection is running (the first 200 characters). It's blank for an idle connection.

The Detail Pane #

Selecting a lock shows the connection's Database (its default schema), the locked Table, User, Type, and Mode, and the copyable query text. For a record lock, Lock Data shows the index and key values actually covered, which tells a single-row lock from a gap lock covering a range that includes rows that don't exist yet.

Recipes #

To find what's blocking everything

  1. Open Locks. Any Waiting in the Status chart means contention.

  2. Find the blocker in the first row.

    It has the highest Duration, with its waiters indented beneath it. Its Waiters count is how many sessions are stuck behind it.

  3. Select the blocker and read its query.

    If the query is empty and Conn shows the idle badge, it's an uncommitted transaction, not a slow query.

  4. Note the Conn number. That's the connection to act on.

To decide whether to wait or kill

  1. Check the blocker's Duration. A few seconds on a query that's actually executing usually clears itself.
  2. An idle blocker won't finish on its own. It holds its locks until the application commits or the connection ends.
  3. One waiter is an inconvenience. A dozen means the queue is growing faster than it drains.

  4. To intervene, find that connection ID in Processes and stop it.

    Use Stop Query to cancel just the statement, or Kill Connection to end the session and roll back its transaction.

To understand a gap-lock conflict

  1. Select the waiting lock. Modes containing GAP or INSERT_INTENTION concern ranges, not rows.
  2. Read Lock Data for the index and key range covered.

  3. Compare the blocker's and waiter's queries.

    An insert waiting on a gap lock held by a range scan or locking read is the usual pairing.

  4. Fixes are structural: narrow the range the reading query locks, or use READ COMMITTED where your application can tolerate it.

To catch a blocker that keeps coming back

  1. Turn on auto-refresh and watch the blocker's Conn across refreshes.
  2. The same ID each time is one stuck session. A different ID each time is a workload pattern.
  3. For a pattern, take the query text to Query Digest to see how often it runs and how long it takes.