摘要: 因同事多線程下在同一個事務進行刪除和插入操作導致數據庫死鎖,想解決此問題就要先了解innodb lock機制,本文將通過案例重現,結合innodb lock機制進行剖析。如有不當之處，望大神指正補充
目錄
- 概述
- 問題重現與解析
- Innodb lock機制
概述
正在奮力寫"BUG"中,同事說出現死鎖了,讓幫忙看一下,so 放下手中工作,去瞅瞅, 一段很簡單的代碼,就是mysql數據庫下使用多線程先刪除數據再插入數據,造成了死鎖
順便騙個贊，覺得寫的還可以的，不要吝嗇你的贊喲
問題重現與解析
代碼
代碼解釋:
第一張圖,同事採用發令槍,多線程批量處理一批數據
第二張圖是批量處理數據的邏輯, 在一個事務中,先刪除一條數據然後再插入一批數據
異常提示
堆棧異常: Deadlock found when trying to get lock; try restarting transaction; 意思很明瞭,就是嘗試獲取鎖時發現死鎖.
疑惑
這段代碼做刪除時,使用的索引列,憑直觀理解,這應該只是個行級鎖, 不應該會出現死鎖的,看代碼很清楚的理解到,每個線程持有一個事務操作,如果是行級鎖肯定不會出現死鎖,所以刪除的時候肯定不止鎖了一條數據
撥雲見日
在生產環境中mysql的存儲引擎絕大部分是 InnoDB ，為什麼使用 InnoDB 呢? 點擊查看詳細答案， 就憑事務安全這一條，相信足以讓選擇InnoDb了。
代碼中delete操作的是非唯一索引列在innoDB引擎下會觸發 next-key lock（間隙鎖）。
舉例： 表t中有非唯一索引列 test_id為 1, 10, 18, 22, 26的5條數據，此時模擬操作:
事務A 刪除一條不存在的數據，數據庫就會去找從左開始找最近的索引值
delete from t where test_id= 27;
事務B 刪除一條不存在的數據，數據庫就會去找從左開始找最近的索引值
delete from t where test_id= 28;
此時事務A和B就會分別產生一個(26,正無窮)間隙鎖,然後繼續操作
事務A
INSERT INTO t VALUES(27);
此時事務A阻塞,因為事務B在刪除操作時擁有了區間鎖
事務B
INSERT INTO t VALUES(28);
此時事務B就會死鎖,因為事務A在刪除操作時擁有了區間鎖
解決之道
1、刪除時先判斷數據是否存在
2、刪除和插入分兩個事務處理
3、將事務隔離級別設置為讀已提交
關於InndDb的Lock機制解釋此問題,詳見下章
Innodb lock機制
Innodb鎖類型
- 共享和排他(獨佔)鎖
共享鎖允許持有鎖的事務讀取一行數據
排他(獨佔)鎖 允許持有鎖的事務更新或者刪除一行數據
- 意圖鎖
innodb支持多重粒度鎖，即行鎖和表鎖共存
意圖共享鎖（IS）表明一個事務對錶中個別數據設置一個共享鎖
意圖排他鎖（IX）表明一個事務對錶中個別數據設置一個排他鎖
舉例：
SELECT ... LOCK IN SHARE MODE 將會對查詢的表設置意圖共享（IS）鎖， SELECT ... FOR UPDATE 將會對查詢的表設置意圖排他鎖
- 記錄鎖
記錄鎖是一個索引記錄的鎖。索引記錄不一定只是一條數據喲。
示例： SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 會對錶t中c1=10的記錄們（此處可以是多條數據）加記錄鎖，以免其他事務對c1=10的數據們做插入、更新、刪除操作。即使表t沒有索引，InnoDb也會創建一個隱藏的索引群組。
- Gap Locks（區間鎖）
區間鎖是一種索引記錄之間或者某索引記錄之前或者 某索引記錄之後的鎖。區間鎖可以只跨越一個索引值，也可以是多個索引值，也可以只是個空區間。當事務隔離級別為 讀已提交或者啟用配置 innodb_locks_unsafe_for_binlog （此配置現已禁用）時，在搜索或者索引掃描是 區間鎖是禁用的。
舉例0： SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事務插入t.c1=15這樣的值，無論是否有這樣的記錄，因為鎖定了t.c1=10到t.c1=20的區間。
舉例1：
SELECT * FROM child WHERE id = 100;
如果id是一個唯一索引列，以上語句只會產生一個id=100 的鎖，如果id沒有索引或者不是唯一索引，這條語句將會鎖定 100以前的區間
- Next-Key Locks （間隙鎖）
間隙鎖是一種記錄鎖和區間（索引之前）鎖的組合鎖。本文例子的問題根源就是這個
在搜索或者掃描索引時，InnoDb通過這種方式提供了行級鎖，它在找到的索引記錄上設置共享鎖或者排他鎖。行級鎖就是索引記錄鎖。一個索引記錄的間隙鎖也會是個索引之前的區間鎖。如果一個會話在索引中有共享或者排他鎖，其他會話就不能在這個索引順序之前的區間插入新的索引記錄。
舉例：加入一個索引包含了10，11,13,20 。間隙鎖包含以下場景：
(負無窮, 10](10, 11](11, 13](13, 20](20, 正無窮)
默認情況下，InnoDb在可重複讀 事務隔離級別下使用此鎖，通過 SHOW ENGINE INNODB STATUS 命令可以查看事務處理數據的間隙鎖狀態。
- 插入意圖鎖
插入意圖鎖是在執行插入操作時的一種區間鎖。此鎖是為了多個事務在同一個索引區間鎖之間插入時不必等待。
示例：
如果有兩個索引記錄為90和120
在客戶端A 開啟事務執行命令
SELECT * FROM child WHERE id > 100 FOR UPDATE;
此語句會產生一個在120之前的區間鎖
在客戶端B 開啟事務執行命令
INSERT INTO child (id) VALUES (101);
此條命令在120之前的區間鎖之間插入一條數據，該事務在獲取排他鎖的同時，產生一個插入意圖鎖
- 自增鎖
自增鎖是一個事務在自增列中插入數據時產生的一種特殊的表級鎖。簡單的情況下，一個事務在表裡插入數據，其他事務都必須等待往表裡插自己的數據， 所以第一個事務接收連續的主鍵值插入到數據行。
innodb_autoinc_lock_mode 配置項可以控制自增鎖的算法，它允許在做插入操作時如何權衡自動增量值序列和插入操作的最大併發性。點擊查看此配置詳細信息
不同sql在InnoDb下產生的鎖
在查詢、更新、刪除語句執行過程中通常會對每個掃描過的索引設置記錄鎖。在where語句中是否含有排除數據的條件並不重要，InnoDb不會記錄具體的where條件，只知道掃描了哪些索引。這鎖通常是間隙鎖（ next-key locks ），它會阻塞在記錄之前的區間插入新數據，然而區間鎖是可以被明確的禁用，這將使間隙鎖失效。事務隔離級別也會影響鎖。
如果二級索引被用於搜索和索引記錄就會設置一個排他鎖，InnoDB也會檢索相應的群集索引記錄，並對其設置鎖。
如果sql語句沒有合適的索引，mysql就會整張表，表的每一行都會被鎖，就會阻塞其他用戶往此表插入數據，所以建立一個適當的索引很重要。
InnoDb引擎中不同sql語句設置鎖如下：
- SELECT ... FROM 一個讀語句，讀取數據庫的快照過程中不會設置鎖，除非將事務隔離級別設置為SERIALIZABLE。
- SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE, 會為每個掃描到的行設置鎖，並且為不符合條件的數據釋放鎖，有時候，不會立刻釋放鎖，因為在查詢的時候丟失了結果數據和源數據的關聯，比如在一個 Union語句中，從一張表中掃描（和鎖定）行被插入到一個臨時表中，然後再評估它們是否符合結果集。 在這種情況下，在臨時表中的行與原始表中的行之間的關係丟失，而後者直到查詢執行結束時才會解鎖。
- SELECT ... LOCK IN SHARE MODE 語句在所有搜索到的索引設置共享間隙鎖，然而，通過一個唯一索引搜索到唯一行就會產生一個索引記錄鎖。
- SELECT ... FOR UPDATE ， 語句在所有搜索到的索引設置排他（獨佔）間隙鎖 。然而，通過一個唯一索引搜索到唯一行就會產生一個索引記錄鎖。
對於每個搜索到的索引， SELECT ... FOR UPDATE 阻塞了 SELECT ... LOCK IN SHARE MODE 或者在某一事務隔離級別讀的其他會話，一般讀語句都會忽略讀視圖中存在記錄的任何鎖。
- DELETE FROM ... WHERE ... 和 UPDATE ... WHERE ... 在搜索遇到的每條記錄上設置一個獨佔的間隙鎖。 然而，通過一個唯一索引搜索到唯一行就會產生一個索引記錄鎖。
- INSERT 語句對插入的行設置排他（獨佔）鎖。這個鎖是一個索引記錄鎖，而不是間隙鎖，並且不會阻止其他會話在插入的行之前區間中插入數據。
閱讀更多 混在軟件圈 的文章
