MySQL死锁日志分析

查询死锁日志

show engine innodb status;

建表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
insert into test values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

事务自动提交取消设置

set autocommit=0;

案例分析

行锁案例

select from test where id=5 for update; | | | 2 | - | insert into test value(12,12,12);
*(no blocked) | | 3 | commit； | |

间隙锁简单案例

select from test where id=11 for update; | | | 2 | - | insert into test value(12,12,12);
*(blocked) | | 3 | commit； | 解除阻塞 |

间隙锁死锁问题

select * from test where id=6 for update; | | | 2 | - | begin;

select from test where id=8 for update; | | 3 | | insert into test values(8,8,8);
(blocked) | | 4 | insert into test values(7,7,7);
(Deadlock found) | 解除阻塞，*(affected row 1) | | | commit;
数据表不会生成(7,7,7)行记录 | commit;
数据表生成 (8,8,8)的行记录 |

等值查询-唯一索引

索引上的等值查询 - 给唯一索引（已存在行记录）（比如：id=5、id=10）加锁的时候，next-key lock升级为行锁。

索引上的等值查询 - next-key lock范围锁（前开后闭：(5,10]）向右遍历时最后一个值不满足查询条件时，next-key lock 退化为间隙锁。

select * from test where id=9 for update;
|
|
| | 2 |
| begin;

insert into test values(8,8,8);
(blocked) |
| | 2 |
|
| begin;

select * from test where id = 10 for update; |

• 1：加锁的范围是 (5,10]的范围锁；
• 2：由于是索引的等值查询，并且表中最后的数据 id=10不满足id=9的查询要求，故id=10的行级锁退化为间隙锁：（5,10）
• 所以事务B中id=8会被锁住，而id=10的时候不会被锁住。

等值查询-普通索引

select * from test where c=5 lock in share mode;

或者：

begin;

select id from test where c=5 for update;
|
|
| | 2 |
| update test set d=d+1 where id = 5;
(blocked) |
| | 2 |
|
| insert into value(7,7,7);
(blocked) |

select id from test where c=5 lock in share mode;
|
|
| | 2 |
| update test set d=d+1 where id = 5; |
| | 2 |
|
| insert into value(7,7,7);
(blocked) |

• 1：加锁范围是 (0,5],(5,10]的范围锁；
• 2：由于c是普通索引，根据原则4，搜到到的5后继续向后遍历直到搜索到10才放弃，故加锁范围为(5,10];
• 3：由于查询时等值查询，并且最后一个值不满足查询要求id=5,故间隙锁退化为(5, 10)
• 4：因为加锁是普通索引c加锁，而且因为索引覆盖（select id），没有对主键进行加锁，所以事务B执行正常；
• 5：因为加锁范围为(5,10), 故事务C执行阻塞；
• 6：第二个表格中， lock in share mode因为覆盖索引所以没有锁主键索引，如果使用for update 程序会觉得在之后执行更新操作时，会将主键索引一同锁住。