MySQL 死鎖 - 訪問不同的主鍵值也會造成死鎖

我瀏覽了 MySQL 日誌並找到了死鎖的原因

第一個執行緒正在嘗試執行以下查詢。哪個正在等待執行緒 2。

UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

內部查詢 ( SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19)) 的結果是M_ID = 3562。

第二個執行緒正在執行下面的查詢。

DELETE FROM M_SAMP WHERE M_ID=3455

M_SAMPno foreign key和 MM_RVW_SAMP 表中定義了關係。M_ID 是 M_SAMP 表的主鍵。和引擎是InnoDB。這個問題正在重複。

誰能幫助我，由於發生了死鎖，如何授予鎖？

日誌

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-23 10:52:28 de8
*** (1) TRANSACTION:
TRANSACTION 13275344, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 25 lock struct(s), heap size 2408, 1394 row lock(s), undo log entries 1
MySQL thread id 1455, OS thread handle 0x11ac, query id 39660 xyz 192.168.1.108 userName Sending data
UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275344 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 13275335, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
254 lock struct(s), heap size 27512, 21595 row lock(s), undo log entries 7
MySQL thread id 1458, OS thread handle 0xde8, query id 39664 xyz 192.168.1.108 userName updating
DELETE FROM M_SAMP WHERE M_ID=3455
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275335 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0

謝謝你。

更新：

M_SAMP表中的記錄數is3524和 in MM_RVW_SAMPis 2。

下面是EXPLAIN查詢的結果。

EXPLAIN UPDATE M_SAMP SET MM_Q_IND=1 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

因此，我將回答“為什麼它會在整個查詢中產生問題”這個問題：

在 MySQL 的REPEATABLE-READ事務隔離模式下，不會出現虛行。為了做到這一點，MySQL 不僅鎖定行，還鎖定它們之間的間隙（也稱為下一個鍵鎖定）。

由於 MySQL 碰巧正在使用索引掃描對第一個表執行讀取，因此 MySQL 認為的間隙可能比預期的要大，因為它使用主鍵來檢查是否沒有插入可能影響該特定查詢的新行掃描類型。因此，表的很大一部分可能會受到影響。

減少事務隔離模式應該避免間隙鎖定，並且只鎖定正在更新的特定行並提高一般並發性，同時避免死鎖，因為該特定查詢僅影響一行。

**更新：**似乎較低的隔離級別並不能修復更新+子選擇的鎖定。將其重寫為 JOIN 確實可以解決問題嗎？（也許 UPDATE 與 SELECT 有不同的優化？）。JOIN 重寫的想法是這樣做的：

UPDATE M_SAMP M 
JOIN MM_RVW_SAMP MM 
ON M.M_ID = MM. M_ID 
SET M.MM_Q_IND = 1 
WHERE MM.TARGET_M_ID = 19;

引用自：https://dba.stackexchange.com/questions/93545