It is sometimes helpful to identify which transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. (For descriptions of these tables, see Section 14.16.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.)

Suppose that three sessions are running concurrently. Each session corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these sessions have issued the following statements, but none has yet committed its transaction:

In this scenario, use the following query to see which transactions are waiting and which transactions are blocking them:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

Or, more simply, use the sys schema innodb_lock_waits view:

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

If a NULL value is reported for the blocking query, see Identifying a Blocking Query After the Issuing Session Becomes Idle.

In the preceding table, you can identify sessions by the “waiting query” or “blocking query” columns. As you can see:

You can see the underlying data in the tables INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS.

The following table shows some sample contents of the Information Schema INNODB_TRX table.

The following table shows some sample contents of the Information Schema INNODB_LOCKS table.

The following table shows some sample contents of the Information Schema INNODB_LOCK_WAITS table.

When identifying blocking transactions, a NULL value is reported for the blocking query if the session that issued the query has become idle. In this case, use the following steps to determine the blocking query:

Sometimes it is useful to correlate internal InnoDB locking information with the session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB transaction ID, the corresponding MySQL session ID and name of the session that may be holding a lock, and thus blocking other transactions.

The following output from the INFORMATION_SCHEMA tables is taken from a somewhat loaded system. As can be seen, there are several transactions running.

The following INNODB_LOCKS and INNODB_LOCK_WAITS tables show that:

The following table shows the contents of the Information Schema PROCESSLIST table for a system running a heavy workload.

The following table shows the contents of the Information Schema INNODB_TRX table for a system running a heavy workload.

The following table shows the contents of the Information Schema INNODB_LOCK_WAITS table for a system running a heavy workload.

The following table shows the contents of the Information Schema INNODB_LOCKS table for a system running a heavy workload.