Example 13.2. Identifying Blocking Transactions

It is sometimes helpful to be able to identify which transaction is blocking another. You can use the Information Schema tables to find out which transaction is waiting for another, and which resource is being requested.

Suppose you have the following scenario, with three users running concurrently. Each user (or session) corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these users have issued the following commands, but none has yet committed its transaction:

In this scenario, you may use this query to see who is waiting for whom:

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;

In the above result, you can identify users by the “waiting query” or “blocking query”. 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 INFORMATION_SCHEMA.INNODB_TRX.

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_LOCKS.

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS.

Example 13.3. More Complex Example of Transaction Data in Information Schema Tables

Sometimes you would like to correlate the internal InnoDB locking information with 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 user that may be holding a lock, and thus blocking another transaction.

The following output from the INFORMATION_SCHEMA tables is taken from a somewhat loaded system.

As can be seen in the following tables, there are several transactions running.

The following INNODB_LOCKS and INNODB_LOCK_WAITS tables shows that:

Note that there may be an inconsistency between queries shown in the two tables INNODB_TRX.TRX_QUERY and PROCESSLIST.INFO. The current transaction ID for a thread, and the query being executed in that transaction, may be different in these two tables for any given thread. See Section 13.7.6.3.3, “Possible Inconsistency with PROCESSLIST” for an explanation.

The following table shows the contents of INFORMATION_SCHEMA.PROCESSLIST in a loaded system.

The following table shows the contents of INFORMATION_SCHEMA.INNODB_TRX in a loaded system.

The following table shows the contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS in a loaded system

The following table shows the contents of INFORMATION_SCHEMA.INNODB_LOCKS in a loaded system.