14.16.2.1 Using InnoDB Transaction and Locking Information — MySQL 5.7

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

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

Иногда полезно определить, какая транзакция блокирует другую. Таблицы, содержащие информацию о транзакциях InnoDB и блокировках данных, позволяют определить, какая транзакция ожидает другую и какой ресурс запрашивается. (Для описания этих таблиц см. раздел 14.16.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       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;

Или, проще говоря, используйте схему 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, см. Определение блокирующего запроса после того, как сеанс, который его выдал, стал бездействующим.

id ожидающей trx	поток ожидающей	ожидающий запрос	id блокирующей trx	поток блокирующей	блокирующий запрос
`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.

Вы можете увидеть базовые данные в таблицах INNODB_TRX, INNODB_LOCKS и INNODB_LOCK_WAITS.

В следующей таблице показано некоторое примерное содержимое таблицы схемы информации INNODB_TRX.

trx id	состояние trx	trx начат	trx запрошен id блокировки	trx ожидание начато	вес trx	id mysql потока trx	запрос trx
`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`

В следующей таблице показано некоторое примерное содержимое таблицы схемы информации INNODB_LOCKS.

id блокировки	id trx блокировки	режим блокировки	тип блокировки	таблица блокировки	индекс блокировки	данные блокировки
`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`

В следующей таблице показано некоторое примерное содержимое таблицы схемы информации INNODB_LOCK_WAITS.

запрашивающий id trx	запрошенный id блокировки	блокирующий id trx	блокирующий 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, если сеанс, который выдал запрос, стал бездействующим. В этом случае выполните следующие шаги, чтобы определить блокирующий запрос:

Определите ID процесса блокирующей транзакции. В таблице sys.innodb_lock_waits ID процесса блокирующей транзакции — это значение 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_LOCKS и INNODB_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 таблицах. Для объяснения см. Раздел 14.16.2.3, «Сохранение и согласованность информации о транзакциях и блокировках InnoDB».

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

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`

Следующая таблица показывает содержимое таблицы Information Schema 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 …`

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

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

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

id блокирования	id блокирующей транзакции	режим блокирования	тип блокирования	таблица блокирования	индекс блокирования	данные блокирования
`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`