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

shenhp

问题起因：
两条写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