使用InnoDB事务和锁信息识别阻塞事务
有时识别哪些事务阻塞了另一个事务是有帮助的。包含InnoDB事务和数据锁信息的表使您能够确定哪个事务正在等待另一个事务，以及正在请求哪些资源。
假设有三个会话同时运行。每个会话对应于一个MySQL线程，并依次执行一个事务。考虑当这些会话发出以下语句，但还没有提交事务时，系统的状态:
.会话a
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select a from abc for update; select sleep(100);+---+ | a | +---+ | 1 | +---+ 1 row in set (0.01 sec) mysql> select sleep(100); +------------+ | sleep(100) | +------------+ | 0 | +------------+ 1 row in set (1 min 40.00 sec)
.会话b
mysql> select a from abc for update;
.会话c
mysql> select a from abc for update;
在这种情况下，使用以下查询来查看哪些事务正在等待，哪些事务正在阻塞它们:
mysql> 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;
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
| 5686612 | 174 | select a from abc for update | 5686611 | 173 | select a from abc for update |
| 5686612 | 174 | select a from abc for update | 5686610 | 172 | select sleep(100) |
| 5686611 | 173 | select a from abc for update | 5686610 | 172 | select sleep(100) |
+----------------+----------------+------------------------------+-----------------+-----------------+------------------------------+
3 rows in set, 1 warning (0.01 sec)
或者，更简单地，使用sys schema的innodb_lock_waits视图:
mysql> SELECT
-> waiting_trx_id,
-> waiting_pid,
-> waiting_query,
-> blocking_trx_id,
-> blocking_pid,
-> blocking_query
-> FROM sys.innodb_lock_waits;
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| waiting_trx_id | waiting_pid | waiting_query | blocking_trx_id | blocking_pid | blocking_query |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
| 5686611 | 173 | select a from abc for update | 5686610 | 172 | select sleep(100) |
| 5686612 | 174 | select a from abc for update | 5686611 | 173 | select a from abc for update |
| 5686612 | 174 | select a from abc for update | 5686610 | 172 | select sleep(100) |
+----------------+-------------+------------------------------+-----------------+--------------+------------------------------+
3 rows in set, 3 warnings (0.08 sec)
在上面的查询结果中，你可以通过“等待查询”或“阻塞查询”列来识别会话。如你所见:
.会话b(trx_id 568661，线程号173)和会话c(trx id 5686612，线程号174)都在等待会话a(trx id 5686610,线程号172
.会话c正等待会话b和会话a
你可以在表INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS中看到底层的数据。
下表是INFORMATION_SCHEMA.INNODB_TRX的一些示例内容。
mysql> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_mysql_thread_id,trx_query from 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 | +-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ | 5686614 | RUNNING | 2023-12-11 16:39:57 | NULL | NULL | 2 | 172 | select sleep(100) | | 5686612 | LOCK WAIT | 2023-12-11 16:07:07 | 5686612:423:3:2 | 2023-12-11 16:40:07 | 2 | 174 | select a from abc for update | | 5686611 | LOCK WAIT | 2023-12-11 16:07:04 | 5686611:423:3:2 | 2023-12-11 16:40:04 | 2 | 173 | select a from abc for update | | 421200136110592 | RUNNING | 2023-12-11 16:07:52 | NULL | NULL | 0 | 176 | select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_mysql_thread_id,trx_query from information_schema.innodb_trx | +-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index,lock_data from information_schema.innodb_locks; +-----------------+-------------+-----------+-----------+--------------+------------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_data | +-----------------+-------------+-----------+-----------+--------------+------------+-----------+ | 5686612:423:3:2 | 5686612 | X | RECORD | `test`.`abc` | PRIMARY | 1 | | 5686611:423:3:2 | 5686611 | X | RECORD | `test`.`abc` | PRIMARY | 1 | | 5686614:423:3:2 | 5686614 | X | RECORD | `test`.`abc` | PRIMARY | 1 | +-----------------+-------------+-----------+-----------+--------------+------------+-----------+ 3 rows in set, 1 warning (0.00 sec) mysql> select requesting_trx_id,requested_lock_id,blocking_trx_id,blocking_lock_id from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 5686612 | 5686612:423:3:2 | 5686611 | 5686611:423:3:2 | | 5686612 | 5686612:423:3:2 | 5686614 | 5686614:423:3:2 | | 5686611 | 5686611:423:3:2 | 5686614 | 5686614:423:3:2 | +-------------------+-------------------+-----------------+------------------+ 3 rows in set, 1 warning (0.00 sec)
InnoDB锁和锁等待信息
当一个事务更新表中的一行，或者用SELECT FOR UPDATE锁住它时，InnoDB会为该行建立一个锁的列表或队列。类似地，InnoDB在表上维护了一个表级锁的列表。如果第二个事务想要更新一行数据或者在不兼容的模式下锁定一个已经被前一个事务锁定的表，InnoDB会向相应的队列添加一个锁请求。为了让事务获得锁，必须删除先前进入锁队列的所有不兼容的锁请求(当持有或请求这些锁的事务提交或回滚这些锁时)。
一个事务可以对不同的行或表有任意数量的锁请求。在任何给定的时间，一个事务可以请求一个由另一个事务持有的锁，在这种情况下，它被另一个事务阻塞。请求事务必须等待持有阻塞锁的事务提交或回滚。如果事务没有等待锁，则它处于运行状态。如果事务正在等待锁，则它处于锁等待状态。(INFORMATION_SCHEMA INNODB_TRX表表示事务状态值。)
INNODB_LOCKS表为每个锁等待事务保存了一个或多个行，表示任何阻止其进行的锁请求。该表还包含一行，描述给定行或表的待决锁队列中的每个锁。INNODB_LOCK_WAITS表显示了一个事务持有的哪些锁是其他事务请求的阻塞锁。
InnoDB事务与锁信息的持久化与一致性
由事务和锁表(INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS)暴露的数据代表了快速变化的数据。这与用户表不同，用户表中的数据只有在应用程序发起更新时才会发生变化。底层数据是内部系统管理的数据，可以非常快速地更改。
出于性能方面的原因，以及为了尽量减少事务和锁定表之间的误导性连接，每当发出对任何表的SELECT请求时，InnoDB都会将所需的事务和锁定信息收集到中间缓冲区中。仅当从上一次读取缓冲区以来已经超过0.1秒时，才会刷新该缓冲区。填充这三张表所需的数据以原子性和一致性的方式获取，并保存在这个全局内部缓冲区中，形成一个即时点的“快照”。如果在0.1秒内发生多次表访问(当MySQL处理这些表之间的联结时，几乎肯定会发生这种情况)，则使用相同的快照来满足查询。
当您将这些表中的任何一张联结到一个查询中时，都会返回正确的结果，因为这三张表的数据来自同一个快照。因为对这些表的每次查询都不会刷新缓冲区，如果您在十分之一秒内对这些表发出单独的查询，那么每次查询的结果都是相同的。另一方面，如果两次对相同或不同表的单独查询间隔超过十分之一秒，可能会得到不同的结果，因为数据来自不同的快照。
因为在收集事务和锁定数据时，InnoDB必须暂时停止，对这些表过于频繁的查询可能会对其他用户的性能产生负面影响。
因为这些表包含敏感信息(至少INNODB_LOCKS.LOCK_DATA和INNODB_TRX.TRX_QUERY)，出于安全考虑，只有具有进程权限的用户才能从中选择。
如前所述，填充事务和锁表(INNODB_TRX, INNODB_LOCKS和INNODB_LOCK_WAITS)的数据会被自动获取并保存到一个提供“时间点”快照的中间缓冲区中。当从同一个快照中查询时，所有三个表的数据是一致的。然而，底层数据变化如此之快，以至于其他类似的快速变化的数据可能并不同步。因此，在比较InnoDB事务和锁定表中的数据与PROCESSLIST表中的数据时，你应该小心。PROCESSLIST表中的数据与锁定和事务的数据并不来自同一个快照。即使你只执行了一个SELECT操作(例如，关联INNODB_TRX和PROCESSLIST)，这些表的内容通常也不一致。INNODB_TRX可以引用PROCESSLIST中不存在的行，或者INNODB_TRX中显示的当前正在执行的事务的SQL查询。TRX_QUERY可能与PROCESSLIST.INFO中的不同。