mysql 2个sql更新不同记录但死锁

问题起因：

两条写sql，操作的记录没有任何冲突，但发生死锁

 

预备知识：

InnoDB行锁是通过给索引上的索引项加锁来实现的

 

创建测试表

CREATE TABLE `t1` (

  `pk_id` INT(11) NOT NULL,

  `type` INT(11) NOT NULL,

  `status` INT(11) NOT NULL,

  PRIMARY KEY (`pk_id`)

);

create index idx_type on t1(type);

create index idx_status on t1(status);

生成测试数据

INSERT INTO  t1 (pk_id,TYPE,STATUS)

VALUES

(1,1,0),

(2,1,0),

(3,1,0),

(4,2,0),

(5,2,0),

(6,1,1),

(7,1,1),

(8,2,1);

 

例1.不一样的锁等待

连接A执行

SET autocommit=0;

BEGIN;

SELECT * FROM t1 force index(PRIMARY) WHERE pk_id<4 AND TYPE=1 AND pk_id!=2 FOR UPDATE;

马上返回查到的结果有两条pk_id为1和3

 

连接B执行

SET autocommit=0;

BEGIN;

SELECT * FROM t1 WHERE pk_id=2 FOR UPDATE;

执行后连接B一直是等待状态，如果连接A commit，连接B马上就执行完成

说明：连接A虽然查出来的结果只有pk_id为1和3的两条记录，但把pk_id为2的PRIMARY索引记录也锁住了，所以连接B一直等待

 

换个索引试试

在连接A里

commit;

SELECT * FROM t1 force index(idx_type) WHERE pk_id<4 AND TYPE=1 AND pk_id!=2 FOR UPDATE;

注意只换了force index使用的索引，其他都没变

在连接B里想写操作TYPE=1的记录(pk_id为1、2、3、6、7)都等待，因为连接A把idx_type中TYPE=1的记录都锁了

和之前例子对照可以发现，索引锁是按使用的索引来操作，并且可以确定的是锁的范围会超出查询结果范围，这点和一般以为的不一样，具体算法还有待研究。

 

例2.死锁

连接A执行

COMMIT;
SET autocommit=0;
SELECT * FROM t1 WHERE pk_id<5 FOR UPDATE;

连接A先锁住了pk索引的部分记录

 

接着连接B执行

COMMIT;
SET autocommit=0;

SELECT * FROM t1 FORCE INDEX (idx_status) WHERE STATUS=0 FOR UPDATE;

连接B锁往了idx_status的部分记录，再要锁pk时被连接A block，所以只能等待

 

最后连接A执行

UPDATE t1 SET STATUS=6 WHERE pk_id<5;

这时连接B报dead lock found

简单来讲连接A先锁住pk，B先锁住idx_status再拿pk就拿不到，这时A再拿idx_status就死锁了

类似于一个人有X但要Y，一个人有Y但要X，互不相让，就死锁了。

 

例3.想不到的死锁

把例1和例2的情况结合起来，就会出来本文最开始碰到的问题，想不到的死锁，即更新的记录完全不冲突，但就是死锁了

比如

SELECT * FROM t1 force index(idx_type) WHERE pk_id<4 AND TYPE=1 FOR UPDATE;

和

update t1 set status=1 where pk_id=6

虽然想操作的记录不同，但锁的记录有相同的，所以也可能会死锁

 

例4.index merge死锁

如果sql where里同时使用了type和status，因为type和status上都有单字段索引，所以explain会发现使用了index merge

有的sql使用的索引是先idx_type再idx_status，有的先idx_status再idx_type

这样如果锁的记录有冲突，就可能和例3一样死锁了

 

解决方案：

1.只有一个pk，不要其他索引。这样只有lock wait，不会死锁

2.有多个index，但写数据时使用的都是同样的index组合

3.有多个index，按不同的index组合写数据，但逻辑上保证锁的记录不冲突

 

时间所限，只整理了大概的逻辑，一些细节未深入。有兴趣的可以看看mysql的next-key locking