Movatterモバイル変換

It is sometimes helpful to identify which transaction blocks another. The tables that contain information aboutInnoDB 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, seeSection 17.15.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_queryFROM       performance_schema.data_lock_waits wINNER JOIN information_schema.innodb_trx b  ON b.trx_id = w.blocking_engine_transaction_idINNER JOIN information_schema.innodb_trx r  ON r.trx_id = w.requesting_engine_transaction_id;

Or, more simply, use thesys schemainnodb_lock_waits view:

SELECT  waiting_trx_id,  waiting_pid,  waiting_query,  blocking_trx_id,  blocking_pid,  blocking_queryFROM sys.innodb_lock_waits;

If a NULL value is reported for the blocking query, seeIdentifying 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 theINFORMATION_SCHEMAINNODB_TRX table and Performance Schemadata_locks anddata_lock_waits tables.

The following table shows some sample contents of theINNODB_TRX table.

The following table shows some sample contents of thedata_locks table.

The following table shows some sample contents of thedata_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 internalInnoDB locking information with the session-level information maintained by MySQL. For example, you might like to know, for a givenInnoDB 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 theINFORMATION_SCHEMAINNODB_TRX table and Performance Schemadata_locks anddata_lock_waits tables is taken from a somewhat loaded system. As can be seen, there are several transactions running.

The followingdata_locks anddata_lock_waits tables show that:

The following table shows the contents of thePROCESSLIST table for a system running a heavyworkload.

The following table shows the contents of theINNODB_TRX table for a system running a heavyworkload.

The following table shows the contents of thedata_lock_waits table for a system running a heavyworkload.

The following table shows the contents of thedata_locks table for a system running a heavyworkload.