首页手记 MySQL锁情况分析

MySQL锁情况分析

标签：

MySQL

TIPS
本文基于MySQL 8.0.22

分析锁的三种方式

方式1：
```
show status like '%innodb_row_lock%';
```
可查看行锁相关的统计信息
方式2：
```
SHOW ENGINE INNODB STATUS;
```
关注结果中 TRANSACTIONS 段落

方式3：利用锁、事务相关的表

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 官方文档

锁相关的表

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 3
1	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_query
2486	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;
```