在某个深夜的上线过程中,测试人员在线上回归某个功能点时,居然出现了死锁的情况,事后找DBA sama分析死锁日志,发现死锁的主要原因是mysql innoDB的间隙锁。下面详细介绍间隙锁以及复现代码中的死锁现场:
InnoDB有三种行锁的算法:
1,Record Lock:是加在索引记录上的。
2,Gap Lock(间隙锁):对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面
3,Next-Key Lock:前两种锁的结合,锁定一个范围,并且锁定记录本身,主要目的是解决幻读的问题。
间隙锁主要是防止幻象读,用在Repeated-Read(简称RR)隔离级别下。在Read-Commited(简称RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于statement based replication
间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到RC下,或者开启 innodb_locks_unsafe_for_binlog(默认是OFF)。
间隙锁(无论是S还是X)只会阻塞insert操作。
先来看一下gap lock对于辅助索引是如何作用的:
CREATE TABLE `test` (
`id` bigint(20) NOT NULL,
`k` bigint(20) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
|REPEATABLE-READ | REPEATABLE-READ|
+-----------------------+-----------------+
1 row in set (0.00 sec)
Session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | k |
+----+------+
| 2 | 2 |
| 5 | 5 |
| 10 | 10 |
+----+------+
3 rows in set (0.00 sec)
mysql>delete from test wherek=5;
Query OK, 1 row affected (0.00 sec)
Session 2:
mysql> insert into test (id,k) values(3,3);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(4,4);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(6,6);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(9,9);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(1,1);√
Query OK, 1 row affected (0.02 sec)
mysql> insert into test (id,k) values(11,11);√
Query OK, 1 row affected (0.02 sec)
mysql> delete from test where id in(1,11);√
Query OK, 2 rows affected (0.02 sec)
mysql> insert into test (id,k) values(1,2);√
Query OK, 1 row affected (0.02 sec)
mysql> insert into test (id,k) values(3,2);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(9,10);×
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,k) values(11,10);√
Query OK, 1 row affected (0.02 sec)
可以看到,delete k=5的记录阻塞了k=3、4、5、6、7、8、9记录的插入操作,事实上,除了对于k=5这条记录上record lock之外,innoDB对于delete和update在辅助索引(非主键索引)上的条件时会对扫过的记录上间隙锁,为了防止幻读,会锁住k=5这条记录的前面一条记录(id=2,k=2)到后面一条记录(id=10,k=10)之间的区间,即锁住k在区间(2,10)的范围(如果没有后一条记录,一直锁到正无穷),至于在边界k=2及k=10上,由于索引内是按照主键排序的,不会锁住(id<2,k=2)但是会锁住(id>2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id<10,k=10).
值得注意的是,delete和update在唯一索引(primary key/unique key)上更新存在的记录时只会上行级记录锁(record key),而在唯一索引上更新不存在的记录时同辅助索引一样会上间隙锁;在上例中,delete id=5只会在(id=5,k=5)这条记录上上X锁,而delete id=7却会锁住(id>5&&id<10)这个区间。
再现生产环境的死锁:
A表:Create table A (
id bigint(20) NOT NULL,
....
PRIMARY KEY (id)
) ENGINE=InnoDB ...
出现死锁的伪代码如下:
Begin;
Delete from A where id = n;
‚Insert into A (id,...) values (n,...);
Commit/rollback;
假设当前A表仅有一条id=1的记录,在并发的情况下两个线程都先完成delete操作后再进行insert:
线程1执行的事务(由于代码上的问题导致n为根据DDB的ID分配策略重新获取的id,所以出现了删除不存在的记录的情况):
Begin;
Delete from A where id =3;(注意虽然id是主键,由于id=3记录不存在,导致上的是间隙锁而非行级锁,间隙锁锁住A表id in (1,+∞))
Insert into A (id,...) values (3,...);(试图插入但被线程2持有的A表(1,+∞)间隙锁阻塞)
线程2执行的事务:
Begin;
Delete from A where id =5;(间隙锁锁住A表 id in (1,+∞))
Insert into A (id,...) values (5,...);(试图插入但被线程1持有的A表(1,+∞)间隙锁阻塞)
此时出现两个事务互相等待对方持有的间隙锁而无法插入,出现死锁。
解决办法:
避免更新或者删除不存在的记录,虽然更新存在的记录也会产生间隙锁,但是间隙锁锁住的范围会更小;更新不存在的记录会锁住意想不到的区间范围,极其容易导致死锁问题
本文来自网易实践者社区,经作者晋挺授权发布。