GreatSQL 中 Insert 慢是什么情況？
作者：王權(quán)富貴
導(dǎo)致此次問題的原因是 GAP 鎖阻塞了 INSERT_INTENTION 鎖；因此建議客戶在執(zhí)行 update 操作時，where 條件用主鍵列，這樣可以避免加 GAP 鎖。
背景概述
客戶反映，業(yè)務(wù)上某張表的 insert 操作速度很慢，單條 insert 語句的最大執(zhí)行時間超過了 5 秒。在收到客戶問題后，我們仔細檢查了數(shù)據(jù)庫狀態(tài)以及主機的負載情況，發(fā)現(xiàn)目前一切正常，并沒有發(fā)現(xiàn)數(shù)據(jù)庫故障或主機負載過高導(dǎo)致 insert 操作變慢的問題。
因此，我們分析了慢日志，希望從中找出問題。經(jīng)過分析，發(fā)現(xiàn)這條插入語句的query_time和lock_time幾乎相同，因此懷疑是由于鎖等待導(dǎo)致插入操作變慢。隨后，我們捕獲了通用日志，幾乎同一時間這張表有update，insert操作，發(fā)現(xiàn)由于更新操作阻塞了插入操作，導(dǎo)致插入速度下降的問題。這個更新操作所在的事務(wù)包含了多條 SQL 語句，因此如果該事務(wù)執(zhí)行時間較長，就會阻塞插入操作，導(dǎo)致插入操作的執(zhí)行時間延長。
問題復(fù)現(xiàn)
本次測試基于 GreatSQL 8.0.32-25，隔離級別為 RR
2.1 創(chuàng)建測試表
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 事務(wù)執(zhí)行順序
時間 | 事務(wù)1 | 事務(wù)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; | -- 鎖等待結(jié)束 |
T6 | commit; |
2.3 事務(wù)1執(zhí)行
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】這條數(shù)據(jù)加加了X,GAP鎖
2.4 事務(wù)2執(zhí)行
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 結(jié)論
此次Insert慢的原因就是Update語句所在的事務(wù)執(zhí)行時間較長，Update語句產(chǎn)生了GAP鎖；
Insert語句在執(zhí)行時此Update語句所在事務(wù)還沒有執(zhí)行完成，因此Insert處于鎖等待階段，待Update所在事務(wù)提交后Insert才提交；
總結(jié)
導(dǎo)致此次問題的原因是 GAP 鎖阻塞了 INSERT_INTENTION 鎖；因此建議客戶在執(zhí)行 update 操作時，where 條件用主鍵列，這樣可以避免加 GAP 鎖。
責任編輯：武曉燕
來源：
GreatSQL社區(qū)
