GreatSQL中 Insert 慢是什麼情況？
背景概述
客戶反映，業務上某張表的 Insert 操作速度很慢，單條 Insert 語句的最大執行時間超過了 5 秒。在收到客戶問題後，我們仔細檢查了資料庫狀態以及主機的負載情況，發現目前一切正常，並沒有發現資料庫故障或主機負載過高導致 insert 操作變慢的問題。
因此，我們分析了慢日誌，希望從中找出問題。經過分析，發現這條插入語句的query_time和lock_time幾乎相同，因此懷疑是由於鎖等待導致插入操作變慢。隨後，我們捕獲了通用日誌，幾乎同一時間這張表有update，insert操作，發現由於更新操作阻塞了插入操作，導致插入速度下降的問題。這個更新操作所在的事務包含了多條 SQL 語句，因此如果該事務執行時間較長，就會阻塞插入操作，導致插入操作的執行時間延長。
問題復現
本次測試基於 GreatSQL-8.0.32-25，隔離級別為 RR
2.1 建立測試表
greatsql> CREATE TABLE `t11` (
`id` int NOT NULL,
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
`c3` int DEFAULT NULL,
`c4` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c2` (`c2`,`c3`),
KEY `c4` (`c4`)
);
greatsql> insert into t11 values (1,1,1,1,1),(2,2,2,2,2),(3,3,3,3,3),(5,5,5,5,5);
2.2 事務執行順序
| 時間 | 事務1 | 事務2 |
|---|---|---|
| T1 | BEGIN; | BEGIN; |
| T2 | update t10 set c2=20 where c4=2; | |
| T3 | insert into t10 values (6,2,2,2,2); | |
| T4 | -- hang住，處於鎖等待 | |
| T5 | commit; | -- 鎖等待結束 |
| T6 | commit; |
2.3 事務1執行
greatsql> begin;
greatsql> update t11 set c2=20 where c4=2;
檢視加鎖情況：
greatsql> select THREAD_ID,EVENT_ID,ENGINE_LOCK_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| THREAD_ID | EVENT_ID | ENGINE_LOCK_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 55 | 20 | 140531661278568:44172:140531678523168 | test | t11 | NULL | TABLE | IX | GRANTED | NULL |
| 55 | 20 | 140531661278568:43110:6:3:140531678129184 | test | t11 | c4 | RECORD | X | GRANTED | 2, 2 |
| 55 | 20 | 140531661278568:43110:4:3:140531678129528 | test | t11 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
| 55 | 20 | 140531661278568:43110:6:4:140531678129872 | test | t11 | c4 | RECORD | X,GAP | GRANTED | 3, 3 |
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.01 sec)
可以看到此時給【3, 3】這條資料加加了X,GAP鎖
2.4 事務2執行
greatsql> begin;
greatsql> insert into t11 values (6,2,2,2,2);
檢視加鎖情況：
greatsql> select THREAD_ID,EVENT_ID,ENGINE_LOCK_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
| THREAD_ID | EVENT_ID | ENGINE_LOCK_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
| 56 | 14 | 140531661279416:44172:140531678523936 | test | t11 | NULL | TABLE | IX | GRANTED | NULL |
| 56 | 14 | 140531661279416:43110:6:4:140531678132256 | test | t11 | c4 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 3, 3 |
| 55 | 20 | 140531661278568:44172:140531678523168 | test | t11 | NULL | TABLE | IX | GRANTED | NULL |
| 55 | 20 | 140531661278568:43110:6:3:140531678129184 | test | t11 | c4 | RECORD | X | GRANTED | 2, 2 |
| 55 | 20 | 140531661278568:43110:4:3:140531678129528 | test | t11 | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
| 55 | 20 | 140531661278568:43110:6:4:140531678129872 | test | t11 | c4 | RECORD | X,GAP | GRANTED | 3, 3 |
+-----------+----------+-------------------------------------------+---------------+-------------+------------+-----------+------------------------+-------------+-----------+
6 rows in set (0.00 sec)
greatsql> select REQUESTING_THREAD_ID,REQUESTING_EVENT_ID,REQUESTING_ENGINE_LOCK_ID,BLOCKING_THREAD_ID,BLOCKING_EVENT_ID,BLOCKING_ENGINE_LOCK_ID from performance_schema.data_lock_waits;
+----------------------+---------------------+-------------------------------------------+--------------------+-------------------+-------------------------------------------+
| REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_ENGINE_LOCK_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_ENGINE_LOCK_ID |
+----------------------+---------------------+-------------------------------------------+--------------------+-------------------+-------------------------------------------+
| 56 | 14 | 140531661279416:43110:6:4:140531678132256 | 55 | 20 | 140531661278568:43110:6:4:140531678129872 |
+----------------------+---------------------+-------------------------------------------+--------------------+-------------------+-------------------------------------------+
1 row in set (0.00 sec)
透過上面2張表，可以看到 X,GAP鎖 阻塞了 X,GAP,INSERT_INTENTION 鎖；
2.5 結論
此次insert慢的原因就是update語句所在的事務執行時間較長，update語句產生了GAP鎖；
insert 語句在執行時此update語句所在事務還沒有執行完成，因此insert處於鎖等待階段，待update所在事務提交後insert才提交；
總結
導致此次問題的原因是 GAP鎖阻塞了 INSERT_INTENTION 鎖；因此建議客戶在執行update操作時，where條件用主鍵列，這樣可以避免加GAP鎖。
Enjoy GreatSQL 😃
關於 GreatSQL
GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。
相關連結： GreatSQL社群 Gitee GitHub Bilibili
GreatSQL社群：
社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html
技術交流群：
微信：掃碼新增
GreatSQL社群助手微信好友，傳送驗證資訊加群。
