并发插入duplicate-key导致的死锁

       当mysql死锁时，可以通过show engine innodb status来查看死锁日志，可以看到发生死锁的sql。

        复现一下:

        先创建一个表

         

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

        session1

START TRANSACTION;
INSERT INTO t1 VALUES(1);

         session2

START TRANSACTION;
INSERT INTO t1 VALUES(1);

         session3

START TRANSACTION;
INSERT INTO t1 VALUES(1);

      然后session1执行

ROLLBACK;

     然后就会产生死锁了.

关键在于

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock