15.14.2.1 Using InnoDB Transaction and Locking Information

Note

This section describes locking information as exposed by the Performance Schema data_locks and data_lock_waits tables, which supersede the INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables in MySQL 8.0. For similar discussion written in terms of the older INFORMATION_SCHEMA tables, see Using InnoDB Transaction and Locking Information in MySQL 5.7 Reference Manual.

Identifying Blocking Transactions

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 15.14.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:

Session A:
1. BEGIN;
2. SELECT a FROM t FOR UPDATE;
3. SELECT SLEEP(100);
Session B:
1. SELECT b FROM t FOR UPDATE;
Session C:
1. SELECT c FROM t FOR UPDATE;

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 performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_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.

waiting trx id	waiting thread	waiting query	blocking trx id	blocking thread	blocking query
`A4`	`6`	`SELECT b FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A3`	`5`	`SELECT SLEEP(100)`
`A5`	`7`	`SELECT c FROM t FOR UPDATE`	`A4`	`6`	`SELECT b FROM t FOR UPDATE`

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

Session B (trx id A4, thread 6) and Session C (trx id A5, thread 7) are both waiting for Session A (trx id A3, thread 5).
Session C is waiting for Session B as well as Session A.

You can see the underlying data in the INFORMATION_SCHEMA INNODB_TRX table and Performance Schema data_locks and data_lock_waits tables.

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

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
`A3`	`RUNNING`	`2008-01-15 16:44:54`	`NULL`	`NULL`	`2`	`5`	`SELECT SLEEP(100)`
`A4`	`LOCK WAIT`	`2008-01-15 16:45:09`	`A4:1:3:2`	`2008-01-15 16:45:09`	`2`	`6`	`SELECT b FROM t FOR UPDATE`
`A5`	`LOCK WAIT`	`2008-01-15 16:45:14`	`A5:1:3:2`	`2008-01-15 16:45:14`	`2`	`7`	`SELECT c FROM t FOR UPDATE`

The following table shows some sample contents of the data_locks table.

lock id	lock trx id	lock mode	lock type	lock schema	lock table	lock index	lock data
`A3:1:3:2`	`A3`	`X`	`RECORD`	`test`	`t`	`PRIMARY`	`0x0200`
`A4:1:3:2`	`A4`	`X`	`RECORD`	`test`	`t`	`PRIMARY`	`0x0200`
`A5:1:3:2`	`A5`	`X`	`RECORD`	`test`	`t`	`PRIMARY`	`0x0200`

The following table shows some sample contents of the data_lock_waits table.

requesting trx id	requested lock id	blocking trx id	blocking lock id
`A4`	`A4:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A3`	`A3:1:3:2`
`A5`	`A5:1:3:2`	`A4`	`A4:1:3:2`

Inhaltsverzeichnis Haut

Identifying a Blocking Query After the Issuing Session Becomes Idle

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:

Identify the processlist ID of the blocking transaction. In the sys.innodb_lock_waits table, the processlist ID of the blocking transaction is the blocking_pid value.
Using the blocking_pid, query the MySQL Performance Schema threads table to determine the THREAD_ID of the blocking transaction. For example, if the blocking_pid is 6, issue this query:
1. SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
Using the THREAD_ID, query the Performance Schema events_statements_current table to determine the last query executed by the thread. For example, if the THREAD_ID is 28, issue this query:
1. SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
2. WHERE THREAD_ID = 28\G
If the last query executed by the thread is not enough information to determine why a lock is held, you can query the Performance Schema events_statements_history table to view the last 10 statements executed by the thread.
1. SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
2. WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

Inhaltsverzeichnis Haut

Correlating InnoDB Transactions with MySQL Sessions

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 INNODB_TRX table and Performance Schema data_locks and data_lock_waits tables is taken from a somewhat loaded system. As can be seen, there are several transactions running.

The following data_locks and data_lock_waits tables show that:

Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D, and 77B to commit.
Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.
Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.
Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.
Transaction 77A is running, currently executing SELECT.
Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.
Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.
Transaction 19C is running, currently executing an INSERT.

Note

There may be inconsistencies between queries shown in the INFORMATION_SCHEMA PROCESSLIST and INNODB_TRX tables. For an explanation, see Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.

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

ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
`384`	`root`	`localhost`	`test`	`Query`	`10`	`update`	`INSERT INTO t2 VALUES …`
`257`	`root`	`localhost`	`test`	`Query`	`3`	`update`	`INSERT INTO t2 VALUES …`
`130`	`root`	`localhost`	`test`	`Query`	`0`	`update`	`INSERT INTO t2 VALUES …`
`61`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`INSERT INTO t2 VALUES …`
`8`	`root`	`localhost`	`test`	`Query`	`1`	`update`	`INSERT INTO t2 VALUES …`
`4`	`root`	`localhost`	`test`	`Query`	`0`	`preparing`	`SELECT * FROM PROCESSLIST`
`2`	`root`	`localhost`	`test`	`Sleep`	`566`		`NULL`

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

trx id	trx state	trx started	trx requested lock id	trx wait started	trx weight	trx mysql thread id	trx query
`77F`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77F`	`2008-01-15 13:10:16`	`1`	`876`	`INSERT INTO t09 (D, B, C) VALUES …`
`77E`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77E`	`2008-01-15 13:10:16`	`1`	`875`	`INSERT INTO t09 (D, B, C) VALUES …`
`77D`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77D`	`2008-01-15 13:10:16`	`1`	`874`	`INSERT INTO t09 (D, B, C) VALUES …`
`77B`	`LOCK WAIT`	`2008-01-15 13:10:16`	`77B:733:12:1`	`2008-01-15 13:10:16`	`4`	`873`	`INSERT INTO t09 (D, B, C) VALUES …`
`77A`	`RUNNING`	`2008-01-15 13:10:16`	`NULL`	`NULL`	`4`	`872`	`SELECT b, c FROM t09 WHERE …`
`E56`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E56:743:6:2`	`2008-01-15 13:10:06`	`5`	`384`	`INSERT INTO t2 VALUES …`
`E55`	`LOCK WAIT`	`2008-01-15 13:10:06`	`E55:743:38:2`	`2008-01-15 13:10:13`	`965`	`257`	`INSERT INTO t2 VALUES …`
`19C`	`RUNNING`	`2008-01-15 13:09:10`	`NULL`	`NULL`	`2900`	`130`	`INSERT INTO t2 VALUES …`
`E15`	`RUNNING`	`2008-01-15 13:08:59`	`NULL`	`NULL`	`5395`	`61`	`INSERT INTO t2 VALUES …`
`51D`	`RUNNING`	`2008-01-15 13:08:47`	`NULL`	`NULL`	`9807`	`8`	`INSERT INTO t2 VALUES …`

The following table shows the contents of the data_lock_waits table for a system running a heavy workload.

requesting trx id	requested lock id	blocking trx id	blocking lock id
`77F`	`77F:806`	`77E`	`77E:806`
`77F`	`77F:806`	`77D`	`77D:806`
`77F`	`77F:806`	`77B`	`77B:806`
`77E`	`77E:806`	`77D`	`77D:806`
`77E`	`77E:806`	`77B`	`77B:806`
`77D`	`77D:806`	`77B`	`77B:806`
`77B`	`77B:733:12:1`	`77A`	`77A:733:12:1`
`E56`	`E56:743:6:2`	`E55`	`E55:743:6:2`
`E55`	`E55:743:38:2`	`19C`	`19C:743:38:2`

The following table shows the contents of the data_locks table for a system running a heavy workload.

lock id	lock trx id	lock mode	lock type	lock schema	lock table	lock index	lock data
`77F:806`	`77F`	`AUTO_INC`	`TABLE`	`test`	`t09`	`NULL`	`NULL`
`77E:806`	`77E`	`AUTO_INC`	`TABLE`	`test`	`t09`	`NULL`	`NULL`
`77D:806`	`77D`	`AUTO_INC`	`TABLE`	`test`	`t09`	`NULL`	`NULL`
`77B:806`	`77B`	`AUTO_INC`	`TABLE`	`test`	`t09`	`NULL`	`NULL`
`77B:733:12:1`	`77B`	`X`	`RECORD`	`test`	`t09`	`PRIMARY`	`supremum pseudo-record`
`77A:733:12:1`	`77A`	`X`	`RECORD`	`test`	`t09`	`PRIMARY`	`supremum pseudo-record`
`E56:743:6:2`	`E56`	`S`	`RECORD`	`test`	`t2`	`PRIMARY`	`0, 0`
`E55:743:6:2`	`E55`	`X`	`RECORD`	`test`	`t2`	`PRIMARY`	`0, 0`
`E55:743:38:2`	`E55`	`S`	`RECORD`	`test`	`t2`	`PRIMARY`	`1922, 1922`
`19C:743:38:2`	`19C`	`X`	`RECORD`	`test`	`t2`	`PRIMARY`	`1922, 1922`

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-information-schema-examples.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut