BenchmarkSQL 對 MySQL 測試時請注意隔離級別！
1背景
最近在使用 BenchmarkSQL[1] 工具對 MySQL 進行性能測試的過程中，遇到一個比較有意思的問題，Share 給大家。
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/
