1、故事起因于2016年11月15日的一个生产bug。业务场景是：归档一个表里边的数据到历史表里边，同是删除主表记录。
2、背景场景简化如下（数据库引擎InnoDb，数据隔离级别RR[REPEATABLE]）
-- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hello'); -- 创建表test2 CREATE TABLE test2 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); -- Transcation 1 begin; insert into test2 select * from test1 where id = 1; delete from test1 where id = 1; -- Transcation 2 begin; insert into test2 select * from test1 where id = 1; |
3、具体执行顺序
Mysql 官方解释
Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDBgenerates an error for one of the clients and releases its locks. The client returns this error。
实际场景和mysql文档有些区别，文档里边要获取的是X锁。具体事例里边要获取的是S锁。
