聊一個 MySQL 插入死鎖問題
作者：寫代碼的SharkChili
本文就以一個簡單的功能示例為大家演示一下insert into ...... select 可能引發的問題和解決方案。
在進行MySQL數據備份遷移時，很多人為了避免網絡IO的開銷而選用insert into ...... select 進行數據遷移備份，但你是否知道這種做法會存在那些隱患呢？ 所以本文就以一個簡單的功能示例為大家演示一下insert into ...... select 可能引發的問題和解決方案。
問題復現
這里為了演示問題，筆者生成了一張帶有500w數據的數據表，對應DDL語句如下：
CREATE TABLE `batch_insert_test` (
`id` int NOT NULL AUTO_INCREMENT,
`fileid_1` varchar(100) DEFAULT NULL,
`fileid_2` varchar(100) DEFAULT NULL,
`fileid_3` varchar(100) DEFAULT NULL,
`fileid_4` varchar(100) DEFAULT NULL,
`fileid_5` varchar(100) DEFAULT NULL,
`fileid_6` varchar(100) DEFAULT NULL,
`fileid_7` varchar(100) DEFAULT NULL,
`fileid_8` varchar(100) DEFAULT NULL,
`fileid_9` varchar(100) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2520001 DEFAULT CHARSET=utf8mb3 COMMENT='測試批量插入，一行數據1k左右';使用count語句查看數據量：
select count(*) from batch_insert_test;稍微久等了一小會，輸出語句如下，可以看到一張表數據剛剛好達到500w:
count(*)|
--------+
5000000|同樣的我們給出備份遷移表的DDL，表結構是一樣的，唯一區別就是表名后綴多了個bak：
CREATE TABLE `batch_insert_test_bak` (
`id` int NOT NULL AUTO_INCREMENT,
`fileid_1` varchar(100) DEFAULT NULL,
`fileid_2` varchar(100) DEFAULT NULL,
`fileid_3` varchar(100) DEFAULT NULL,
`fileid_4` varchar(100) DEFAULT NULL,
`fileid_5` varchar(100) DEFAULT NULL,
`fileid_6` varchar(100) DEFAULT NULL,
`fileid_7` varchar(100) DEFAULT NULL,
`fileid_8` varchar(100) DEFAULT NULL,
`fileid_9` varchar(100) DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2520001 DEFAULT CHARSET=utf8mb3 COMMENT='測試批量插入，一行數據1k左右';此時我們使用數據庫連接工具在會話窗口，執行如下遷移語句：
insert into batch_insert_test_bak select * from batch_insert_test;然后我們再寫一段程序模擬插入：
@Test
public void insert() {
while (true) {
BatchInsertTest batchInsertTest = new BatchInsertTest();
batchInsertTest.setFileid1(RandomUtil.randomString(1));
batchInsertTest.setFileid2(RandomUtil.randomString(1));
batchInsertTest.setFileid3(RandomUtil.randomString(1));
batchInsertTest.setFileid4(RandomUtil.randomString(1));
batchInsertTest.setFileid5(RandomUtil.randomString(1));
batchInsertTest.setFileid6(RandomUtil.randomString(1));
batchInsertTest.setFileid7(RandomUtil.randomString(1));
batchInsertTest.setFileid8(RandomUtil.randomString(1));
batchInsertTest.setFileid9(RandomUtil.randomString(1));
batchInsertTest.setCreateDate(new Date());
long begin = System.currentTimeMillis();
batchInsertTestMapper.insert(batchInsertTest);
long end = System.currentTimeMillis();
log.info("插入耗時:{} ms", end - begin);
ThreadUtil.sleep(10000L);
}
}從輸出結果來看，一開始插入并不是很耗時，基本都是毫秒級完成，但是隨著時間的推移，插入的耗時逐漸增加，最慢的一次數據插入竟然花費了1分多鐘：
18.546 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:148 ms
28.778 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:221 ms
38.926 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:143 ms
49.588 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:652 ms
59.763 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:166 ms
09.820 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:56 ms
19.930 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:99 ms
30.027 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:86 ms
40.145 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:113 ms
50.238 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:79 ms
17.178 INFO c.s.w.WebTemplateApplicationTests:117 main 插入耗時:76927 ms原因剖析
我們不妨使用如下語句查看一下執行計劃：
explain insert into batch_insert_test_bak select * from batch_insert_test;可以看出無論是insert還是select都是走全表掃描，因為select查詢沒有走索引導致select子句的執行過程會針對整張表從上到下的掃描進行一個逐步鎖(S鎖)的過程，隨著時間的推移它最終就會變為全表鎖。
而insert語句也因為對于插入數量未知而上全表鎖，進而長期持有auto-inc鎖，當然因為insert的表是用于遷移備份數據的，auto-inc鎖的長時間持有對于業務來說影響不大。
id|select_type|table |partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra|
--+-----------+---------------------+----------+----+-------------+---+-------+---+-------+--------+-----+
1|INSERT |batch_insert_test_bak| |ALL | | | | | | | |
1|SIMPLE |batch_insert_test | |ALL | | | | |4692967| 100.0| |而select則不同，select查詢操作上的是讀鎖也就是S鎖，這使得其他事務針對掃描到的數據只能上S鎖不能上X鎖即寫鎖。
通過執行計劃可以看到我們的操作是全表掃描ALL，這也就意味著該查詢逐步上S鎖，導致一段時間后，整張表都被鎖住，使得我們的新的會話的插入語句的事務無法提交。進而導致大量連接數阻塞積壓，各種超時問題也就隨之誕生，嚴重一點就很可能導致整個業務線癱瘓：
解決方案
所以如果我們希望遷移時不鎖住全表，可以在指定在每次遷移時指定一個范圍，所以我們針對時間字段增加索引，通過縮小范圍加索引查詢避免全表鎖：
ALTER TABLE db1.batch_insert_test DROP INDEX batch_insert_test_create_date_IDX;
CREATE INDEX batch_insert_test_create_date_IDX USING BTREE ON db1.batch_insert_test (create_date);然后遷移的sql改為：
insert into batch_insert_test_bak select * from batch_insert_test where create_date <now() ;查看執行計劃發現，select走了range索引，避免全表掃描，解決了上述的風險：
id|select_type|table |partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
--+-----------+---------------------+----------+-----+---------------------------------+---------------------------------+-------+---+----+--------+---------------------+
1|INSERT |batch_insert_test_bak| |ALL | | | | | | | |
1|SIMPLE |batch_insert_test | |range|batch_insert_test_create_date_IDX|batch_insert_test_create_date_IDX|6 | | 1| 100.0|Using index condition|小結
由此可以得出，再使用數據insert into ...... select進行數據遷移時，無比考慮讀寫鎖的工作機制，以及遷移可能導致的鎖的粒度和范圍，只有精確的評估風險點才能保證功能上限不影響正常業務的工作。
責任編輯：趙寧寧
來源：
寫代碼的SharkChili
