GreatSQL 中 Insert 慢是什么情况？

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社区助手微信好友，发送验证信息加群。