岳阳公司网站建设,怎么做一个静态网页,WordPress仿app主题,设计网名的花样符号MySQL 死锁问题分析优化器特性及解决方案
MySQL 锁机制介绍
1、MySQL常用存储引擎的锁机制
MyISAM和MEMORY采用表级锁(table-level locking)
BDB采用页面锁(page-level locking)或表级锁#xff0c;默认为页面锁
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级…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 表示表空间 IDpage 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) ) ENGINEInnoDB AUTO_INCREMENT270983 DEFAULT CHARSETutf8mb4 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’;
#部分结果 ±---- -±------------------±-----±-------- | type | possible_keys | key | Extra | ±---- -±------------------±-----±-------- | ALL | uidx_1 | NULL | Using where | ±---- -±------------------±-----±-------- 注意自 MySQL 5.6 开始可以直接查看 UPDATE/DELETE/INSERT 等语句的执行计划。因个人习惯、避免误操作等原因还是习惯改为 SELECT 查看执行计划。
执行计划中可能的索引有 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’;
#部分结果 ±---------±--------±-------- | key_len | rows | Extra | ±---------±--------±-------- | 3 | 64 | Using Index condition | ±---------±--------±-------- 优化器预估返回 64 行数据占比 64/4509 1.4%因此可以使用索引。但通过执行计划从 Extra 列看到 Using index condition 提示。该提示则说明使用了索引条件下推Index Condition Pushdown, ICP。针对该特性参考官方简要说明如下
使用 Index Condition Pushdown扫描将像这样进行
获取下一行的索引元组但不是完整的表行。 测试 WHERE 条件中应用于此表的部分并且只能使用索引列的进行检查。如果不满足条件则继续到下一行的索引元组。 如果满足条件则使用索引元组定位并读取整个表行。 测试适用于此表的 WHERE 条件的其余部分。根据测试结果接受或拒绝该行。 既然可以使用到 ICP 特性进一步执行如下验证语句
mysql desc select * from ltb2 where b ‘20990717’ and c ‘code001’;
#部分结果 ±---------±--------±-------- | key_len | rows | Extra | ±---------±--------±-------- | 133 | 64 | Using Index condition | ±---------±--------±-------- 发现当新增 c 列作为条件后并且根据 key_len索引里使用的字节数可以判断的确使用到了 uidx_1 索引中的 c 列。但 rows 的结果与实际返回结果差异较大实际执行仅返回 0 行。
更重要的是既然具有 ICP 特性针对原始的 SQL 为什么不能助于 ICP 特性使用到索引呢
mysql select * from ltb2 where b ‘20230717’ and c ‘code001’ 执行计划跟踪 继续带着问题通过 MySQL 提供的 OPTIMIZER TRACE跟踪执行计划生成过程。命令如下
SET OPTIMIZER_TRACE“enabledon”,END_MARKERS_IN_JSONon; SET OPTIMIZER_TRACE_MAX_MEM_SIZE1000000; – 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“enabledoff”; 由于分析结果较长截取 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’
#分析结果 “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 */ }
“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’;
#部分结果 ±---------±--------±-------- | key_len | rows | Extra | ±---------±--------±-------- | 133 | 1273 | Using Index condition | ±---------±--------±-------- 同时根据执行计划的输出结果rows 列应该是优化器阶段的输出key_len/Extra 则包括了执行阶段的输出。
综上所述对于问题 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 时刻如果假设不成立那死锁的原因又是什么 以上都是针对于唯一索引/主键索引的执行逻辑分析的。那结合该案例全表扫描和索引查询的执行逻辑是否存在差异差异的地方在哪里 除了调整索引还能通过什么方式避免该问题发生
