InnoDB非唯一索引导致死锁

最新推荐文章于 2021-02-06 19:31:29 发布

原创最新推荐文章于 2021-02-06 19:31:29 发布 · 456 阅读

CC 4.0 BY-SA版权

文章标签：

2 篇文章

订阅专栏

死锁日志

收到警报，数据库死锁回滚交易失败

SHOW ENGINE INNODB STATUS; 获取最近发生的deadlock

配置：innodb_print_all_deadlocks并在error log查看

在这里插入图片描述
（图1）

表T结构：

Table	Non_unique	Key_name	Column_name
T	0	PRIMARY	id
T	1	m	m_no

简化语句：

图1update语句1（id = 2933）：update T set flag = 0 where m_no = 123 and f = 1;
图1update语句2（id = 2934）：update T set flag = 0 where m_no = 123 and f = 2;

select * from T where m_no = 123;

id	m_no	f	flag
2993	123	1	0
2994	123	2	0

explain update T set flag = 0 where m_no = 123 and f = 1;

possible_keys（计划用到的索引）	rows（计划查询的行数）
m_no_index	2

即使只查询f=1的记录，仍会查询2行

由于MySql是在索引上行锁，两个事务同时用一个key–m_no_index索引，两个事务都需要同时对m_no=123的两条记录上行锁，当两个记录上锁顺序不一样（事务1锁2933行，事务2锁2934行）就有几率发生死锁。

固定上锁顺序
- 先select所有的行，然后按照主键id排序，每个事务都按顺序上锁。
  - 每个事务都先上锁2933行，如果没抢到2933行就阻塞等待，不会去抢2934行
  - 优化点：仅select主键可以在当前索引树直接拿到主键id，减少一次回表
- 缺点：因为每个事务都增加了查询和排序，增加了性能损耗，
重试机制
- 死锁发生需要一定的巧合，在非唯一索引导致的死锁问题重试在大多数时候不会有问题
- 缺点：个别事务会发生失败，影响用户体验
避免长时间持有锁，减少死锁概率
- 避免长事务
- 优化业务逻辑，在事务尽量接近结束再上锁，而不是事务刚开始的时候
- 尽早commit