通常情况下，当出现锁问题时，我们习惯性通过 SHOW FULL PROCESSLIST 和 SHOW ENGINE INNODB STATUS 命令来判断事务中锁问题的情况。其实还有特别重要的三张表，即在 information_schema 数据库下的 innodb_trx、innodb_locks 和 innodb_lock_waits 表。 这三张表可以更方便地来帮助我们监控当前的事务并分析可能存在的锁问题。
下面通过实例来逐一了解一下这三张表。
例 1
在 A窗口中，开启一个事务，在查询 tb_student 表字段 age<15 的语句上加一个写锁，SQL 命令如下：
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test.tb_student WHERE age<15 FOE UPDATE; +----+------+------+------+------+ | id | name | age | sex | num | +----+------+------+------+------+ | 3 | 王五 | 13 | 女 | 12 | | 4 | 张四 | 13 | 女 | 12 | | 6 | 赵六 | 12 | 女 | 4 | +----+------+------+------+------+ 3 rows in set (0.02 sec)
在 B窗口中开启一个事务，在 tb_student 表中插入 age=14 的记录，出现锁等待超时。
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tb_student(name,age) VALUES ('dd',14);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction我们通过开始提到的三张表来分析出现的锁等待问题。
查询 innodb_trx 表，SQL 语句和运行结果如下：
mysql> SELECT * FROM information_schema.innodb_trx \G
*************************** 1. row ***************************
trx_id: 22694
trx_state: LOCK WAIT
trx_started: 2019-08-25 09:17:26
trx_requested_lock_id: 22694:197:3:1
trx_wait_started: 2019-08-25 09:17:26
trx_weight: 2
trx_mysql_thread_id: 42
trx_query: INSERT INTO tb_student(name,age) VALUES ('dd',14)
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 22693
trx_state: RUNNING
trx_started: 2019-08-25 09:17:17
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 41
trx_query: select * FROM information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 7
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| trx_id | 唯一的事务 id 号。本例为 22694 和 22693 |
| trx_state | 当前事务的状态。本例中 22694 事务号是 lock_wait 锁等待状态 |
| trx_wait_started | 事务开始等待的时间。本例为 2019-08-25 09:17:26 |
| trx_mysql_thread_id | 线程 id，与 SHOW FULL PROCESSLIST 相对应。本例为 42 |
| trx_query | 事务运行的 SQL 语句，本例为 INSERT INTO tb_student(name,age) VALUES ('dd',14)。 |
| trx_operation_state | 事务运行的状态。本例为 inserting。 |
使用 SHOW FULL PROCESSLIST 语句查看当前线程处理情况，通常用来处理突发事件，返回的结果是实时变化的：
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
| id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
| 35 | root | localhost:64579 | test | Sleep | 1772 | | NULL |
| 36 | root | localhost:64582 | NULL | Sleep | 1775 | | NULL |
| 45 | root | localhost:64933 | test | Query | 0 | starting | SHOW FULL PROCESSLIST |
| 46 | root | localhost:64934 | test | Query | 8 | update | INSERT INTO tb_student(name,age) VALUES ('dd',14) |
+----+------+-----------------+------+---------+------+----------+---------------------------------------------------+
4 rows in set (0.00 sec)以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| id | 一个标识，kill 有问题的线程时使用 |
| user | 显示当前用户，如果不是 root，这个命令就只显示你权限范围内的 SQL 语句 |
| host | 显示这个语句是从哪个 ip 的哪个端口上发出的，可以用来追踪出问题语句的用户 |
| db | 显示这个进程目前连接的是哪个数据库 |
| command | 显示当前连接的执行命令，一般就是休眠（sleep），查询（query），连接（connect） |
| time | 这个状态持续的时间，单位是秒 |
| state | 显示使用当前连接的 SQL 语句的状态 |
| info | 显示这个 SQL 语句，因为长度有限，所以长的 SQL 语句就会显示不全，是判断问题语句的重要依据 |
下面通过 innodb_lock_waits 和 innodb_locks 两张表来判断持有锁和锁等待的对象。本例中 22696 是锁等待的对象，22695 是持有锁的对象。
innodb_lock_waits 表包含每个被阻止 InnoDB 事务的一个或多个行，指示它已请求的锁以及阻止该请求的任何锁：
mysql> SELECT * FROM information_schema.innodb_lock_waits \G *************************** 1. row *************************** requesting_trx_id: 22696 requested_lock_id: 22696:197:3:1 blocking_trx_id: 22695 blocking_lock_id: 22695:197:3:1 1 row in set, 1 warning (0.00 sec)
以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| requesting_trx_id | 请求（阻止）事务的 id |
| requested_lock_id | 事务正在等待的锁的id |
| blocking_trx_id | 阻止事务的 id |
| blocking_lock_id | 阻止另一个事务继续进行的事务所持有的锁的 id |
innodb_locks 表提供有关 InnoDB 事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的锁：
mysql> SELECT * FROM information_schema.innodb_locks \G *************************** 1. row *************************** lock_id: 22696:197:3:1 lock_trx_id: 22696 lock_mode: X lock_type: RECORD lock_table: `test`.`tb_student` lock_index: PRIMARY lock_space: 197 lock_page: 3 lock_pec: 1 lock_data: supremum pseudo-record *************************** 2. row *************************** lock_id: 22695:197:3:1 lock_trx_id: 22695 lock_mode: X lock_type: RECORD lock_table: `test`.`tb_student` lock_index: PRIMARY lock_space: 197 lock_page: 3 lock_pec: 1 lock_data: supremum pseudo-record 2 rows in set, 1 warning (0.00 sec)
以上各列含义说明如下：
| 列名 | 描述 |
|---|---|
| lock_id | 一个唯一的锁 id 号，内部为 InnoDB |
| lock_trx_id | 持有锁的交易的 id |
| lock_mode | 如何请求锁定。允许锁定模式描述符 S，X， IS，IX， GAP，AUTO_INC 和 UNKNOWN。锁定模式描述符可以组合使用以识别特定的锁定模式。 |
| lock_type | 锁的类型 |
| lock_table | 已锁定或包含锁定记录的表的名称 |
| lock_index | 索引的名称，如果 lock_type 是 RECORD，否则 NULL |
| lock_space | 锁定记录的表空间 id，如果 lock_type 是 RECORD，否则 NULL |
| lock_page | 锁定记录的页码，如果 lock_type 是 RECORD，否则 NULL。 |
| lock_pec | 页面内锁定记录的堆号，如果 lock_type 是 RECORD，否则 NULL。 |
| lock_data | 与锁相关的数据。 如果 lock_type 是 RECORD，是锁定的记录的主键值，否则 NULL。此列包含锁定行中主键列的值，格式为有效的 SQL 字符串。如果没有主键，lock_data 则是唯一的 InnoDB 内部行 id 号。如果对键值或范围高于索引中的最大值的间隙锁定，则 lock_data 报告 supremum pseudo-record。当包含锁定记录的页面不在缓冲池中时（如果在保持锁定时将其分页到磁盘），InnoDB不从磁盘获取页面，以避免不必要的磁盘操作。相反， lock_data 设置为 NULL。 |
