一條 SQL 引發的事故，同事直接被開除！！

  • 2020 年 9 月 15 日
  • 筆記

前言

Insert into select請慎用。

這天xxx接到一個需求，需要將表A的數據遷移到表B中去做一個備份。本想通過程序先查詢查出來然後批量插入。但xxx覺得這樣有點慢，需要耗費大量的網絡I/O，決定採取別的方法進行實現。

通過在Baidu的海洋里遨遊，他發現了可以使用insert into select實現，這樣就可以避免使用網絡I/O，直接使用SQL依靠數據庫I/O完成，這樣簡直不要太棒了。

然後他就被開除了。

事故發生的經過。

由於數據數據庫中order_today數據量過大，當時好像有700W了並且每天在以30W的速度增加。

所以上司命令xxx將order_today內的部分數據遷移到order_record中，並將order_today中的數據刪除。

這樣來降低order_today表中的數據量。

由於考慮到會佔用數據庫I/O，為了不影響業務，計劃是9:00以後開始遷移，但是xxx在8:00的時候，嘗試遷移了少部分數據(1000條)，覺得沒啥問題，就開始考慮大批量遷移。

在遷移的過程中，應急群是先反應有小部分用戶出現支付失敗，隨後反應大批用戶出現支付失敗的情況，以及初始化訂單失敗的情況，同時騰訊也開始報警。

然後xxx就慌了，立即停止了遷移。

本以為停止遷移就就可以恢復了，但是並沒有。後面發生的你們可以腦補一下。

事故還原

在本地建立一個精簡版的數據庫，並生成了100w的數據。模擬線上發生的情況。

建立表結構

訂單表

CREATE TABLE `order_today` (
  `id` varchar(32) NOT NULL COMMENT '主鍵',
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商戶編號',
  `amount` decimal(15,2) NOT NULL COMMENT '訂單金額',
  `pay_success_time` datetime NOT NULL COMMENT '支付成功時間',
  `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付狀態  S：支付成功、F：訂單支付失敗',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '備註',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間 -- 修改時自動更新',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商戶編號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

訂單記錄表

CREATE TABLE order_record like order_today;

今日訂單表數據

模擬遷移

把8號之前的數據都遷移到order_record表中去。

INSERT INTO order_record SELECT
    *
FROM
    order_today
WHERE
    pay_success_time < '2020-03-08 00:00:00';

在navicat中運行遷移的sql,同時開另個一個窗口插入數據，模擬下單。這篇《如何快速安全的插入千萬條數據？》推薦看下。

從上面可以發現一開始能正常插入，但是後面突然就卡住了，並且耗費了23s才成功，然後才能繼續插入。這個時候已經遷移成功了，所以能正常插入了。

出現的原因

在默認的事務隔離級別下：insert into order_record select * from order_today 加鎖規則是：order_record表鎖，order_today逐步鎖（掃描一個鎖一個）。MySQL 四種隔離級別，推薦看下。

分析執行過程。

通過觀察遷移sql的執行情況你會發現order_today是全表掃描，也就意味着在執行insert into select from 語句時，mysql會從上到下掃描order_today內的記錄並且加鎖，這樣一來不就和直接鎖表是一樣了。

這也就可以解釋，為什麼一開始只有少量用戶出現支付失敗，後續大量用戶出現支付失敗，初始化訂單失敗等情況，因為一開始只鎖定了少部分數據，沒有被鎖定的數據還是可以正常被修改為正常狀態。

由於鎖定的數據越來越多，就導致出現了大量支付失敗。最後全部鎖住，導致無法插入訂單，而出現初始化訂單失敗。

解決方案

由於查詢條件會導致order_today全表掃描，什麼能避免全表掃描呢，很簡單嘛，給pay_success_time字段添加一個idx_pay_suc_time索引就可以了，由於走索引查詢，就不會出現掃描全表的情況而鎖表了，只會鎖定符合條件的記錄。

關於 MySQL 索引的詳細用法有實戰，大家可以關注公眾號Java技術棧在後台回復mysql獲取系列乾貨文章。

最終的sql

INSERT INTO order_record SELECT
    *
FROM
    order_today FORCE INDEX (idx_pay_suc_time)
WHERE
    pay_success_time <= '2020-03-08 00:00:00';

執行過程

總結

使用insert into tablA select * from tableB語句時，一定要確保tableB後面的where，order或者其他條件，都需要有對應的索引，來避免出現tableB全部記錄被鎖定的情況。

參考文章

insert into … select 由於SELECT表引起的死鎖情況分析：

//blog.csdn.net/asdfsadfasdfsa/article/details/83030011

結尾

如果覺得對你有幫助，可以多多評論，多多點贊哦，謝謝。

作者：不一樣的科技宅
來源：juejin.im/post/6844904086173646862

近期熱文推薦：

1.終於靠開源項目弄到 IntelliJ IDEA 激活碼了，真香！

2.我用 Java 8 寫了一段邏輯，同事直呼看不懂，你試試看。。

3.吊打 Tomcat ，Undertow 性能很炸！！

4.國人開源了一款超好用的 Redis 客戶端，真香！！

5.《Java開發手冊（嵩山版）》最新發佈，速速下載！

覺得不錯，別忘了隨手點贊+轉發哦！

VirMach 便宜 VPS

QNews

QNews