记一次Mysql并发死锁

数据准备

create table t
(
    id   int(20) auto_increment
        primary key,
    cell varchar(20) null
);

INSERT INTO t (id, cell) VALUES (1, '11111111111');
INSERT INTO t (id, cell) VALUES (2, '22222222222');
INSERT INTO t (id, cell) VALUES (3, '33333333333');
INSERT INTO t (id, cell) VALUES (4, '44444444444');
INSERT INTO t (id, cell) VALUES (5, '55555555555');

session参数设置

//手动提交
set session autocommit=0;

模拟并发

session1

start TRANSACTION;
INSERT INTO t(cell) VALUES('44444444');
UPDATE t set cell = 123 WHERE cell = '44444444' ;

session2

start TRANSACTION;
INSERT INTO t(cell) VALUES('5555555');
UPDATE t set cell= 456 WHERE cell = '5555555' ;

多个终端session模拟并发事务

时间序号	session1	session2
1	start TRANSACTION;
2	INSERT INTO t(cell) VALUES('44444444');
3		start TRANSACTION;
4		INSERT INTO t(cell) VALUES('5555555');
5	UPDATE t set cell = 123 WHERE cell = '44444444' ;
6		UPDATE t set cell= 456 WHERE cell = '5555555' ;

结果

• 当运行到事务1的update时，发生了等待
• 当运行到事务2的update时，发生了死锁

问题分析

根据show engine innodb status查询 

 可见Transaction1与Transaction2 同时锁住了同一部分，而且是locak_mode X rec bur not gap Record lock

事务1的insert产生了一个插入意向锁（insert 会对插入的这条记录加排他记录锁，在加记录锁之前还会加一种 GAP 锁，叫做插入意向锁），事务2的insert也产生了一个插入意向锁（不会被互相锁住，因为数据行并不冲突），此时事务1再进行update语句，因未走索引，导致扫全表，而在扫到事务2插入那条数据时，行锁与插入意向锁冲突了，导致事务1需要等待事务2释放插入意向锁而进行等待。事务2在进行update时，也同样需要扫全表，但是全表都被事务1的update锁住了，事务2需要等待 等待事务2释放插入意向锁的 事务1 的行锁 释放，因此发生了死锁

解决方法：在cell列建立索引