BenchmarkSQL 对 MySQL 测试时请注意隔离级别！

BenchmarkSQL 是一款经典的开源数据库测试工具，内含了TPC-C测试脚本，可支持 Oracle、MySQL、PostgreSQL、SQL Server以及一些国产数据库的基准测试。

作者：李彬，爱可生 DBA 团队成员，负责项目日常问题处理及公司平台问题排查。爱好有亿点点多，吉他、旅行、打游戏

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1500 字，预计阅读需要 5 分钟。

背景

最近在使用 BenchmarkSQL 工具对 MySQL 进行性能测试的过程中，遇到一个比较有意思的问题，Share 给大家。

什么是 BenchmarkSQL？

BenchmarkSQL 是一款经典的开源数据库测试工具，内含了TPC-C测试脚本，可支持 Oracle、MySQL、PostgreSQL、SQL Server以及一些国产数据库的基准测试。

问题描述

如下图，在使用 BenchmarkSQL（版本为 5.0）压测一段时间后，会出现卡住的现象，即 tpm TOTAL 的值不再发生变化，但通过 top 命令观测到 MySQL 当前的压力还是很大。

登录 MySQL，通过 information_schema.innodb_trx 表可以看到，MySQL 一直在重复执行这两个 SQL：

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 685907
                 trx_state: RUNNING
               trx_started: 2024-05-28 11:14:21
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 157
                 trx_query: SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 685907
                 trx_state: RUNNING
               trx_started: 2024-05-28 11:14:21
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 157
                 trx_query: DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2102
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)

多次执行 show master status\G 也可以看到 GTID 不再发生变化。为了更好的分析，打开 MySQL 的 general log 后重新压测抓取 SQL：

可以看到确实出现了重复 DELETE FROM 和 SELECT 的情况，再往前多看几个事务，你会发现前几个事务均对 2102 这条记录进行了 DELETE 的操作。

源码探索

为什么会有这种类似死循环的情况出现呢？怀着探索精神，我们一起去看看 BenchmarkSQL 的源代码。

1. 首先下载对应的源码包，通过 for 循环找出 SQL 文件对应的代码文件。

[root@lucky src]$ pwd
/root/packages/BenchmarkSQL-5.0/src

[root@lucky src]$ for dic in client  jdbc  LoadData  OSCollector
do
 echo $dic
 for file in `ls $dic`
 do
     echo $file && cat $dic/$file | grep -Ein 'bmsql_customer|grep bmsql_customer|bmsql_customer|bmsql_oorder|bmsql_new_order|bmsql_order_line|bmsql_stock|bmsql_item|bmsql_history'
 done
done

2. 执行以上命令，可以定位到事务 SQL 的代码在 ./client/jTPCCConnection.java 文件中，通过搜索 DELETE FROM bmsql_new_order 和 SELECT no_o_id FROM bmsql_new_order，找到对应的 stmtDeliveryBGSelectOldestNewOrder 和 stmtDeliveryBGDeleteOldestNewOrder 关键字。

3. 再次通过关键字 stmtDeliveryBGDeleteOldestNewOrder 搜索，最终可以定位到 ./BenchmarkSQL-5.0/src/client/jTPCCTData.java 的这部分代码：

看到这部分注释，也许你已经知道了问题所在，下面我们结合代码、注释和实验，来探究卡住的原因。

1. 事务A：

   DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2102;

2. 事务B：

   DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2102;

   执行时被阻塞。

3. 事务A：提交。

4. 事务B：

   继续执行 DELETE 语句，但因为事务A已经删除了这行数据，故删除的记录数为 0。BenchmarkSQL 中使用了 JDBC 的 executeUpdate() 方法，该方法会返回一个 INT 类型的值，即本次操作在数据库中改变的行数。

5. 结合代码分析，在执行 stmt2.executeUpdate() 后，rc=0，o_id=-1。又因为 o_id<0，故执行了 continue，继续下一个 while 循环。

// 重点简要代码
while (o_id < 0)
{
   rs = stmt1.executeQuery();
   rc = stmt2.executeUpdate();
   if (rc == 0)
   {
       o_id = -1;
   }
}

if (o_id < 0)
{
   continue;
}

6. 因为当前隔离级别配置为 REPEATABLE-READ 级别，故在同一事务中执行 SELECT no_o_id FROM bmsql_new_order ...ASC 进行排序后，查询结果依旧为 no_o_id=2102 的数据，由此 rc 再次被赋值为 0，进入了无限的 while 死循环中。

场景实验

下面我们基于 REPEATABLE-READ 级别和 READ-COMMITTED 级别，进行类似场景的实验。

1. REPEATABLE-READ 场景

sessionA	sessionB
set autocommit=0;	set autocommit=0;
SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2542	SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2542
DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2542;	DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2542; # 锁等待
commit;	# 上一条 DELETE 语句执行成功，返回 0 rows affected
	SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2542
	DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2542; # 执行成功，返回0 rows affected
	SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2542
	...

2. READ-COMMITTED 场景

sessionA	sessionB
set autocommit=0;	set autocommit=0;
SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2543	SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2543
DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2543;	DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2543; # 锁等待
commit;	# 上一条 DELETE 语句执行成功，返回 0 rows affected
	SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 ORDER BY no_o_id ASC limit 1; # 结果=2544
	...

总结

由此我们可以得出结论，因为 MySQL 配置的隔离级别是 REPEATABLE-READ，导致 BenchmarkSQL 出现了死循环的问题，将其修改为 READ-COMMITTED 级别后，问题得以解决。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle