高并發場景下的MySQL幾類死鎖事故案例分析
本文主要講解MySQL中出現死鎖的應用案例，以及相關的業務場景，不會純講理論，希望對這塊感興趣的朋友可以有所幫助。
什么是死鎖
多個線程在訪問某些資源的時候，需要等待對方釋放彼此所需資源，而進入了等待互斥的狀態。
通俗一些來說，A線程持有B鎖，然后想要訪問A鎖，此時B線程持有A鎖，想要訪問B鎖，這種情況下就容易出現死鎖。
MySQL中鎖的類型有哪些？
下邊我們以用戶消息表案例來進行說明：
CREATE TABLE `t_user_message` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '發信方id',
`object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id',
`relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '關聯id',
`is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已讀（0未讀，1已讀）',
`sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息條數',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '狀態（0無效 1有效）',
`content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息內容',
`type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '類型（0文本，1語音，2圖片，3視頻，4表情，5分享鏈接）',
`ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '擴展字段',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '發信方id索引',
KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引',
KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '關聯id索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用戶消息表';按照鎖的粒度來區分，可以分為以下兩種：
行鎖
只會鎖住特定的行記錄,例如下邊這條sql：
select * from t_user_message where user_id=1001 for update;表鎖
會把整個表的數據給鎖住，性能較差,例如下邊這條sql：
select * from t_user_message for update;排它鎖和共享鎖的區別
共享鎖
在多個事務里面都可以讀取共享鎖所鎖住的內容。
排它鎖
只能在一個事務里對同樣的數據進行加鎖，假設A事務對某行數據加入了排它鎖之后，其他事務就無法再對該行記錄加入排它鎖。
關于排它鎖和共享鎖的使用
看到這里，你可能對共享鎖和排它鎖并不是理解得很徹底，那么先別著急，我們先從實戰來加深下你對它的理解。
排它鎖
在Innodb存儲引擎中，常見的update，insert，delete這些sql都會默認加入上排他鎖，而我們的select語句如果沒有加入特殊關鍵字（下邊會講是什么樣的特殊關鍵字） ，是不會加入排他鎖的。
如果select語句希望加入排它鎖，那么可以嘗試以下方式：
使用 for update 關鍵字
select * from t_user_message for update;共享鎖
在正常的select語句中，是不會有加鎖的，例如下邊這條sql：
select * from t_user_message;這條sql在innodb中，默認是不會鎖表，也不會鎖行記錄。如果你希望加上一把共享鎖，那么可以嘗試以下的這種寫法：
使用 lock in share mode 關鍵字
select * from t_user_message lock in share mode;lock in share mode 和 for update使用起來有什么區別？
來看看這個案例，我們準備了兩個MySQL的會話窗口。
lock in share mode 測試
先來看會話A：會話A中，關閉了自動提交功能，然后執行這個lock in share mode的鎖，此時它使用了共享鎖鎖住了全表的內容。
圖片
再來看會話B：會話B中也是相同的，關閉自動提交后，執行lock in share mode的共享鎖，發現依然可以正常查詢，沒有堵塞行為。
圖片
這時候我們將會話B的當前事務先提交，然后在會話B中繼續執行一條update語句（非事務狀態下） ，要知道update是默認帶了拍它鎖的，此時因為我們的會話A沒有commit，所以會話B的這條update操作會進入堵塞的狀態，如下圖：
圖片
只有當會話A的事務執行完畢了，將lock in share mode的鎖給釋放掉，會話B才會繼續執行。
for update測試
下邊讓我們來看看 for update 加鎖的影響，會話A關閉了自動提交，然后執行了一條for update的sql，但是沒有commit；此時我們的會話B也開始了同樣的步驟，但是卻卡住了。
圖片
假如事務A一直都不提交的話，那么事務B最終會報出以下異常：
圖片
[查詢2中發生錯誤] Lock wait timeout exceeded; try restarting transaction再來看看for update鎖住的數據，對于其他會話的寫操作有何影響。
如下圖所示，我們的會話A依舊沒有commit，但是此時會話B中嘗試執行一次update操作，由于update默認帶了排他鎖，這條sql會鎖表，所以和會話A中的for update鎖出現了沖突，導致會話B一直處于堵塞狀態。
圖片
小總結
通過上述的幾個測試，大家應該也有深刻的體會了，那么我們就來進行下總結，加深下印象。
lock in share mode 鎖 | for update 鎖 | |
多session讀 | 不會堵塞，多個session可以讀共同鎖住的記錄。 | 會堵塞，只能有一個session讀取到鎖住的記錄，其他session的訪問得等待。 |
多session寫 | 會堵塞，任何寫相關的操作都不行 | 會堵塞，任何寫相關的操作都不行 |
看到這里，你應該對lock in share mode 和 for update 有一定了解了吧，但是這兩種鎖，光了解理論，其實還是不夠的，需要有實戰才能讓你對它理解更加深刻，來看下邊的案例。
lock in share mode使用不當，導致死鎖
來看下邊的這個業務場景：
假設我們有一個賬戶表，表結構如下：
CREATE TABLE `t_account` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`coin` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;然后在業務操作上，我們的賬戶扣款和增款邏輯上的設計如下：
//開啟一個事務操作
set autocommit=0;
begin;
//如果賬戶存在，才進行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111 lock in share mode;
//這里我們假設賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100 where user_id=111;
//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;這里為了保證將賬戶流水記錄和打款兩個操作保證一致性，得加入一個本地事務去修飾。但是這段代碼中使用了一個lock in share mode的關鍵字，這個關鍵字是為了避免在并發的情況下，對賬戶記錄進行讀的過程中，有其他地方對賬戶的coin值進行寫的修改。
之所以可能會有其他地方對coin值進行額外的寫操作，主要原因是因為系統業務中的老舊代碼存在，重復造輪子，本來A服務中只有一處地方對賬戶進行修改操作，結果在B服務里，也有一段類似的代碼修改，直接操作了數據庫表，但是由于不好去調整那個服務的代碼，所以暫時只能用 lock in share mode 操作去加鎖。相比于for update鎖來說，使用lock in share mode加鎖，對于讀的影響不大，所以早期設計的時候，沒有考慮那么多，就直接用了它上線。并且上線之后并發度不高，暫時就沒有發現什么問題。
看到這里，你可能感覺似乎這種設計沒有什么問題，那么我們來看看下邊的這個場景：
隨著并發度的增加，我們將修改余額的這個操作，在A服務里面封裝成為了一個方法，并且供各個地方進行調用。但是有一天，出現了這么一個業務場景：
在RocketMQ的消費方，會對用戶的賬戶進行打款操作。在這個消費方的代碼中，同一個userId的消息會有許多條，而且是同一時刻的大量并發消費，這就意味著，同一時刻會有大量的請求調用這個打款的操作，而且是并發，同一個userId。那么這種情況下，我們的 lock in share mode會發生什么樣的情況呢 -- 死鎖。
來看下圖：
圖片
由于我們的線程A持有了鎖，線程B也持有了鎖，但是它們接下來的update操作，都是得等對方將共享鎖釋放后才可以繼續執行，所以就發生了死鎖的場景。
圖片
如何解決上述的lock in share mode死鎖
那么我們如何卻避免上邊的場景發生呢，這里我給出以下兩種思路。
不用鎖，提升事務隔離級別為讀已提交
//開啟一個事務操作
set autocommit=0;
//如果賬戶存在，才進行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111;
//這里我們假設賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100,version=version+1 where user_id=111;
//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;去掉使用lock in share mode，使用樂觀鎖。
例如加入一個version字段，那么我們在執行賬戶扣款的時候，加入version的判斷。例如：
//開啟一個事務操作
set autocommit=0;
//如果賬戶存在，才進行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111 and version=#{version};
//這里我們假設賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100,version=version+1 where user_id=111 and version=#{version};
//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');
commit;這里要注意，當同時兩個會話針對同一行數據執行上述更新操作的時候，可能會導致同一行的記錄被鎖，所以我們在進行update的時候，可以用一個version字段去管理。但是這種設計，可能會導致一次更新失敗，需要進行重試，因此并發量高的情況下，容易對MySQL造成較大的壓力。
引入分布式鎖
直接在業務層引入一把分布式鎖，這種思路比較暴力，但是確實有效。
其實只要我們的select類型的sql中進行顯示加鎖，就有可能會有死鎖情況發生，所以建議大家使用的時候謹慎。
行鎖的幾種類型
- Record Lock（記錄鎖）：單個行記錄上的鎖。這個也是我們日常認為的行鎖。
- Gap Lock（間隙鎖）：間隙鎖，鎖定一個范圍，但不包括記錄本身（只不過它的鎖粒度比記錄鎖的鎖整行更大一些，他是鎖住了某個范圍內的多個行，包括根本不存在的數據）。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。該鎖只會在隔離級別是RR或者以上的級別內存在。間隙鎖的目的是為了讓其他事務無法在間隙中新增數據。
- Next-Key Lock（臨鍵鎖）：它是記錄鎖和間隙鎖的結合，鎖定一個范圍，并且鎖定記錄本身。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。next-key鎖是InnoDB默認的鎖，該鎖也只會在隔離級別是RR或者以上的級別內存在。
行鎖的事故案例
消息數據更新設計不當，導致出現Record Lock死鎖
這里我們需要先了解下消息記錄表的結構；
CREATE TABLE `t_user_message` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '發信方id',
`object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id',
`relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '關聯id',
`is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已讀（0未讀，1已讀）',
`sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息條數',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '狀態（0未審核 1審核失敗 2審核通過）',
`content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息內容',
`type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '類型（0文本，1語音，2圖片，3視頻，4表情，5分享鏈接）',
`ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '擴展字段',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '發信方id索引',
KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引',
KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '關聯id索引'
) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用戶消息表';假設我們的會話A執行了以下事務操作：
START TRANSACTION;
//更新用戶的消息狀態，從未讀變為已讀
update t_user_message set is_read=1 where user_id=1003 and object_id=1004;
//...中間有些別的業務操作
update t_user_message set is_read=1 where user_id=1001 and object_id=1002;
commit;而此時我們的會話B在執行一個異步的消息是否合法的檢測工作，具體操作如下：
set autocommit=0;
START TRANSACTION;
//定時任務更新用戶的消息審核狀態，從未審核變為審核通過
update t_user_message set status=2 where user_id=1001 and object_id=1002;
//...中間有些別的業務操作
update t_user_message set status=2 where user_id=1003 and object_id=1004;
commit;這兩個事務如果并發執行，并發度高的情況下，可能會出現死鎖情況，死鎖產生的步驟如下圖所示：
圖片
一般遇到這類情況，我們都會推薦在進行更新的時候，盡可能的避免死鎖條件發生，例如調整sql的執行順序。例如變更為如下操作：
圖片
另外，調整順序后，盡量將本地事務的顆粒度控制到最小，減少因為加鎖堵塞帶來的性能問題。
間隙鎖堵塞案例分析
首先我們要將當前會話的事務隔離級別設置為可重復讀：
set SESSION transaction ISOLATION LEVEL REPEATABLE READ;如果你想確認當前的會話的事務隔離級別，那么可以使用以下命令去查詢：
SELECT @@transaction_isolation; （mysql8.0語法）
SELECT @@tx_isolation; （mysql5.7語法）還是針對我們的消息表t_user_message，在某些高并發場景下，如果使用可重復讀的話，尤其是事務場景中，出現死鎖的概率會加大。例如下邊這個場景：
事務1中，對消息表的可讀狀態進行修改，修改的是記錄表中的前3條數據，由于是可重復讀，以及非唯一索引user_id和object_id所以這里會鎖住的是(0,100011]這個區間的id記錄，也就是說只要我們更新的行是超過了100011 id的都沒問題。
圖片
但是假設此時有個插入請求，打算往100009之前寫入一條記錄的話，就會出現間隙鎖堵塞的問題，例如下圖所示：
圖片
產生間隙鎖的原因
1.使用了update，delete，selecct... for update相關操作
2.使用了可重復讀的隔離級別
3.在執行update/delete/select ... for update操作之后，在對應的間隙中插入了新的數據（注意是insert了新的數據才會有間隙鎖問題產生)。
MySQL中的死鎖檢測
在mysql5.7、mysql5.8等5系版本中
查看死鎖代碼是
select * from information_schema.innodb_locks;查看等待鎖的代碼
select * from information_schema.innodb_lock_waits但是要注意，在mysql 8.0中查看死鎖代碼變了，如果繼續用5.7的代碼會提示報錯
Unknown table ‘INNODB_LOCKS’ in information_schema所以在8.0使用以下代碼
查看死鎖
select * from performance_schema.data_locks;查看死鎖等待時間
select * from performance_schema.data_lock_waits;
