0 環(huán)境

　　項目初期沒做讀寫分離，基于一個主庫完成讀寫。業(yè)務量逐漸增大，偶爾收到系統(tǒng)異常報警信息，DBA 通知我們數(shù)據(jù)庫出現(xiàn)死鎖異常。

　　業(yè)務簡單，就新增訂單、修改訂單、查詢訂單等，為啥會死鎖？日志分析，發(fā)現(xiàn)是作為冪等性校驗的一張表經(jīng)常出現(xiàn)死鎖異常。懷疑是索引導致死鎖。后來開發(fā)環(huán)境模擬相關(guān)操作，復現(xiàn)了。

　　1 復現(xiàn)

　　創(chuàng)建一張訂單記錄表，用于校驗訂單重復創(chuàng)建：

　　CREATE TABLE `order_record` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_no` int(11) DEFAULT NULL, `status` int(4) DEFAULT NULL, `create_date` datetime(0) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_order_status`(`order_no`,`status`) USING BTREE ) ENGINE = InnoDB

　　先將事務設置為手動提交。MySQL 和 Oracle 提交事務不太一樣，MySQL 默認自動提交事務，可通過以下命令行查看自動提交事務是否開啟：

　　mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec)

　　先將 MySQL 數(shù)據(jù)庫的事務提交設置為手動提交，關(guān)閉自動提交事務：

　　mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)

　　訂單做冪等性校驗時，先通過訂單號檢查訂單是否存在，如不存在則新增訂單記錄。

　　模擬創(chuàng)建產(chǎn)生死鎖的運行 SQL 語句。先模擬新建兩個訂單，并按以下順序執(zhí)行冪等性校驗 SQL 語句（垂直方向代表執(zhí)行的時間順序）：

　　任務A 任務B BEGIN; BEGIN; SELECT id FROMorder_recordWHEREorder_no= 4 FOR UPDATE;//檢查是否存在 order_no等于4的訂單

　　SELECT id FROMorder_recordWHEREorder_no= 5 FOR UPDATE;//檢查是否存在 order_no等于5的訂單 INSERT INTOorder_record(order_no,status,create_date) VALUES (4,1,'2019-07-13 10:57:03');//如果沒有，則插入信息

　　此時，鎖等待中......

　　INSERT INTOorder_record(order_no,status,create_date) VALUES (5,1,'2019-07-13 10:57:03');//如果沒有，則插入信息

　　此時，鎖等待中......

　　COMMIT;(未完成) COMMIT;(未完成)

　　此時，發(fā)現(xiàn)兩個事務已進入死鎖狀態(tài)?？稍?information_schema 數(shù)據(jù)庫中查詢到具體的死鎖情況：

　　為啥 SELECT 要加 for update 排他鎖，而不用共享鎖？試想若兩個訂單號一樣的請求同時進來，就有可能出現(xiàn)幻讀。即一開始事務 A 中的查詢無該訂單號，后來事務 B 新增一個該訂單號的記錄，此時事務 A 再新增一條該訂單號記錄，就會創(chuàng)建重復的訂單記錄。面對這種情況，可用鎖間隙算法防止幻讀。

　　2 死鎖咋產(chǎn)生的？

　　33講過并發(fā)事務中的鎖機制以及行鎖的具體實現(xiàn)算法。

　　行鎖的實現(xiàn)算法：

　　record lock，對索引項加鎖

　　gap lock，對索引項之間的間隙加鎖

　　next-key lock，前面兩種的組合，對索引項以其之間的間隙加鎖

　　只在可重復讀或以上隔離級別下的特定操作才會取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 時，除了基于唯一索引的查詢之外，其它索引查詢時都會獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。主鍵索引也屬于唯一索引，所以主鍵索引是不會使用 gap lock 或 next-key lock。

　　MySQL gap lock 默認開啟，即 innodb_locks_unsafe_for_binlog 參數(shù)值 disable，且 MySQL 默認 RR。

　　執(zhí)行以下查詢 SQL 時，由于 order_no 列為非唯一索引，此時又是 RR，所以 SELECT 的加鎖類型為 gap lock，gap 范圍 (4,+∞）。

　　SELECT id FROM `demo`.`order_record` where `order_no` = 4 for update;

　　執(zhí)行查詢 SQL 語句獲取的 gap lock 并不會導致阻塞，而當我們執(zhí)行以下插入 SQL 時，會在插入間隙上再次獲取插入意向鎖。插入意向鎖其實也是一種 gap 鎖，它與 gap lock 是沖突的，所以當其它事務持有該間隙的 gap lock 時，需要等待其它事務釋放 gap lock 之后，才能獲取到插入意向鎖。

　　事務 A、B 都持有間隙 (4,+∞）的 gap 鎖，而接下來的插入操作為獲取到插入意向鎖，都在等待對方事務的 gap 鎖釋放，造成循環(huán)等待，導致死鎖。

　　INSERT INTO `demo`.`order_record`(`order_no`, `status`, `create_date`) VALUES (5, 1, ‘2019-07-13 10:57:03’);

　　可通過以下鎖的兼容矩陣圖，看鎖的兼容性：

　　
Gap Insert Intention Record Next-Key Gap 兼容 沖突 兼容 兼容 Insert Intention 沖突 兼容 兼容 沖突 Record 兼容 兼容 沖突 沖突 Next-Key 兼容 兼容 沖突 沖突 備注 橫向是已經(jīng)持有的鎖，縱向是正在請求的鎖。

　　3 避免死鎖

　　知道了死鎖問題源自哪兒，就可以找到合適的方法來避免它了。

　　避免死鎖最直觀的方法就是在兩個事務相互等待時，當一個事務的等待時間超過設置的某一閾值，就對這個事務進行回滾，另一個事務就可以繼續(xù)執(zhí)行了。這種方法簡單有效，在 InnoDB 中，參數(shù) innodb_lock_wait_timeout 是用來設置超時時間的。

　　另外，我們還可以將 order_no 列設置為唯一索引列。雖然不能防止幻讀，但我們可以利用它的唯一性來保證訂單記錄不重復創(chuàng)建，這種方式唯一的缺點就是當遇到重復創(chuàng)建訂單時會拋出異常。

　　我們還可以使用其它的方式來代替數(shù)據(jù)庫實現(xiàn)冪等性校驗。例如，使用 Redis 以及 ZooKeeper 來實現(xiàn)，運行效率比數(shù)據(jù)庫更佳。

　　4 其它常見 SQL 死鎖問題

　　這里再補充一些常見的 SQL 死鎖問題，以便你遇到時也能知道其原因，從而順利解決。

　　我們知道死鎖的四個必要條件：互斥、占有且等待、不可強占用、循環(huán)等待。只要系統(tǒng)發(fā)生死鎖，這些條件必然成立。所以在一些經(jīng)常需要使用互斥共用一些資源，且有可能循環(huán)等待的業(yè)務場景中，要特別注意死鎖問題。

　　接下來，我們再來了解一個出現(xiàn)死鎖的場景。

　　我們講過，InnoDB 存儲引擎的主鍵索引為聚簇索引，其它索引為輔助索引。如果使用輔助索引來更新數(shù)據(jù)庫，就需要使用聚簇索引來更新數(shù)據(jù)庫字段。如果兩個更新事務使用了不同的輔助索引，或一個使用了輔助索引，一個使用了聚簇索引，就都有可能導致鎖資源的循環(huán)等待。由于本身兩個事務是互斥，也就構(gòu)成了以上死鎖的四個必要條件了。

　　我們還是以上面的這個訂單記錄表來重現(xiàn)下聚簇索引和輔助索引更新時，循環(huán)等待鎖資源導致的死鎖問題：

　　事務A 事務B BEGIN; BEGIN; UPDATEorder_recordSET status = 1 WHEREorder_no= 4 ; UPDATEorder_recordSET status = 1 WHERE id = 4 ;

　　出現(xiàn)死鎖的步驟：

　　事務A 事務B 首先獲取idx_order_status非聚簇索引

　　獲取主鍵索引的行鎖 根據(jù)非聚族索引獲取的主鍵，獲取主鍵索引的行鎖

　　更新status列時，需要獲取idx_order_status非聚簇索引

　　綜上，更新操作時，我們應該盡量使用主鍵來更新表字段，這樣可以有效避免一些不必要的死鎖發(fā)生。

　　5 總結(jié)

　　數(shù)據(jù)庫發(fā)生死鎖的概率并不是很大，一旦遇到了，就一定要徹查具體原因，盡快找出解決方案，老實說，過程不簡單。我們只有先對 MySQL 的 InnoDB 存儲引擎有足夠的了解，才能剖析出造成死鎖的具體原因。

　　例如，以上我例舉的兩種發(fā)生死鎖的場景，一個考驗的是我們對鎖算法的了解，另外一個考驗則是我們對聚簇索引和輔助索引的熟悉程度。

　　解決死鎖的最佳方式當然就是預防死鎖的發(fā)生了，我們平時編程中，可以通過以下一些常規(guī)手段來預防死鎖的發(fā)生：

　　在編程中盡量按照固定的順序來處理數(shù)據(jù)庫記錄，假設有兩個更新操作，分別更新兩條相同的記錄，但更新順序不一樣，有可能導致死鎖；

　　在允許幻讀和不可重復讀的情況下，盡量使用 RC 事務隔離級別，可以避免 gap lock 導致的死鎖問題；

　　更新表時，盡量使用主鍵更新；

　　避免長事務，盡量將長事務拆解，可以降低與其它事務發(fā)生沖突的概率；

　　設置鎖等待超時參數(shù)，我們可以通過 innodb_lock_wait_timeout 設置合理的等待超時閾值，特別是在一些高并發(fā)的業(yè)務中，我們可以盡量將該值設置得小一些，避免大量事務等待，占用系統(tǒng)資源，造成嚴重的性能開銷。

　　FAQ

　　除了設置 innodb_lock_wait_timeout 參數(shù)來避免已經(jīng)產(chǎn)生死鎖的 SQL 長時間等待，你還知道其它方法來解決類似問題嗎？