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