TIPS
本文基于MySQL 8.0.22
分析锁的三种方式
方式1：
show status like '%innodb_row_lock%';
可查看行锁相关的统计信息
方式2：
SHOW ENGINE INNODB STATUS;
关注结果中 TRANSACTIONS 段落
方式3：利用锁、事务相关的表
MySQL 5.7
MySQL 8.xINFORMATION_SCHEMA.INNODB_TRX 官方文档
INFORMATION_SCHEMA.INNODB_TRX 官方文档
INFORMATION_SCHEMA.INNODB_LOCKS 官方文档
PERFORMANCE.DATA_LOCKS 官方文档
INFORMATION_SCHEMA.INNODB_LOCK_WAITS 官方文档
PERFORMANCE.DATA_LOCKS_WAITS 官方文档
锁相关的表
MySQL主要有三张和锁有关的表，不同的版本，表还不太一样，这里我列出来了：
MySQL 5.7
MySQL 8.x
作用INFORMATION_SCHEMA.INNODB_TRX 官方文档
INFORMATION_SCHEMA.INNODB_TRX 官方文档
当前事务的执行情况
INFORMATION_SCHEMA.INNODB_LOCKS 官方文档
PERFORMANCE.DATA_LOCKS 官方文档
锁相关信息
INFORMATION_SCHEMA.INNODB_LOCK_WAITS 官方文档
PERFORMANCE.DATA_LOCKS_WAITS 官方文档
锁等待信息
INFORMATION_SCHEMA.INNODB_TRX表结构
TRX_ID：InnoDB存储引擎内部唯一的事务ID
TRX_WEIGHT：事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储引擎会选择该值最小事务的进行回滚
TRX_STATE：当前事务的状态
TRX_STARTED：事务的开始时间
TRX_REQUESTED_LOCK_ID：当trx_state的状态为LOCK WAIT时，表示当前事务正在等待的锁ID，否则记为NULL。如果想获得更详细的信息，可将该字段和PERFORMANCE.DATA_LOCKS联系起来
TRX_WAIT_STARTED：当trx_state的状态为LOCK WAIT时，表示当前事务开始等待的时间，否则记为NULL
TRX_MYSQL_THREAD_ID：MySQL中的线程ID，SHOW PROCESSLIST显示的结果
TRX_QUERY：事务运行的SQL语句
TRX_OPERATION_STATE：事务当前的操作
TRX_TABLES_IN_USE：事务使用的表的个数
TRX_TABLES_LOCKED：当前SQL语句中拥有行锁的表数目
TRX_LOCK_STRUCTS：事务保留的锁数目
TRX_LOCK_MEMORY_BYTES：内存中此事务的锁占用的内存总大小
TRX_ROWS_LOCKED：此事务大约锁定的行数。这个值可能包含物理存在的、已标记为删除但对事务不可见的记录
TRX_ROWS_MODIFIED：此事务中修改和插入记录的数目
TRX_CONCURRENCY_TICKETS：表示一个事务被swap out之前可以做多少工作，由innodb_concurrency_tickets参数决定
TRX_ISOLATION_LEVEL：当前事务的隔离级别
TRX_UNIQUE_CHECKS：当前事务是否开启了唯一性检查
TRX_FOREIGN_KEY_CHECKS：当前事务是否开启了外键检查
TRX_LAST_FOREIGN_KEY_ERROR：展示最后一个外键错误的详细错误信息
TRX_ADAPTIVE_HASH_LATCHED：自适应hash索引是否被当前事务锁定
TRX_ADAPTIVE_HASH_TIMEOUT：是否要立即放弃搜索自适应hash的锁，还是继续保留。如果没有自适应hash索引争用时，此值是0，争用期间则倒计时到0，并在语句在每次行查找之后立即释放锁
TRX_IS_READ_ONLY：值1表示事务是只读的
TRX_AUTOCOMMIT_NON_LOCKING：值为1表示事务是一个没有使用FOR UPDATE或LOCK IN SHARE MODE子句的SELECT语句，当此值和TRX_IS_READ_ONLY都为1时，innodb会优化事务从而减少更改表事务相关的开销
TRX_SCHEDULE_WEIGHT：由争用感知事务调度(CATS)算法分配给等待锁的事务的事务调度权重，值越大权重越大，此值仅为TRX_STATE字段是LOCK WAIT的事务计算，否则为NULL
PERFORMANCE.DATA_LOCKS表结构
ENGINE：持有或请求锁定的存储引擎
ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息
events_waits_xx表查看等待事件
events_stages_xxx查看到了哪个阶段
events_statements_xx表查看对应的SQL语句
events_transactions_current对应查看事务信息
OBJECT_SCHEMA：对应锁表的schema名称
OBJECT_NAME：对应锁的表名
PARTITION_NAME：对应锁的分区名
SUBPARTITION_NAME：对应锁的子分区名
INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
OBJECT_INSTANCE_BEGIN：锁对应的内存地址
LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值
PERFORMANCE.DATA_LOCKS_WAITS表结构
ENGINE：请求的锁的引擎
REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
REQUESTING_THREAD_ID：请求锁的线程ID
REQUESTING_EVENT_ID：请求锁的EVENT ID
REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
BLOCKING_THREAD_ID：锁阻塞的线程ID
BLOCKING_EVENT_ID：锁阻塞的EVENT ID
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址
实操
准备
步骤
Session 1
Session 2
Session 31
set innodb_lock_wait_timeout = 10000;
set innodb_lock_wait_timeout = 10000;
set innodb_lock_wait_timeout = 10000;
2
BEGIN;
SELECT * FROM test_isolation where id = 1 FOR UPDATE;
-
-
3
-
SELECT * FROM test_isolation where id >= 1 FOR UPDATE;
-
4
-
-
SELECT * FROM test_isolation where id <=1 FOR UPDATE;
SHOW ENGINE INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 2548
Purge done for trx's n:o < 2528 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 562948624083704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 562948624079424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 562948624078568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2547, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 4157, OS thread handle 281473501401136, query id 23214 localhost root executing
SELECT * FROM test_isolation where id <=1 FOR UPDATE
------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 4 n bits 72 index PRIMARY of table `test`.`test_isolation` trx id 2547 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000097f; asc ;;
2: len 7; hex 810000011b0110; asc ;;
3: len 5; hex 7465737431; asc test1;;
...
show status like ‘innodb_row_lock%’;
mysql> show status like 'innodb_row_lock%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| Innodb_row_lock_current_waits | 2 |
| Innodb_row_lock_time | 24967703 |
| Innodb_row_lock_time_avg | 567447 |
| Innodb_row_lock_time_max | 6453606 |
| Innodb_row_lock_waits | 44 |
+-------------------------------+----------+
5 rows in set (0.08 sec)
Innodb_row_lock_current_waits：当前正在等待锁的事务数量
Innodb_row_lock_time：从系统启动到现在发生锁定的总时间
Innodb_row_lock_time_avg：从系统启动到现在发生锁等待的平均时间
Innodb_row_lock_time_max：从系统启动到现在发生锁等待的最大时间
Innodb_row_lock_waits：从系统启动到现在发生等待的次数
查看事务的情况
select *
from information_schema.INNODB_TRX;
锁的情况
select *
from performance_schema.data_locks;
里面可以看到锁的情况，例如锁的类型、状态等信息
锁的等待情况
select *
from performance_schema.data_lock_waits;
事务与锁情况分析
用如下命令，可分析事务等待状况：
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 performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id;
或者简单一点，用如下命令，也可达到相同效果：
SELECT
waiting_trx_id,
waiting_pid,
waiting_query,
blocking_trx_id,
blocking_pid,
blocking_query
FROM sys.innodb_lock_waits;
结果如下：
waiting_trx_id
waiting_thread
waiting_query
blocking_trx_id
blocking_thread
blocking_query2486
4017
SELECT * FROM test_isolation where id <=1 FOR UPDATE
2484
4019
NULL
2486
4017
SELECT * FROM test_isolation where id <=1 FOR UPDATE
2485
4018
SELECT * FROM test_isolation where id >= 1 FOR UPDATE
2485
4018
SELECT * FROM test_isolation where id >= 1 FOR UPDATE
2484
4019
NULL
分析其中的waiting_query 和 blocking_query字段，可知：
Session 2(trx id 2485, thread id 4018)、Session 3(trx id 2486, thread id 4017)都在等待Session 1(trx id 2484, thread id 4019)
Session 3同时在等待Session 1及Session 2
杀死锁(强制释放掉锁)
kill {processlist id}
例如，processlist id可以：
从 show processlist 结果中获取
information_schema.innodb_trx表的trx_mysql_thread_id字段
上面表格中的waiting_thread、blocking_thread
sys.innodb_lock_waits视图的blocking_pid字段
解决blocking_query结果是NULL的问题
如果发出命令的Session空闲时，information_schema.innodb_trx表的trx_query字段会记录NULL。
因此，上面的Session 1的blocking_query字段值是NULL，可用如下步骤找到其执行的SQL。
information_schema.innodb_trx表的trx_mysql_thread_id字段(或sys.innodb_locak_waits表的blocking_pid字段)是被阻塞事务的processlist ID
通过processlist ID到performance_schema.threads表查询被阻塞事务的THREAD_ID
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = {上一步得到的PROCESSLIST_ID};
使用THREAD_ID，到performance_schema.events_statements_current表中查询指定THREAD_ID的数据(该线程最新执行的命令)，例如：
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = {上一步获得的THREAD_ID}
如果通过上一条SQL未能查到数据，那可以到performance_schema.events_statements_history表中查询指定THREAD_ID的数据(该线程最近执行的10条命令)
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
参考文档
本文首发
