首页 > 数据库 > Mysql Innodb存储引擎 insert 死锁分析

Mysql Innodb存储引擎 insert 死锁分析

2015年6月23日 发表评论 阅读评论 4169次阅读    

前阵子高峰期看线上日志,偶然发现了核心数据库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)

T1 T2
 begin;
 begin;  select * from test where userid=5 for update
 select * from test where userid=5 for update
 INSERT INTO test (userid, goldcoin) VALUES ('5', '23') on duplicate key update goldcoin = goldcoin+23 ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  commit;

从上面的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表:

T1 T2
Begin ; Begin;
Insert into test(userid, goldcoin) values( 7,0) ;Query OK, 1 row affected (0.00 sec)对5-10加意向锁
Insert into test(userid, goldcoin) values( 6,0) ;Query OK, 1 row affected (0.00 sec)插入成功,因为6跟7不冲突。
Insert into test(userid, goldcoin) values( 7,0) ;阻塞,跟7上的插入意向锁冲突
Commit;
ERROR 1062 (23000): Duplicate entry '5' for key 'userid'报错,并且对7号记录加共享记录锁,已防止别的事务对记录做修改操作

根据 “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) ;

T1 T2
Begin ; Begin;
select * from test where userid=5 for update ;对1-5, 5-10 gap lock, 5 和对应的主键记录加记录锁。
Insert into test(userid, goldcoin) values( 4,0) ;阻塞。
Insert into test(userid, goldcoin) values( 4,0) ;阻塞

答案是,对于非唯一索引,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的插入性能就会很高。

 

Share
分类: 数据库 标签: ,
  1. 游客
    2016年7月25日01:13 | #1

    单纯的看这两条语句,是不会发生死锁的

  2. 大侠
    2015年7月4日07:37 | #2

    支持

  1. 本文目前尚无任何 trackbacks 和 pingbacks.

注意: 评论者允许使用'@user空格'的方式将自己的评论通知另外评论者。例如, ABC是本文的评论者之一,则使用'@ABC '(不包括单引号)将会自动将您的评论发送给ABC。使用'@all ',将会将评论发送给之前所有其它评论者。请务必注意user必须和评论者名相匹配(大小写一致)。