为什么当出现复制密钥错误时，MySQL InnoDB会在复制索引记录上设置S或X Next-Key锁？ [英] Why MySQL InnoDB set an S or X Next-Key lock on the duplicate index record when a duplicate-key error occurs?

问题描述

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

如果发生复制密钥错误，则在复制索引记录上设置共享锁。如果有多个会话在另一个会话已具有排他锁的情况下尝试插入同一行，则使用共享锁可能会导致死锁。...

...

插入...ON DUPLICATE KEY UPDATE与简单INSERT的不同之处在于，当发生重复键错误时，将独占锁而不是共享锁放置在要更新的行上。

并且我已经阅读了源代码(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 duplicate key error causes a shared lock set on the duplicate index record?)。

首先，容易导致死锁，同一个MySQL文档显示了两个关于死锁的例子。

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

例如

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 

推荐答案

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

示例：您运行的INSERT查询会导致重复键错误。如果您重试该插入查询，它将再次失败，并返回相同的错误。

但是，如果另一个会话更新了导致冲突的行并更改了唯一值，该怎么办？然后，如果您重试插入，它将成功，这是意想不到的。

当您的语句处于锁定状态时，InnoDB无法实现真正的可重复读取事务。例如，INSERT/UPDATE/DELETE，甚至使用锁定选项选择UPDATE、FOR SHARE或LOCK IN SHARE模式。在InnoDB中锁定SQL语句始终作用于最新提交的行版本，而不是您的会话可见的该行版本。

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

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

这篇关于为什么当出现复制密钥错误时，MySQL InnoDB会在复制索引记录上设置S或X Next-Key锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！