聊一個 MySQL 插入死鎖問題

作者：寫代碼的SharkChili 2025-01-20 13:20:00

本文就以一個簡單的功能示例為大家演示一下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

MySQL 數據遷移數據庫