Innodb中怎么查看锁信息
一、前言
上一篇说了下innodb中锁的大概意思， 这篇说说怎么查看加的哪些锁。不然后续出现死锁或者锁等待都不知道为什么。
二、底层基础表信息
在学会如何查看有哪些锁信息时， 需要了解一些基础表信息， 这些能帮助我们快速排查。
从前两篇文章可以了解到innodb中的锁是在事务内执行的，所以我们先了解下底层的事务表看看从中可以看出哪些内容。
2.1 information_schema.INNODB_TRX
底层有两个databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db |
+--------------------+
5 rows in set (0.01 sec)
可以选择information_schema 查看下面是否有事务相关的表。
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables like '%tr%';
+-------------------------------------+
| Tables_in_information_schema (%TR%) |
+-------------------------------------+
| CHECK_CONSTRAINTS |
| INNODB_METRICS |
| INNODB_TRX |
| OPTIMIZER_TRACE |
| REFERENTIAL_CONSTRAINTS |
| ST_GEOMETRY_COLUMNS |
| TABLE_CONSTRAINTS |
| TRIGGERS |
+-------------------------------------+
8 rows in set (0.00 sec)
可见存在事务表INNODB_TRX， 然后看看其表结构，然后针对每个字段的解释加进去
mysql> show create table INNODB_TRX;
....
INNODB_TRX | CREATE TEMPORARY TABLE `INNODB_TRX` (
# 事务ID
`trx_id` varchar(18) NOT NULL DEFAULT '',
# 事务状态， 允许值是 RUNNING，LOCK WAIT， ROLLING BACK，和 COMMITTING。
`trx_state` varchar(13) NOT NULL DEFAULT '',
# 事务开始时间
`trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
# 事务当前等待的锁的ID，如果TRX_STATE是LOCK WAIT；否则NULL。
`trx_requested_lock_id` varchar(105) DEFAULT NULL,
# 事务开始等待锁的时间
`trx_wait_started` datetime DEFAULT NULL,
# 事务权重， 反映（但不一定是准确计数）更改的行数和事务锁定的行数。为了解决死锁， InnoDB选择权重最小的事务作为“受害者”进行回滚。无论更改和锁定行的数量如何，更改非事务表的事务都被认为比其他事务更重。
`trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',
# MySQL 线程 ID。 这个id很重要，如果发现某个事务一直在等待无法结束的话，可以通过这个ID kill掉。
`trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',
# 事务正在执行的 SQL 语句。
`trx_query` varchar(1024) DEFAULT NULL,
# 交易的当前操作，如果有的话；否则 NULL。
`trx_operation_state` varchar(64) DEFAULT NULL,
# InnoDB处理此事务的当前 SQL 语句时使用 的表数。
`trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
# InnoDB当前 SQL 语句具有行锁 的表数。（因为这些是行锁，而不是表锁，尽管某些行被锁定，但通常仍可以由多个事务读取和写入表。）
`trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
# 事务保留的锁数。
`trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
# 此事务的锁结构在内存中占用的总大小。
`trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
# 此事务锁定的大致数量或行数。该值可能包括物理上存在但对事务不可见的删除标记行。
`trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
# 此事务中修改和插入的行数。
`trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
`trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
# 当前事务的隔离级别。
`trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
`trx_unique_checks` int(1) NOT NULL DEFAULT '0',
`trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
`trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
`trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
`trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
`trx_is_read_only` int(1) NOT NULL DEFAULT '0',
`trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
上述已经针对重要字段进行了注释说明，该表主要是记录事务中的一些信息，非常有用，其中就会记录等钱等待锁的ID。
详细请看官方文档：https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-trx-table.html
其中以下几个字段需要特别留意下，
TRX_ID 事务ID，
TRX_REQUESTED_LOCK_ID 事务当前等待的锁的ID。 如果当前事务阻塞就可以看出之前的锁
TRX_MYSQL_THREAD_ID MySQL 线程 ID
2.2 performance_schema.data_locks
上述事务表中有记录当前等待锁的ID， 那么这个id来源哪呢？
可以在information_schema performance_schema中搜索show tables like '%lock%';, 后面发现在performance_schema下
mysql> show tables like '%lock%';
+---------------------------------------+
| Tables_in_performance_schema (%lock%) |
+---------------------------------------+
| data_lock_waits |
| data_locks |
| metadata_locks |
| rwlock_instances |
| table_lock_waits_summary_by_table |
+---------------------------------------+
5 rows in set (0.00 sec)
先看看data_locks的表结构：
CREATE TABLE `data_locks` (
# 持有或请求锁的存储引擎。
`ENGINE` varchar(32) NOT NULL,
# 存储引擎持有或请求的锁的 ID。( ENGINE_LOCK_ID, ENGINE) 值的元组是唯一的。
# information_schema.INNODB_TRX.trx_requested_lock_id 就来源于这
`ENGINE_LOCK_ID` varchar(128) NOT NULL,
# 请求锁定的事务的存储引擎内部 ID
# 来源information_schema.INNODB_TRX.TRX_ID
`ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL,
# 创建锁的会话的线程 ID
`THREAD_ID` bigint(20) unsigned DEFAULT NULL,
`EVENT_ID` bigint(20) unsigned DEFAULT NULL,
`OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
`OBJECT_NAME` varchar(64) DEFAULT NULL,
`PARTITION_NAME` varchar(64) DEFAULT NULL,
`SUBPARTITION_NAME` varchar(64) DEFAULT NULL,
# 锁定索引的名称
`INDEX_NAME` varchar(64) DEFAULT NULL,
`OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
# 锁的类型。该值取决于存储引擎。对于 InnoDB，允许的值为 RECORD行级锁和 TABLE表级锁。
`LOCK_TYPE` varchar(32) NOT NULL,
# 如何请求锁定。
# 该值取决于存储引擎。为 InnoDB，允许值是 S[,GAP]，X[,GAP]， IS[,GAP]，IX[,GAP]， AUTO_INC，和 UNKNOWN。AUTO_INC和UNKNOWN 指示间隙锁定以外的锁定模式 （如果存在）
`LOCK_MODE` varchar(32) NOT NULL,
# 锁定请求的状态。
# 该值取决于存储引擎。对于 InnoDB，允许的值为 GRANTED（锁定已持有）和 WAITING（正在等待锁定）。
`LOCK_STATUS` varchar(32) NOT NULL,
`LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
PRIMARY KEY (`ENGINE_LOCK_ID`,`ENGINE`),
KEY `ENGINE_TRANSACTION_ID` (`ENGINE_TRANSACTION_ID`,`ENGINE`),
KEY `THREAD_ID` (`THREAD_ID`,`EVENT_ID`),
KEY `OBJECT_SCHEMA` (`OBJECT_SCHEMA`,`OBJECT_NAME`,`PARTITION_NAME`,`SUBPARTITION_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
详细参数解释请参考： https://dev.mysql.com/doc/refman/8.0/en/performance-schema-data-locks-table.html
从上面可以知道当前事务如果持有锁的就看出它持有的什么类型的锁、锁状态。
三、实践得真知
1、开始一个事务1， 对某条记录加排他锁：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 3 for update;
+----+------+
| id | name |
+----+------+
| 3 | 3 |
+----+------+
1 row in set (0.00 sec)
然后根据当前线程id查询事务信息:
mysql> select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID() \G
*************************** 1. row ***************************
trx_id: 38441
trx_state: RUNNING
trx_started: 2021-08-22 09:26:56
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 32
trx_query: select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID()
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: 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
1 row in set (0.00 sec)
可以看出当前事务ID38441, 锁定行数为1行， 符合预期。
然后再根据事务ID查看锁信息:
mysql> select * from performance_schema.data_locks where ENGINE_TRANSACTION_ID = 38441 \G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4720840032:1068:140354321295272
ENGINE_TRANSACTION_ID: 38441
THREAD_ID: 72
EVENT_ID: 246
OBJECT_SCHEMA: test_db
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140354321295272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4720840032:11:4:2:140354330466328
ENGINE_TRANSACTION_ID: 38441
THREAD_ID: 72
EVENT_ID: 246
OBJECT_SCHEMA: test_db
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140354330466328
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
2 rows in set (0.00 sec)
可以看出当前事务对应两个锁信息， 第一个是表锁：意向排他锁， 第二个是行锁：排他锁 且 非间隙锁， 都是持有锁的状态， 而且锁的记录也是primarKey = 3的那条记录。 符合预期。
这里可能会有好奇，为啥会有表锁呢？ 不熟悉的可以再看看之前的文章：https://www.cnblogs.com/yuanfy008/p/14993366.html
2、开始一个事务2， 先查看当前线程id, 然后对id=3的那条记录加排他锁。
mysql> begin;
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 33 |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from t where id = 3 for update;
这是会锁等待， 因为事务1占有着呢。
然后再去另外一个窗口根据mysql线程id查看事务情况：
mysql> select * from information_schema.INNODB_TRX where TRX_MYSQL_THREAD_ID = 33 \G
*************************** 1. row ***************************
trx_id: 38445
trx_state: LOCK WAIT
trx_started: 2021-08-22 09:52:40
trx_requested_lock_id: 4720840880:11:4:2:140354330471280
trx_wait_started: 2021-08-22 09:55:56
trx_weight: 2
trx_mysql_thread_id: 33
trx_query: select * from t where id = 3 for update
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
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
1 row in set (0.00 sec)
可以看出当前事务还在等待另一个锁（ID：4720840880:11:4:2:140354330471280）的释放，而这个锁的持有这正好是事务1。符合预期
这个时候我们再去看这个事务对应锁的信息， 那这个时候有几把锁呢？ 应该只有一把：表锁 - 意向排他锁
mysql> select * from performance_schema.data_locks where ENGINE_TRANSACTION_ID = 38445 \G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4720840880:1068:140354321297272
ENGINE_TRANSACTION_ID: 38445
THREAD_ID: 73
EVENT_ID: 31
OBJECT_SCHEMA: test_db
OBJECT_NAME: t
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140354321297272
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
这就是本该拼搏的年纪，却想得太多，做得太少！
