趁着年轻

前阵子高峰期看线上日志，偶然发现了核心数据库mysql出现大量死锁， show engine innodb status 日志如下：（为了保密，部分字段已移除）

LATEST DETECTED DEADLOCK
------------------------
150622 12:28:17
*** (1) TRANSACTION:
TRANSACTION 2003BA867, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 18088412, OS thread handle 0x7fddcb252700, query id 119304839 192.168.1.184 xie Sending data
SELECT userid, goldcoin  FROM user_account WHERE userid = 11111 or userid = 22222 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 311409 n bits 1000 index `userid` of table `payment`.`user_account` trx id 2003BA867 lock_mode X locks rec but not gap waiting
Record lock, heap no 475 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8583d94f; asc O;;
 1: len 8; hex 800000007a87e7a6; asc z ;;

*** (2) TRANSACTION:
TRANSACTION 2003BA834, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 3112, 5 row lock(s), undo log entries 4
MySQL thread id 18088373, OS thread handle 0x7fddc8f07700, query id 119305341 192.168.1.186 xie update
INSERT INTO user_account (userid, goldcoin) VALUES ('22222', '23') on duplicate key update goldcoin = goldcoin + 23
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1 page no 311409 n bits 1000 index `userid` of table `payment`.`user_account` trx id 2003BA834 lock_mode X locks rec but not gap
Record lock, heap no 475 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8583d94f; asc O;;
 1: len 8; hex 800000007a87e7a6; asc z ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1 page no 311409 n bits 1000 index `userid` of table `payment`.`user_account` trx id 2003BA834 lock_mode X waiting
Record lock, heap no 475 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8583d94f; asc O;;
 1: len 8; hex 800000007a87e7a6; asc z ;;

*** WE ROLL BACK TRANSACTION (1)

查看线上代码可以抽离出这样的事务存在：

SELECT userid, goldcoin FROM test WHERE userid = {$sourceid} or userid = 2222 for update
INSERT INTO test (userid, goldcoin) VALUES ('2222', '23') on duplicate key update goldcoin = goldcoin + 23

看上面的死锁日志，事务1获得了一个X记录锁，也就是“SELECT userid, goldcoin FROM user_account WHERE userid = 11111 or userid = 22222 for update”上的userid=11111的锁，但是请求22222的锁时发生了等待。
事务2 呢，已经获得了userid索引上的X记录锁，可以看出应该就是22222上的记录锁了，但是他却在等待同一个userid上的X-lock锁，也就是gap lock&& record lock。

从上面的事务2的lock_mode X可以看出肯定是insert 需要加排他的next key lock， 但由于事务1正在等待22222上的X记录锁，而此锁已经被事务2自己持有了，所以就导致了死锁。（这里可能有点疑惑，为什么事务2已经持有了记录锁，自己去加next key 时却得排在事务1的后面？其实是为了公平，不然事务1可能会产生饥饿现象，总是得不到锁，有锁的容易得锁，没有的饿死）。

因此可以重现事务如下：

CREATE TABLE `test` (
`accountid` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`goldcoin` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`accountid`),
UNIQUE KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ；

insert int test(userid, goldcoin) values(4, 1), (5, 2), (10, 3) ;

(注意本文的前提是innodb存储引擎，RR隔离级别，inodb_autoinc_lock_mode=1)

从上面的2个事务流水上可以复现线上问题，基本原因还是在于第一次事务T2获取的锁不满足后续整个事务的需求，从而被事务1给挡住了。

事务2的select * from test where userid=5 for update ;   语句只是加了记录X锁，但是后面的insert into 由于userid=5的键已经存在，所以不需要插入，从而产生了duplicate-key error ，因此insert into on duplicate key update 需要加X next key lock，已防止后续其他并发事务插入了同样的索引。但X next key 锁跟 事务1的X锁存在冲突，所以产生了死锁。

下面分析一下insert的加锁逻辑。

对于上面的例子，由于userid是唯一索引，所以在select时 innodb不需要加gap lock，只需要加record lock即可, 插入时复杂一些。

传统的存储引擎比如MYISAM， 插入操作是会锁全表的，这样插入性能就会很差，而innodb作为表级锁，插入操作时做了大量性能优化，比如对于自增主键的锁，mysql也只是语句级别的锁，而不是事务级别（这就是innodb的自增主键可能会产生gap的原因），这样就保证了插入性能。

innodb在插入唯一索引时，是不需要加next key 锁的，因为索引唯一，但是他会加一种特殊的插入意向锁（）， 这种锁也是锁住一个范围，但是不会阻碍其他在这个范围内的插入，只会阻碍指定的索引上的插入，比如上面的test表：

根据 “Locks Set by Different SQL Statements in InnoDB” , 如果上面的insert 是 INSERT ... ON DUPLICATE KEY UPDATE， 那么当有重复索引错误后， 是加 X next-key lock， 而不是S记录锁， 原因在于语句后面需要对记录做update操作，所以需要排他锁。 这里简单的insert跟insert ignore效果是一样的，语句执行完成会加S记录锁，以防止别的事务对记录做写操作，从而导致幻读问题存在。

另外，如果userid是非唯一索引呢？上面的事务结果是什么样的？

CREATE TABLE `test` (

`accountid` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`goldcoin` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`accountid`),
KEY `common_useridkey` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 ;

insert into test(userid, goldcoin) values(1, 1), (5, 2), (10, 3) ;

答案是，对于非唯一索引，innodb会用next-key lock， 也就是事务1的 Insert into test(userid, goldcoin) values( 6,0) ; 也会被阻塞，因为事务2对1-5, 5-10 之间的gap 加了X gap 锁， 从而导致插入gap lock 之间的任何索引都会被阻塞。而如果是唯一索引，则不会有gap lock，只有特殊的insertion intention gap lock， 后者不会阻塞其他key的插入操作，所以mysql的插入性能就会很高。