InnoDB中不同SQL语句设置的锁和线上唯一索引导致死锁排查

       锁定读取，更新或删除通常在处理SQL语句时在扫描的每个索引记录上设置记录锁。是否有不满足语句中WHERE条件的行排除在外并不重要。InnoDB不记得确切的WHERE条件，但只知道扫描了哪些索引范围。这些锁通常是next-key锁，也会阻止在记录之前（gap）插入，即不允许在记录的前面间隙插入。但是，可以显示禁用间隙锁定，这将导致不使用next-key锁定。InnoDB Locking详细的可以看下我的另一篇juejin.cn/post/684490…，事务隔离级别还可以影响设置的锁，详细的可以看下我的另一篇juejin.cn/post/684490…。

        如果再搜索中使用了二级索引，并且要设置的索引记录锁是排他锁（X Record Lock），InnoDB还会检索相应的聚集索引记录（主键记录）并对其设置锁。

        如果没有适合于语句的索引（相当于在sql语句中没有使用索引的字段），并且MySQL必须扫描整个表才能处理该语句，则表的每一行都将被锁定，从而阻止其他用户对表的所有插入。创建好的索引很重要，这样查询就不会不必要地扫描许多行。

        InnoDB按如下方式设置特定类型的锁。

• SELECT ... FROM是一致读取，读取数据库的快照并且不设置锁，除非事务隔离级别设置为SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的next-key锁（s next-key lock）。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁（唯一索引等值进行检索，不会出现幻读，可以直接由next-key lock退化为record lock）。
  
• SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE对于使用唯一索引的语句，获取扫描行的锁，并释放不符合结果集中包含条件的行的锁（例如，如果它们不符合WHERE子句中给定的条件）。但是，在某些情况，行可能不会立即解除锁定，因为结果行与其原始源之间的关系在查询执行期间丢失。例如，在UNION联合查询中，在评估表中的扫描（和锁定）行是否符合结果集之前，可能会将它们插入临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后才会解除锁定后一行。
  
• 对于锁定读取(SELECT with FOR UPDATE 或 FOR SHARE)，UPDATE和DELETE语句，所采用的锁定取决于该语句是使用具有唯一搜索条件的唯一索引，还是使用范围类型搜索条件。
• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不锁定其前面的间隙。
• 对于其他搜索条件和非唯一索引，InnoDB锁定扫描的索引范围，使用gap locks或next key locks来阻止其他会话插入到范围覆盖的间隙中。
  
• 对于搜索遇到的索引记录，SELECT ... FOR UPDATE阻止其他会话执行SELECT ... FOR SHARE或者从某些事务隔离级别中读取。一致读取忽略在读取视图中存在的记录上设置的任何锁。
• UPDATE ... WHERE ...在搜索遇到的每个记录上设置独占的next-key锁（在RR级别下）。但是，对于使用唯一索引搜索唯一行，只需要索引记录锁。
  
• 更新修改聚集索引（主键索引）记录时，会对受影响的二级索引记录进行隐式锁定。更新操作还对二级索引记录使用共享锁，影响在插入新的二级索引记录之前执行重复检查扫描以及插入新的二级索引记录时。
• DELETE FROM ... WHERE ...在搜索遇到的每个记录上设置独占的next-key锁（在RR级别下）。但是，对于使用唯一索引搜索唯一行，只需要索引记录锁。和UPDATE ... WHERE ...一样。
• INSERT在插入的行上设置独占锁。此锁是索引记录锁，而不是next-key锁（即没有间隙gap锁），并且不会阻止其他会话插入新的数据在插入行之前的间隙中。在插入行之前，将设置一种称为插入意向间隙锁的间隙锁（不清楚的可以看下我的另一篇juejin.cn/post/684490…）。此锁表示插入的意图，如果多个事务插入到同一索引间隙中的同一位置，则它们不必彼此等待。假设存在值为4和7的索引记录。试图插入5和6的值的单独事务在获取插入行的独占锁之前使用插入意图锁锁定4和7之间的间隙，不会相互阻塞因为行是不冲突的。如果发生重复建错误，则在重复索引记录上设置共享锁。如果有多个会话试图插入同一行（如果另一个会话已具有独占锁），则使用共享锁可能导致死锁。如果另一个会话删除行，则可能发生这种情况。假设InnoDB表t1具有以下结构：

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

• 现在假设三个会话按顺序执行以下操作：
• Session 1: 

• START TRANSACTION;
  INSERT INTO t1 VALUES(1);    
  

• Session 2: 

• START TRANSACTION;
  INSERT INTO t1 VALUES(1);

• Session 3: 

• START TRANSACTION;
  INSERT INTO t1 VALUES(1); 

• Session 1: 

• ROLLBACK;    
  

• 会话1的第一个操作获取行的独占锁。会话2和3的操作都会导致duplicate-key错误，并且它们都会请求行的共享锁。当会话1回滚时，它将释放行上的独占锁，并授予会话2和3的排队共享锁请求。此时，会话2和3死锁：两个会话都无法获取行的独占锁，因为另一个会话持有共享锁。 
•  如果表已包含键值为1的行，并且三个会话按顺序执行以下操作，则会出现类似的情况：
  
• Session 1: 

• START TRANSACTION;
  DELETE FROM t1 WHERE i = 1; 

• Session 2: 

• START TRANSACTION;
  INSERT INTO t1 VALUES(1); 

• Session 3: 

• START TRANSACTION;
  INSERT INTO t1 VALUES(1); 

• Session 1: 

• COMMIT; 

• 会话1的第一个操作获取行的独占锁。会话2和3的操作都会导致duplicate-key错误，并且它们都会请求行的共享锁。当会话1提交时，它会释放行上的排他锁，并授予会话2和3的排队共享锁请求。此时，会话2和3死锁：两个会话都无法获取行的独占锁，因为另一个会话持有共享锁。  
  
• 会话1的第一个操作获取行的独占锁。会话2和3的操作都会导致duplicate-key错误，并且会话2和3它们都会请求行的共享锁。当会话1提交时，它会释放行上的排他锁，并授予会话2和3的排队共享锁请求。此时，会话2和3死锁：两个会话都无法获取行的独占锁，因为另一个会话持有共享锁。 
• INSERT ... ON DUPLICATE KEY UPDATE与简单的插入的不同之处在于，当发生duplicate-key错误时，将在要更新的行上放置排他锁而不是共享锁。对于重复的主键值使用独占索引记录锁。对于重复的唯一键值使用独占的next-key锁。 
• 如果唯一键上没有冲突，则REPLACE的操作与INSERT操作类似。否则，将在要替换的行上放置独占的next-key锁。
• INSERT INTO T SELECT ... FROM S WHERE ... 在插入T的每一行上设置一个排他索引记录锁（不带间隙锁）。如果事务隔离级别是READ COMMITTED，InnoDB将对S进行搜索作为一致读取（不带锁）。否则，InnoDB会在S的行上设置共享的next键锁。InnoDB必须在后一种情况下设置锁：在使用基于语句的二进制日志进行前滚恢复期间，每个SQL语句必须以与最初完全相同的方式执行。 CREATE TABLE ... SELECT ...使用共享的next-key锁执行选择，或者作为一致读取执行选择。和INSERT ... SELECT一样。当在构造中SELECT替换为REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)，InnoDB在表s的行上设置共享的next-key锁。
• InnoDB在初始化表上先前指定的AUTO_INCREMENT列时，在与AUTO_INCREMENT列关联的索引末尾设置一个独占锁。 在innodb_AUTO INC_lock_mode=0的情况下，innodb使用一种特殊的AUTO-INC表锁模式，在访问自动递增计数器时，获取锁并将其保持到当前SQL语句的末尾（而不是整个事务的末尾）。持有AUTO-INC表锁时，其他客户端无法插入到表中。对于innodb_autoinc_lock_mode=1的“批量插入”，也会出现同样的行为。表级AUTO-INC锁不与innodb_autoinc_lock_mode=2一起使用。有关更多信息。 InnoDB在不设置任何锁的情况下获取先前初始化的AUTO_INCREMENT列的值。
  
• 如果在表上定义了FOREIGN KEY外键约束，则任何需要检查约束条件的插入、更新或删除操作都会对检查该约束所查看的记录设置共享记录级锁。InnoDB还会在约束失败的情况下设置这些锁。  
  
• LOCK TABLES设置表锁，但设置这些锁的是InnoDB层之上更高的MySQL层。如果InnoDB_table_locks=1（默认值）和autocommit=0，InnoDB知道表锁，并且InnoDB上面的MySQL层知道行级锁。 否则，InnoDB的自动死锁检测无法检测到涉及此类表锁的死锁。另外，因为在这种情况下，较高的MySQL层不知道行级锁，所以可以在另一个会话当前具有行级锁的表上获取表锁。这不会危及事务完整性。
• 如果innodb_table_locks=1（默认值），锁表在每个表上获取两个锁。除了MySQL层上的表锁之外，它还获取InnoDB表锁。mysql4.1.2之前的MySQL版本没有获取InnoDB表锁；可以通过设置InnoDB_table_locks=0来选择旧行为。如果没有获取InnoDB table lock，那么即使表的某些记录被其他事务锁定，锁表也会完成。 在MySQL 8.0中，innodb_table_locks=0对用锁表显式锁定的表没有影响LOCK TABLES ... WRITE。它确实对为读或写而锁定的表有影响 LOCK TABLES ... WRITE隐式（例如，通过触发器）或通过LOCK TABLES ... READ。
• 当事务被提交或中止时，事务持有的所有InnoDB锁都会被释放。因此，在autocommit=1模式下调用InnoDB表上的锁表没有多大意义，因为获取的InnoDB表锁将立即释放。
• 不能在事务中间锁定其他表，因为锁定表执行隐式提交和解锁表。   

接下来分析因为唯一索引导致的死锁的两个例子，第一个例子的死锁是真实发生在线上

看下线上死锁的日志，死锁发生在我们的member项目的wxhc_distributor表，在wxhc_distributor表中存在一个唯一键cuser_id。

transactionTemplate.execute(new TransactionCallback<Result>() {
            @Override
            public Result doInTransaction(TransactionStatus status) {
       //1、往wxhc_distributor表中插入一条数据
       distributorManager.increaseDistributor(distributorDTO); 
       //2、调用trade中的dubbo api，早上8点多account rds库进行切换，导致调用此接口会一直等待到超时，导致长事务
       createUserAccount(userAccountDTO);
       //3、省略后续的一些插入、更新、查询操作
})

看下阿里云的日志，出现的现象是cuser_id等于48929303记录已经存在Duplicate的错误

本地复现下这个场景，先创建一张yangzai表

CREATE TABLE `yangzai`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(255) NULL DEFAULT NULL,
  `b` int(255) NULL DEFAULT NULL,
  `c` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `un_a`(`a`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

表中已经存在两条记录

开启三个会话，每个会话开启一个事务，现在假设三个会话按顺序执行以下操作：

Session1:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (7,2,3);

Session2:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (7,2,3);

Session3:

BEGIN;
INSERT into yangzai(a,b,c) VALUES (6,2,3);

Session1:

-- 提交7这行数据
COMMIT;

Session1开启事务执行插入操作，会先使用读，判断a这个唯一键是否冲突，由于表中没有这行数据，接下来会加排他的插入意向锁，最后会在a=7这行加上排他的记录锁。

Session2也开启事务执行插入操作和Session1插入的数据一致，Session2也判断a这个唯一键是否冲突，由于Session1也插入这条数据，出现了唯一键冲突，但是由于Session1还未提交，Session2不会报Duplicate key的错误，但是Session2会尝试往a=7的这行唯一索引记录加共享的记录锁，但是由于a=7的排他锁被Session1获取，导致Session2尝试获取a=7的共享记录锁等待，由于Session2在等待a=7的共享记录锁，就不会获取立即获取排他的插入意向锁。

Session3开启事务执行插入操作，由于唯一键检查是否冲突也是使用gap间隙锁，Session3会尝试在a等于（5，正无穷）加上gap锁，但是gap会等待，因为Session1已经在a=7这行加上排他的记录锁，Session2等待获取a=7的共享记录锁。

Session1开始执行COMMIT操作，由于Session1的事务已经提交会释放a=7这行的排他记录锁，Session2获取到a=7的共享记录锁，然后Session2会报1062 - Duplicate entry '7' for key 'un_a', Time: 8.616000s，但是在Session2中的事务还是会持有a=7的共享记录锁，Session3会一直等待，Session3的（5，正无穷）gap锁无法加成功，因为Session2中的事务还是会持有a=7的共享记录锁。线上出现的问题也是一样事务1因为dubbo api调用导致事务未提交占有48929303的排他记录锁，事务2尝试重复插入同一条数据由于Duplicate key尝试获取48929303的共享记录锁等待，事务3插入cuser_id等于48929270的数据会尝试加插入间隙的gap锁，会等待因为事务1占有48929303的排他记录锁，事务2获取48929303的共享记录锁等待，导致事务3的gap等待，由于mysql的保护机制会自动检查是否形成死锁

接下来我们看下本地复现的三个事务所占有锁的情况，在Session1还未提交事务

接下来我们看下第二个例子也是因为唯一键导致的死锁，是由于插入同一条唯一索引数据，先创建一张test表，a是主键索引，字段b和c是联合唯一索引

CREATE TABLE `itemcenter`.`Untitled`  (
  `a` int(11) NOT NULL DEFAULT 0,
  `b` int(11) NULL DEFAULT NULL,
  `c` int(11) NULL DEFAULT NULL,
  `d` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`a`) USING BTREE,
  UNIQUE INDEX `uk_bc`(`b`, `c`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

Session1:

BEGIN;
insert into test values(100213,215,215,312);

Session2:

BEGIN;
insert into test values(100214,215,215,312);

Session3:

BEGIN;
insert into test values(100215,215,215,312);

Session1:

ROLLBACK;

Session1开启一个事务插入唯一索引（b,c）数据是215,215，唯一索引插入需要检验是否有出现唯一键冲突，由于表中不存在这条数据，Session1中的事务会加一个排他的插入意向锁，然后持有（b,c）数据是215,215这条记录的排他记录锁。

Session2、Session3插入的数据和Session1一样，Session2、Session3都需要检验插入唯一索引是否有出现唯一键冲突，由于Session1已经开启事务插入一条数据，导致Session2、Session3中的事务唯一键冲突，Session2、Session3都会尝试加（b,c）数据是215,215这条记录的共享记录锁，都会等待，由于Session1已经持有（b,c）数据是215,215这条记录的排他记录锁。

session1 回滚，Session2、Session3都会加上（b,c）数据是215,215这条记录的共享记录锁。这时候，session2 和 session3 都试图继续执行插入操作，都要加上写锁。两个 session都要等待对方的行锁，所以就出现了死锁。

接下来我们看下本地复现的三个事务所占有锁的情况，在Session1还未提交事务

写的不对的地方希望大家能帮忙指正出来。