17.15.2.1 Using InnoDB Transaction and Locking Information — MySQL 8.4

17.15.2.1 Использование информации о транзакциях и блокировках InnoDB

В этом разделе описывается использование информации о блокировках, предоставляемой таблицей Performance Schema data_locks и data_lock_waits.

Определение блокирующих транзакций

Иногда полезно определить, какая транзакция блокирует другую. Таблицы, содержащие информацию о транзакциях и блокировках данных, позволяют определить, какая транзакция ожидает другую, и какой ресурс запрашивается. (Описание этих таблиц см. в Раздел 17.15.2, «Информация о транзакциях и блокировках InnoDB в INFORMATION_SCHEMA».)

Предположим, что одновременно выполняются три сеанса. Каждый сеанс соответствует потоку MySQL и выполняет одну транзакцию за другой. Рассмотрим состояние системы, когда эти сеансы выполнили следующие операторы, но ни один из них еще не подтвердил свою транзакцию:

Сеанс A:

BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);

Сеанс B:
```
SELECT b FROM t FOR UPDATE;
```
Сеанс C:
```
SELECT c FROM t FOR UPDATE;
```

В этом случае используйте следующий запрос, чтобы увидеть, какие транзакции ожидают и какие их блокируют:

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;

Или, проще говоря, используйте схему sys innodb_lock_waits:

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

Если для блокирующего запроса сообщается значение NULL, см. Определение блокирующего запроса после того, как издающий сеанс стал бездействующим.

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`

В приведенной таблице вы можете определить сеансы по столбцам «ожидающий запрос» или «блокирующий запрос». Как вы можете видеть:

Сеанс B (trx id A4, поток 6) и сеанс C (trx id A5, поток 7) оба ожидают сеанс A (trx id A3, поток 5).
Сеанс C ожидает сеанс B, а также сеанс A.

Вы можете увидеть основные данные в INFORMATION_SCHEMA INNODB_TRX таблице и Performance Schema data_locks и data_lock_waits таблицах.

Следующая таблица показывает некоторые примеры содержимого таблицы INNODB_TRX.

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`

Следующая таблица показывает некоторые примеры содержимого таблицы data_locks.

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`

Следующая таблица показывает некоторые примеры содержимого таблицы data_lock_waits.

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`

Определение блокирующего запроса после того, как сессия выдачи запроса стала бездействующей

При определении блокирующих транзакций для блокирующего запроса сообщается значение NULL, если сессия, которая выпустила запрос, стала бездействующей. В этом случае выполните следующие шаги, чтобы определить блокирующий запрос:

Определите идентификатор процесса (processlist ID) блокирующей транзакции. В таблице sys.innodb_lock_waits идентификатор процесса блокирующей транзакции — это значение blocking_pid.
Используя blocking_pid, запросите таблицу MySQL Performance Schema threads, чтобы определить THREAD_ID блокирующей транзакции. Например, если blocking_pid равно 6, выполните следующий запрос:
```
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
```
Используя THREAD_ID, запросите таблицу Performance Schema events_statements_current, чтобы определить последний запрос, выполненный потоком. Например, если THREAD_ID равно 28, выполните следующий запрос:
```
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G
```
Если последнего выполненного запроса потока недостаточно для определения причины удержания блокировки, можно запросить таблицу Performance Schema events_statements_history, чтобы просмотреть последние 10 выполненных запросов потока.
```
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
```

Сопоставление транзакций InnoDB с сессиями MySQL

Иногда бывает полезно сопоставлять внутреннюю информацию о блокировке InnoDB с информацией на уровне сессии, поддерживаемой MySQL. Например, вам может быть интересно узнать для данного идентификатора транзакции InnoDB соответствующий идентификатор сессии MySQL и имя сессии, которая может удерживать блокировку и, таким образом, блокировать другие транзакции.

Следующий вывод из таблицы INFORMATION_SCHEMA INNODB_TRX и Performance Schema data_locks и data_lock_waits взят из несколько загруженной системы. Как видно, выполняется несколько транзакций.

Следующие таблицы data_locks и data_lock_waits показывают, что:

Транзакция 77F (выполняющая INSERT) ожидает завершения транзакций 77E, 77D и 77B.
Транзакция 77E (выполняющая INSERT) ожидает завершения транзакций 77D и 77B.
Транзакция 77D (выполняющая INSERT) ожидает завершения транзакции 77B.
Транзакция 77B (выполняющая INSERT) ожидает завершения транзакции 77A.
Транзакция 77A выполняется, в настоящее время выполняется SELECT.
Транзакция E56 (выполняющая INSERT) ожидает завершения транзакции E55.
Транзакция E55 (выполняющая INSERT) ожидает завершения транзакции 19C.
Транзакция 19C выполняется, в настоящее время выполняется INSERT.

Примечание

Возможны несоответствия между запросами, показанными в INFORMATION_SCHEMA PROCESSLIST и INNODB_TRX. Для получения объяснения см. Раздел 17.15.2.3, «Сохранение и согласованность информации о транзакциях и блокировках InnoDB».

Следующая таблица показывает содержимое таблицы PROCESSLIST для системы, работающей с большой нагрузкой.

ID	ПОЛЬЗОВАТЕЛЬ	ХОСТ	БАЗА ДАННЫХ	КОМАНДА	ВРЕМЯ	СОСТОЯНИЕ	ИНФОРМАЦИЯ
`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`

Следующая таблица показывает содержимое таблицы INNODB_TRX для системы, работающей с большой нагрузкой.

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 …`

Следующая таблица показывает содержимое таблицы data_lock_waits для системы, работающей с большой нагрузкой.

Идентификатор запрашивающей транзакции	Идентификатор запрашиваемого блокируемого ресурса	Идентификатор блокирующей транзакции	Идентификатор блокируемого ресурса
`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`

В следующей таблице показано содержимое таблицы data_locks для системы, работающей под высокой нагрузкой.

Идентификатор блокировки	Идентификатор транзакции блокировки	Режим блокировки	Тип блокировки	Схема блокировки	Таблица блокировки	Индекс блокировки	Данные блокировки
`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`