为什么MySQL InnoDB在发生重复键错误时在重复索引记录上设置S或X下键锁？

Question

MySQL文档(https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)

如果发生重复键错误，则在重复索引记录上设置共享锁.如果有多个会话试图插入同一行(如果另一个会话已经具有独占锁)，则共享锁的这种使用会导致死锁。..。 ..。 插入..。在重复键更新中，与简单插入不同的是，当发生重复键错误时，将独占锁而不是共享锁放置在要更新的行上。

我已经阅读了相应的源代码(https://github.com/mysql/mysql-server/blob/f8cdce86448a211511e8a039c62580ae16cb96f5/storage/innobase/row/row0ins.cc#L1930)，在出现重复键错误时，InnoDB确实设置了S或X锁。

if (flags & BTR_NO_LOCKING_FLAG) {
    /* Set no locks when applying log
    in online table rebuild. */
} else if (allow_duplicates) {
... ...
      
    /* If the SQL-query will update or replace duplicate key we will take
     X-lock for duplicates ( REPLACE, LOAD DATAFILE REPLACE, INSERT ON
     DUPLICATE KEY UPDATE). */
    err = row_ins_set_rec_lock(LOCK_X, lock_type, block, rec, index, offsets, thr);
 } else {
... ...
    err = row_ins_set_rec_lock(LOCK_S, lock_type, block, rec, index, offsets, thr);
}

但是我想知道为什么InnoDB必须设置这样的锁，似乎这些锁会带来比它们解决的更多的问题(他们解决了这个问题：MySQL重复键错误导致在重复索引记录上设置共享锁？)。

首先，它很容易导致死锁，相同的MySQL文档给出了两个关于死锁的例子。

更糟糕的是，S或X锁不是单一索引记录锁，它是Next键锁，可能拒绝插入许多值，而不仅仅是一个重复的值。

例如：

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4

mysql> select * from t;
+----+------+------+
| id | c    | d    |
+----+------+------+
| 30 |   10 |   10 |
| 36 |  100 |  100 |
+----+------+------+

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.41 sec)

# Transaction 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (null, 100, 100);
ERROR 1062 (23000): Duplicate entry '100' for key 't.uniq_idx_c'

# not commit

# Transcation 2
mysql> insert into t values(null, 95, 95);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(null, 20, 20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(null, 50, 50);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

# All c in [10, 100] can not be inserted 

Answer 1

ACID数据库中的目标是，如果您再次尝试运行查询，则会话中的查询具有相同的结果。

示例:运行一个INSERT查询，该查询将导致重复的键错误。如果重试该INSERT查询，您可能会期望该查询在相同的错误情况下再次失败。

但是，如果另一个会话更新引起冲突的行并更改唯一值怎么办？然后，如果您重新尝试插入，它将成功，这是意外的。

当您的语句锁定时，InnoDB无法实现真正的可重复读取事务。例如，INSERT/ UPDATE /DELETE，甚至是带有更新、共享或锁定共享模式的锁定选项的选择。锁定InnoDB中的SQL语句总是针对最新提交的行版本，而不是会话可见的该行版本。

那么，InnoDB如何模拟可重复读取，以确保受锁定语句影响的行与最新提交的行相同？

通过锁定被锁定语句间接引用的行，防止它们被其他并发会话更改。

Answer 2

我在MySQL源代码中找到的另一个可能的解释是row0ins.cc 2141线。

我们对可能的重复设置了一个锁:在MySQL的逻辑日志记录中需要这样做，以确保在前滚过程中获得与原始执行中相同的重复错误。