insert 语句_再遇死锁insert语句导致的死锁

      日前，我们生产上遇到了一个死锁现象，通过show engine innodb status 看到死锁信息如下(场景复现，生产上的语句不同，但情况完全相同)：

如图所示，能看到的信息，仅仅看到两个相同"select * from test where cid='00018' for update" SQL之间产生死锁，因为该系统没有开数据库全日志，不知道这两个会话执行这个SQL之前是否在同一事物内还执行过其他SQL, 研发同事看到上面图片的信息后，第一反应------“怎么两个select for update 语句会产生死锁？”。然后作者也被带偏，按照常规的思维看这个问题，通常我们在进行排他性的修改操作时，在事务启动后，第一个语句就是select for update。所以，“也怀疑是多并发对同一条记录执行select for update 语句产生死锁。”

         然后跟踪该"select for update"加锁过程，同时根据上面图片中的show engine innodb status \G 命令输出的死锁信息中，有持有共享锁， 但跟踪“select for update”语句的加锁过程，并没有发现有加共享锁的现象。 另外，仔细翻了翻mysql的代码，发现对行加共享锁的函数极少。 重点关注了下面的函数。row_ins_set_shared_rec_lock

发现该函数加共享锁，根据这个函数的注释，是在检查可能发生唯一性冲突的时候使用。 

       根据以上获得的信息，认为事务开始之后，应该先执行了其他的语句后，再执行了"select for update "语句，select for update 语句并不是事务开启后的第一个语句，并将这个观点(猜测)反馈给研发。 

         然后在测试环境通过压测复现这个问题，因为开启了全日志，所以马上找到了完整的sql 。 果然，在执行select for update语句之前，执行了一个insert 操作。 完整的事务如下：

begin ;

insert  into  test(cid,name ) values('00018','test00018');

select  cid,name,from test where cid='00018'for update

update test set ........

commit ;

表test 的cid列有唯一性索引cid.

应用研发执行这个事务的目的是：首先不管表test里面是否cid为00018的记录，先执行插入操作，如果不存在cid列相同的记录，则插入成功。 如果存在，则插入失败，不管插入成功还是失败，然后执行后面的update语句。 目的是，如果记录不存在，则先插入，应用执行相关处理后，然后修改状态。 如果记录存在，则修改原记录的状态。  这个逻辑看起来貌似没有啥问题，但事实上，却发生了死锁，死锁产生的原因？

       接下来，我们来分析上面的SQL，以及为什么产生死锁。

我们先来尝试场景复现。创建一个很简单的表，表上有主键跟唯一性索引，然后插入一些数据，进行测试。

表结构以及数据如下：

测试1;

首先session 1 执行

begin;

insert into test(cid,name ) values('00016','test00016');

然后session2 执行

begin;

insert into test(cid,name ) values('00016','test00016');

因为session1 已插入cid为00016记录(但未提交)，所以session2 等待。

再然后session 1 执行

select cid,name from test where cid='00016' for update;

正常加锁， session 2 继续在insert语句上等待，

并没有出现我们遇到的死锁？？？？场景还是没有复现。原因是什么？

继续测试......

测试2：

首先session 1 执行

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(cid,name ) values('00017','test00017');

ERROR 1062 (23000): Duplicate entry '00017' for key 'cid'

因为表中已经包含cid等于00017的记录，所以唯一性索引冲突。

然后session 2 执行

mysql> begin;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(cid,name ) values('00017','test00017');

ERROR 1062 (23000): Duplicate entry '00017' for key 'cid'

遇到同样的错误。

再然后session 1 执行：

select cid,name from test where cid='00017' for update;

出现锁等待。

最后session 2执行

select cid,name from test where cid='00017' for update;

出现死锁，session 2 报出dead lock .  session 1 成功执行select for update语句。

以下是session2会话的截图：

生产跟测试环境中的死锁场景复现。

进一步挖掘：为什么测试1案例没有出现死锁，而测试2案例却出现死锁？

首先分析测试1案例：

session 1插入cid等于00016的记录，该记录在表中不存在，所以成功插入，并对该记录加排他锁。

然后session 2 执行同样的操作，因为被session 1 插入cid为00016没有被提交，且被session1 持有排他锁，但该记录已经存在(虽然没有被提交)，session 2 需要加共享锁来检查(或者说确认)是否有唯一性索引冲突，排他锁跟共享锁产生冲突，所以session2 处于锁等待状态。  通过show engine innodb status 命令可以获取session2  当前的等待状态，信息如下：

如上图所示：session 2 出现锁等待，需要获取共享锁。

session 2 一直等待，直到session1 提交或者回滚，或者锁超时。 

再接下来，session 1执行select for update,因为session 1 已经获得了cid等于00016记录的排他锁，所以自然能成功执行该语句。 因为session 1不需要进行锁等待，自然不会发生死锁。

接下来分析测试2案例：

首先session 1 插入cid等于00017记录，因为该表已经存在该记录，所以报唯一性索引冲突。 但是，在检查唯一索引冲突的时候，已经在唯一性索引cid上对cid等于00017的记录加上了共享锁， SQL执行失败后，该锁并没有释放。

然后session 2 执行同样的操作，得到同样的结果，报唯一性索引冲突，因为共享锁跟共享锁不冲突，所以同样加了共享锁。 

再然后session1 执行select for update操作，需要加排他锁，因为session 2持有共享锁，堵塞session 1 对该记录加排他锁，所以出现等待。 

最后，session 2 执行select for update操作，也需要加排他锁，因为session 1 持有共享锁，所以无法加锁。 形成环状锁等待，所以报死锁错误。

   总结这个案例：导致死锁最关键的两点，1，做唯一性冲突检查时加了共享锁。2.发现有冲突时，报出唯一性索引冲突，但没有释放所加的共享锁(这个现象，在MySQL里，有较多的场景类似)，需要执行rollback 命令后才释放该锁。

        针对我们遇到这个死锁问题，在业务可以接受的条件下，最简单修正方式为.将insert语句跟select for update 用两个事物拆分开。