title: Why MySQL InnoDB set an S or X Next-Key lock on the duplicate index record when a duplicate-key error occurs?
body: <p>MySQL document (<a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html</a>) mentioned,</p>
  <blockquote>
  <p>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. ...</p>
  <p>...</p>
  <p>INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs.</p>
  </blockquote>
  <p>and I've read the source code(<a href="https://github.com/mysql/mysql-server/blob/f8cdce86448a211511e8a039c62580ae16cb96f5/storage/innobase/row/row0ins.cc#L1930" rel="nofollow noreferrer">https://github.com/mysql/mysql-server/blob/f8cdce86448a211511e8a039c62580ae16cb96f5/storage/innobase/row/row0ins.cc#L1930</a>) that corresponding this situation, InnoDB indeed set the S or X lock when a duplicate-key error occurs.</p>
  <pre class="lang-cpp prettyprint-override"><code>if (flags &amp; 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);
  }
  
  </code></pre>
  <p>But I wonder why InnoDB has to set such locks, it seems that these locks will bring more problems than they solve(they solved this problem: <a href="https://stackoverflow.com/questions/21111676/mysql-duplicate-key-error-causes-a-shared-lock-set-on-the-duplicate-index-record">MySQL duplicate key error causes a shared lock set on the duplicate index record?</a>).</p>
  <p>Firstly, it can result in deadlock easily, the same MySQL document showed 2 examples about the deadlock.</p>
  <p>Worse, the S or X lock is not single index-record lock, it is <strong>Next Key lock</strong> and may refuse many values to be inserted rather than just one duplicated value.</p>
  <p>e.g.</p>
  <pre class="lang-sql prettyprint-override"><code>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&gt; select * from t;
  +----+------+------+
  | id | c    | d    |
  +----+------+------+
  | 30 |   10 |   10 |
  | 36 |  100 |  100 |
  +----+------+------+
  
  mysql&gt; show variables like '%iso%';
  +-----------------------+-----------------+
  | Variable_name         | Value           |
  +-----------------------+-----------------+
  | transaction_isolation | REPEATABLE-READ |
  +-----------------------+-----------------+
  1 row in set (0.41 sec)
  
  # Transaction 1
  mysql&gt; begin;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; insert into t values (null, 100, 100);
  ERROR 1062 (23000): Duplicate entry '100' for key 't.uniq_idx_c'
  
  # not commit
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code># Transcation 2
  mysql&gt; insert into t values(null, 95, 95);
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  mysql&gt; insert into t values(null, 20, 20);
  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  mysql&gt; 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 
  </code></pre>