mysql innodb 死锁_MySQL innoDB 间隙锁产生的死锁问题 --记一次死锁血案

在某个深夜的上线过程中，测试人员在线上回归某个功能点时，居然出现了死锁的情况，事后找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,+∞)间隙锁阻塞)

此时出现两个事务互相等待对方持有的间隙锁而无法插入，出现死锁。

解决办法：

避免更新或者删除不存在的记录，虽然更新存在的记录也会产生间隙锁，但是间隙锁锁住的范围会更小；更新不存在的记录会锁住意想不到的区间范围，极其容易导致死锁问题

本文来自网易实践者社区，经作者晋挺授权发布。