Analyzing a BenchmarkSQL‑Induced Infinite Loop in MySQL under REPEATABLE‑READ Isolation

While using BenchmarkSQL (v5.0) to stress‑test MySQL, the author observed that after a period of load the tpm TOTAL metric stopped changing even though the server remained under heavy load, indicating a stall.

Inspection of information_schema.innodb_trx revealed two repeating transactions: a SELECT and a DELETE on bmsql_new_order . The GTID in show master status also stopped advancing, confirming that the benchmark was stuck.

By enabling the MySQL general log and reviewing the captured SQL statements, the author identified repeated DELETE FROM bmsql_new_order ... and SELECT no_o_id FROM bmsql_new_order ... ORDER BY ... ASC operations targeting the same row (e.g., no_o_id = 2102 ).

Source‑code exploration of BenchmarkSQL located the relevant logic in ./client/jTPCCConnection.java and ./BenchmarkSQL-5.0/src/client/jTPCCTData.java . The key fragment shows a loop that executes a SELECT followed by stmt2.executeUpdate() . When the DELETE affects zero rows (rc = 0), the code sets o_id = -1 and continues the loop.

while (o_id < 0) {
    rs = stmt1.executeQuery();
    rc = stmt2.executeUpdate();
    if (rc == 0) {
        o_id = -1;
    }
}
if (o_id < 0) {
    continue;
}

Because the session runs under the default REPEATABLE‑READ isolation level, the SELECT sees the same row again after the failed DELETE , causing rc to remain 0 and the loop to repeat indefinitely, creating an apparent dead‑loop in BenchmarkSQL.

The author reproduced the issue with two experiments:

REPEATABLE‑READ scenario: Session A deletes a row, Session B blocks on the same delete, commits, then repeatedly sees the same no_o_id and attempts the delete again, never progressing.

READ‑COMMITTED scenario: After Session A commits, Session B’s subsequent SELECT returns the next row, allowing the delete to succeed and the benchmark to continue.

The conclusion is that the infinite loop is caused by the REPEATABLE‑READ isolation level; switching the MySQL transaction isolation to READ‑COMMITTED eliminates the stall.

References: BenchmarkSQL documentation.