MySQL锁监控

通常情况下，当出现锁问题时，我们习惯性通过 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)

以上各列含义说明如下：

使用 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)

以上各列含义说明如下：

下面通过 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)

以上各列含义说明如下：

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)

以上各列含义说明如下：