微信开发 网站备案吗,昆山品牌网站,app定制开发公司选择,大连关键词快速排名背景
在DBS-集群列表-更多-连接查询-死锁中#xff0c;看到9月22日有数据库死锁日志#xff0c;后排查发现是因为mysql的优化-index merge#xff08;索引合并#xff09;导致数据库死锁。
定义
index merge(索引合并)#xff1a;该数据库查询优化的一种技术#xff0…背景
在DBS-集群列表-更多-连接查询-死锁中看到9月22日有数据库死锁日志后排查发现是因为mysql的优化-index merge索引合并导致数据库死锁。
定义
index merge(索引合并)该数据库查询优化的一种技术在mysql 5.1之后进行引入它可以在多个索引上进行查询并将结果合并返回。
mysql数据库的锁机制
在排查问题之前首先讲一下mysql数据库的锁机制
1 加锁的基本单位是 next-key lock记录锁间隙锁当记录锁或者间隙锁能够解决幻读的问题就会退化为记录锁行锁间隙锁。
2 加锁是将锁加在了索引之上而不是数据之上。
3 对于当前读索引进行加锁当前读语句包括了select … from. … for update,select…from … lock in share mode,update…,delete…。
4 加锁根据唯一性索引、非唯一性索引进行了区分根据查询条件分为了等值查询、范围查询根据是否能够查到数据又分为了记录存在和不存在的情况。
本次死锁问题使用的索引是非唯一性索引的等值查询中记录存在的情况因此本文仅仅详细介绍这种情况其它情况可以查看最下面的参考文档1
加锁情况是会依次扫描首先扫描到条件匹配的数据加一个next-key lock然后接下来扫描到第一个记录不匹配的数据增加一个间隙锁最后对查到记录的主键增加一个记录锁
针对以上情况加了三种锁加锁的目的是为了防止幻读的发生。
针对二级索引的锁进行分析
表结构
CREATE TABLE jdi_roster_apply_detail (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,apply_id varchar(100) NOT NULL COMMENT 申请单号,status tinyint(10) NOT NULL COMMENT 状态,PRIMARY KEY (id),KEY idx_status (status),KEY idx_apply_id (apply_id)
) ENGINEInnoDB AUTO_INCREMENT984483 DEFAULT CHARSETutf8 COMMENT黑白名单申请单明细
表数据
idapply_idstatus95965116953692205220689981960738169536922757617369019613191695373047673903326196136516953731224478652281
通过 idx_apply_id建立的b树 因为索引是二级索引所以叶子节点存储的数据是主键值。
执行sql:
select * from jdi_roster_apply_detail where apply_id1695369227576173690 for update
执行数据扫描过程
1 查到符合条件的记录增加next-key 锁因此锁是(1695369220522068998,1695369227576173690]
2 找到第一个不符合记录的数据增加间隙锁因此锁是 16953692275761736901695373047673903326
3 对符合条件的主键索引增加记录锁因此对 id960738增加记录锁。
针对三种锁解决的幻读
1 如果没有第一条的next-key锁 另一个事务增加一个apply_id1695369227576173690 id960738 时该事务在进行查询时会多一条记录因此会造成幻读。
2 如果没有第二条的 间隙锁另一个事务增加一个apply_id1695369227576173690 id960738是该事务在进行查询时会多一条记录因此会造成幻读。
3 如果没有第三条的记录锁另一条事务删除一条 id960738的记录该事务进行查询时会少一条数据因此会造成幻读。
实际问题分析
数据库死锁日志 以上日志两个事务分别执行了update语句
#事务1
update jdi_roster_apply_detail set status 10 where status 1 and apply_id 1695369220522068998
#事务2
update jdi_roster_apply_detail set status 10 where status 1 and apply_id 1695369227576173690
这个sql是用于将某个申请单id待审批的数据改为已审批。
因为在泰山里不能执行update语句 因此执行了select语句查看用的索引情况
explain select * from jdi_roster_apply_detail where status 1 and apply_id 1695369220522068998
执行的结果 通过结果可以看出两个update语句都使用了两个索引分别是idx_statusidx_apply_id然后将查到的结果进行合并因此在模拟的过程中可以将其拆成两个查询语句。
死锁模拟
事务1事务2锁的范围beginbeginselect * from jdi_roster_apply_detail where apply_id ‘1695369220522068998’ for updateidx_apply_id所以锁住了-∞1695369220522068998](1695369220522068998,1695369227576173690) 主键id索引锁住了 id959651select * from jdi_roster_apply_detail where apply_id ‘1695369227576173690’ for updateidx_apply_id所以锁住了16953692205220689981695369227576173690](1695369227576173690,1695373047673903326) 主键id索引锁住了 id960738select * from jdi_roster_apply_detail where status 1 for update会对idx_status上加next-key锁和间隙锁但是在对主键959651960738961319961365进行加记录锁时其中事务2 对960738已经加了记录锁所以该事务1进行了阻塞。select * from jdi_roster_apply_detail where status 1 for update会对idx_status上加next-key锁和间隙锁但是在对主键959651960738961319961365进行加记录锁时其中事务1对959651已经加了记录锁所以该事务2进行了阻塞。deadlock
两个事务分别想要两个主键id的记录锁造成相互等待形成了死锁。
以上是先执行idx_apply_id的索引查询再执行idx_status索引查询如果先执行idx_status索引查询再执行idx_apply_id的索引查询也会因为主键的记录锁造成死锁。
解决方案
1 利用force index(idx_apply_id)强制走某个索引这样InnoDB就会忽略index merge避免多个索引同时加锁的情况。
2 禁用Index Merge用命令禁用Index MergeSET GLOBAL optimizer_switch‘index_mergeoff,index_merge_unionoff,index_merge_sort_unionoff,index_merge_intersectionoff’;
3 Index Merge同时使用了2个独立索引因此新建一个包含这两个索引所有字段的联合索引这样InnoDB就只会走这个单独的联合索引。
第三种方案相较于第一种查询性能更好相对于第二种仅仅作用于该表影响范围小因此本次也是采用了该方案。
总结
该死锁问题是因为优化器使用了合并索引问题导致的最终通过新建一个联合索引来解决这个问题。
参考文档
1 https://www.xiaolincoding.com/mysql/lock/how_to_lock.html 作者京东工业 李小辉 来源京东云开发者社区 转载请注明来源
