当前位置：首页 > news >正文

【MySQL】 MySQL 死锁问题分析优化器特性及优化方案

news 文章来源：https://blog.csdn.net/wjianwei666/article/details/133270085 2025/2/7 19:02:20

MySQL 死锁问题分析优化器特性及解决方案

MySQL 锁机制介绍

1、MySQL常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

2、各种锁特点

表级锁：开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

行级锁：开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

3、各种锁的适用场景

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用

行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统

4、死锁

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

MySQL 死锁问题分析优化

1、问题现象

INSERT 并发死锁问题的文章。一个具体案例如下：

研发反馈应用发生死锁，收集如下诊断内容：

LATEST DETECTED DEADLOCK

2023-07-04 06:02:40 0x7fc07dd0e700
* (1) TRANSACTION:
TRANSACTION 182396268, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59269692, OS thread handle 140471135803136, query id 3738514953 192.168.0.215 user1 updating
delete from ltb2 where c = ‘CCRSFD07E’ and j = ‘Y15’ and b >= ‘20230717’ and d != ‘1’ and e != ‘1’
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396268 lock_mode X locks rec but not gap waiting
* (2) TRANSACTION:
TRANSACTION 182396266, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1729
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 2 row lock(s), undo log entries 1
MySQL thread id 59261188, OS thread handle 140464721291008, query id 3738514964 192.168.0.214 user1 updating
update ltb2 set f = ‘0’, g = ‘0’, is_value_date = ‘0’, h = ‘0’, i = ‘0’ where c = ‘22115001B’ and j = ‘Y4’ and b >= ‘20230717’
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap
* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 603 page no 86 n bits 248 index PRIMARY of table `testdb`.`ltb2` trx id 182396266 lock_mode X locks rec but not gap waiting
* WE ROLL BACK TRANSACTION (1)

以上 space id 603 page no 86 n bits 248，其中 space id 表示表空间 ID，page no 表示记录锁在表空间内的哪一页，n bits 是锁位图中的位数，而不是页面偏移量。记录的页偏移量一般以 heap no 的形式输出，但此例并未输出该信息。

基本环境信息
确认如下问题相关信息：

数据库版本：Percona MySQL 5.7
事务隔离级别：Read-Commited
表结构和索引：
CREATE TABLE ltb2 (
ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
j varchar(16) DEFAULT NULL COMMENT ‘’,
c varchar(32) NOT NULL DEFAULT ‘’ COMMENT ‘’,
b date NOT NULL DEFAULT ‘2019-01-01’ COMMENT ‘’,
f varchar(1) NOT NULL DEFAULT ‘’ COMMENT ‘’,
g varchar(1) NOT NULL DEFAULT ‘’ COMMENT ‘’,
d varchar(1) NOT NULL DEFAULT ‘’ COMMENT ‘’,
e varchar(1) NOT NULL DEFAULT ‘’ COMMENT ‘’,
h varchar(1) NOT NULL DEFAULT ‘’ COMMENT ‘’,
i varchar(1) DEFAULT NULL COMMENT ‘’,
LAST_UPDATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
PRIMARY KEY (ID),
UNIQUE KEY uidx_1 (b,c)
) ENGINE=InnoDB AUTO_INCREMENT=270983 DEFAULT CHARSET=utf8mb4 COMMENT=‘’;
关键信息梳理

事务 T1
语句 delete from ltb2 where c = ‘code001’ and j = ‘Y15’ and b >= ‘20230717’ and d != ‘1’ and e != ‘1’
关联对象及记录 space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2
持有的锁未知
等待的锁 lock_mode X locks rec but not gap waiting

事务 T2
语句 update ltb2 set f = ‘0’, g = ‘0’, is_value_date = ‘0’, h = ‘0’, i = ‘0’ where c = ‘22115001B’ and j = ‘Y4’ and b >= ‘20230717’
关联对象及记录 space id 603 page no 86 n bits 248 index PRIMARY of table testdb.ltb2
持有的锁 lock_mode X locks rec but not gap
等待的锁 lock_mode X locks rec but not gap waiting
可以看到在主键索引上发生了死锁，但是在查询的条件中，并未使用主键列。

那为什么会在主键列出现死锁？在分析死锁根因问题前，需要先清楚 SQL 的执行情况。

2、SQL 执行情况

执行计划
以上两个 SQL 发现都有列 b、c 作为条件，且该列构成了索引唯一索引 uidx_1。简化 SQL 改为查询语句，并确认执行计划：

mysql> desc select * from ltb2 where b >= ‘20230717’ and c = ‘code001’;

执行计划中可能的索引有 uidx_1(b,c)，但实际并未使用该索引，而是采用全表扫描方式执行。

根据经验，由于列 b 为索引的最左列。但查询的条件为 b>= ‘20230717’，即该条件不是等值查询。因此数据库可能只能“使用”到 b 列。为进一步确认不使用 b 列索引的原因，查询数据分布：

mysql> select count(1) from ltb2;

±-----------+
| count(1) |
±-----------+
| 4509 |
±-----------+

mysql> select count(1) from ltb2 where b >= ‘20230717’ ;

±-----------+
| count(1) |
±-----------+
| 1275 |
±-----------+
计算满足 b 列条件的数据占比为 1275/4509 = 28%，占比差不多达到了 1/3。此时也的确不应使用该使用索引。

难道已经是作为 MySQL 5.7 的数据库，优化器还是这么简单？

ICP 特性
带着问题，将条件设置一个更大的值（但小于该列的最大值），再次执行验证查询语句：

mysql> desc select * from ltb2 where b >= ‘20990717’;

使用 Index Condition Pushdown，扫描将像这样进行：

获取下一行的索引元组（但不是完整的表行）。
测试 WHERE 条件中应用于此表的部分，并且只能使用索引列的进行检查。如果不满足条件，则继续到下一行的索引元组。
如果满足条件，则使用索引元组定位并读取整个表行。
测试适用于此表的 WHERE 条件的其余部分。根据测试结果接受或拒绝该行。
既然可以使用到 ICP 特性，进一步执行如下验证语句：

mysql> desc select * from ltb2 where b >= ‘20990717’ and c = ‘code001’;

更重要的是，既然具有 ICP 特性，针对原始的 SQL 为什么不能助于 ICP 特性使用到索引呢？

mysql> select * from ltb2 where b >= ‘20230717’ and c = ‘code001’
执行计划跟踪
继续带着问题，通过 MySQL 提供的 OPTIMIZER TRACE，跟踪执行计划生成过程。命令如下：

SET OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
– sql-1:
select * from ltb2 where b >= ‘20990717’ and c = ‘code001’;
– sql-2:
select * from ltb2 where b >= ‘20990717’;
– sql-3
select * from ltb2 where b >= ‘20230717’ and c = ‘code001’;

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace=“enabled=off”;
由于分析结果较长，截取 SQL-1 和 SQL-2 的部分结果 (rows_estimation 和 considered_execution_plans)。具体内容如下：

SQL-1
select * from ltb2 where b >= ‘20990717’ and c = ‘code001’

#分析结果
“analyzing_range_alternatives”:{
“range_scan_alternatives”:[
{
“index”:“uidx_1”,
“ranges”:[
“0xe76610 <= b”
] /* ranges /,
“index_dives_for_eq_ranges”: true,
“rowid_ordered”: false,
“using_mrr”: false,
“index_only”: false,
“rows”:64,
“cost”: 77.81,
“chosen”: true
}
] / range_scan alternatives */
}

“best_access_path”:{
“considered access_paths”:[
“rows_to_scan”: 64,
“access_type”:“range”,
“range_details”:{
“used index”;“uidx 1”
} /* range_details /,
“resulting_rows”: 64,
“cost”: 90.61,
“chosen”: true
}
] / considered access_paths /
} / best access_path */,
SQL-2
select * from ltb2 where b >= ‘20990717’

“considered access_paths”:[
{
“rows_to_scan”: 64,
“access_type”:“range”,
“range_details”:{
“used index”:“uidx_1”
} /* range_details /,
“resulting_rows”: 64,
“cost”: 90.61,
“chosen”: true
}
] / considered access_paths */,
根据以上信息：两个 SQL 的 cost 部分是完全相同的，且在优化器分析阶段只能识别到 b 的条件。分析阶段，只能根据优化器认为可用的列来计算 cost。ICP 特性，应该是在执行阶段采用用到的特性。

同时，根据 SQL-3 的执行跟踪结果，对比全表扫描和索引扫描的 cost，截取部分结果如下：

SQL-3
select * from ltb2 where b >= ‘20230717’ and c = ‘code001’;

#全表扫描结果
“range_analysis”: {
“table _scan”: {
“rows”: 4669，
“cost”: 1018.9
} /* table_scan */，

#索引扫描评估结果
“analyzing_range_alternatives”: {
“range_scan_alternatives”: [
{
“index”:“uidx_1”,
“ranges”:[
“@xe7ce0f] <= b”
] /* ranges /,
“index dives_for_eq_ranges”: true,
“rowid_ordered”: false,
“using_mrr”: false,
“index_only”: false,
" rows": 1273,
“cost”: 1528.6,
“chosen”: false,
“cause”:“cost”
}
] / range scan_alternatives */,

#最优执行计划
“best_access_path”: {
“considered access_paths”:[
{
“rows_to_scan”: 4669,
“access_type”:“scan”,
“resulting_rows”: 4669,
“cost”: 1016.8,
“chosen”: true
}
] /* considered access_paths // best access_path */
}
由于优化器阶段使用使用列 b，使用索引的成本高于全表扫描。那最终数据库就会选择使用全表扫描。除非应用使用 hint 强制索引：

mysql> desc select * from ltb2 FORCE INDEX (uidx_1) where b >= ‘20230717’ and c = ‘code001’;

综上所述，对于问题 SQL 和索引结构，由于列 b 为索引的最左列，且查询时的条件为 b>= ‘20230717’（非等值条件），数据库优化器只能“使用”到 b 列。并给予“使用”的列，评估扫码的行数和 cost。

如果优化器评估后，使用索引的成本更低，则可以使用该索引，并利用 ICP 特性进一步提高查询性能；

如果优化器评估后，使用全表扫描或的成本更低，那数据库就会选择使用全表扫描。

3、SQL 优化方案

根据第 2 部分明确了问题的原因后，通过调整索引，解决最左列尾范围查询的问题即可解决该问题。具体如下：

alter table ltb2 drop index uidx_1;
alter table ltb2 add index uidx_1(c,b);
alter table ltb2 add index idx_(b);
死锁为何发生
自此，完成了 SQL 执行计划问题的分析和解决。但直接的问题是死锁，因查询语句无法使用索引，正常就应该使用全表扫描。但是全表扫描为什么会出现死锁呢？

在此，参考《故障分析 | 从 Insert 并发死锁分析 Insert 加锁源码逻辑》的经验，对死锁过程进行大胆猜想：

T1 时刻
trx-2 执行了 UPDATE，在处理行时，在 row_search_mvcc 函数中，查询到数据。获取了对应行的 LOCK_X,LOCK_REC_NOT_GAP 锁；

T2 时刻
trx-1 执行了 DELETE，在处理行时，在 row_search_mvcc 函数中，查询到数据，尝试获取行的 LOCK_X,LOCK_REC_NOT_GAP。但由于 trx-1 已经持有了该锁，因此被堵塞。并会创建一个锁（以指示锁等待）；

T3 时刻
trx-2 继续执行 UPDATE 操作。由于是该操作除了在 T1 时刻的操作外，在其它位置，还需要获取锁（lock_mode X locks rec but not gap）。但由于 T2 时刻，trx-1 尝试获取该锁而被堵塞，并且也增加了一个锁。

假如此时，此处的实现机制和 INSERT 死锁案例一样，也没有先进行冲突检查。而只是看记录上是否存在锁的话，那么此时也会看到该记录上有 trx-1 事务的锁。从而导致 trx-2 第二次获取锁时，被堵塞。

死锁发生！

以上仅根据经验进行的猜想，真正的原因还需要进一步分析和验证。有兴趣的读者结合如下几个问题，进一步研究。

以上各步骤获取锁的位置，是否正确？
T3 时刻，update操作在其它的什么位置再次获取了锁？
T3 时刻，发起的假设是否成立？如成立，具体逻辑是什么？不成立，那正确的逻辑是什么？
T3 时刻，如果假设不成立，那死锁的原因又是什么？
以上都是针对于唯一索引/主键索引的执行逻辑分析的。那结合该案例，全表扫描和索引查询的执行逻辑是否存在差异？差异的地方在哪里？
除了调整索引，还能通过什么方式避免该问题发生？