BenchmarkSQL 是一款经典的开源数据库测试工具，内含了TPC-C测试脚本，可支持 Oracle、MySQL、PostgreSQL、SQL Server以及一些国产数据库的基准测试。
2问题描述
如下图，在使用 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 的操作。
3源码探索
为什么会有这种类似死循环的情况出现呢？怀着探索精神，我们一起去看看 BenchmarkSQL 的源代码。
- 首先下载对应的源码包，通过 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- 执行以上命令，可以定位到事务 SQL 的代码在 ./client/jTPCCConnection.java 文件中，通过搜索 DELETE FROM bmsql_new_order 和 SELECT no_o_id FROM bmsql_new_order，找到对应的 stmtDeliveryBGSelectOldestNewOrder 和 stmtDeliveryBGDeleteOldestNewOrder 关键字。
图片
- 再次通过关键字 stmtDeliveryBGDeleteOldestNewOrder 搜索，最终可以定位到 ./BenchmarkSQL-5.0/src/client/jTPCCTData.java 的这部分代码：
图片
看到这部分注释，也许你已经知道了问题所在，下面我们结合代码、注释和实验，来探究卡住的原因。
- 事务A：DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2102;
- 事务B：DELETE FROM bmsql_new_order WHERE no_w_id = 1 AND no_d_id = 1 AND no_o_id = 2102;执行时被阻塞。
- 事务A：提交。
- 事务B：继续执行 DELETE 语句，但因为事务A已经删除了这行数据，故删除的记录数为 0。BenchmarkSQL 中使用了 JDBC 的 executeUpdate() 方法，该方法会返回一个 INT 类型的值，即本次操作在数据库中改变的行数。
- 结合代码分析，在执行 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;
}- 因为当前隔离级别配置为 REPEATABLE-READ 级别，故在同一事务中执行 SELECT no_o_id FROM bmsql_new_order ...ASC 进行排序后，查询结果依旧为 no_o_id=2102 的数据，由此 rc 再次被赋值为 0，进入到无限的 while 死循环中。
4场景实验
下面我们基于 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 | |
... |
5总结
由此我们可以得出结论，因为 MySQL 配置的隔离级别是 REPEATABLE-READ，导致 BenchmarkSQL 出现了死循环的问题，将其修改为 READ-COMMITTED 级别后，问题得以解决。
参考资料
[1]benchmarksql: https://benchmarksql.readthedocs.io/en/latest/
