本文介紹了為什么當出現復制密鑰錯誤時，MySQL InnoDB會在復制索引記錄上設置S或X Next-Key鎖？的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
提到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鎖？的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
