search

waterguo / benchmarksql

BenchmarkSQL for MySQL, PostgreSQL and Oracle
13 stars 13 forks source link

Why mysql cannot exit benchmark? #6

Open sangli00 opened 4 years ago

sangli00 commented 4 years ago 

2020-04-04 22:42:15,099 ERROR - java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1288)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:794)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
        at jTPCCTerminal.paymentTransaction(jTPCCTerminal.java:1258)
        at jTPCCTerminal.executeTransaction(jTPCCTerminal.java:321)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:172)
        at jTPCCTerminal.run(jTPCCTerminal.java:122)
        at java.lang.Thread.run(Thread.java:748)

Term-00, Running Average tpmTOTAL: 11.74    Current tpmTOTAL: 228    Memory Usage: 139MB 2020-04-04 22:42:15,100 ERROR - Lock wait timeout exceeded; try restarting transaction       Term-00, Running Average tpmTOTAL: 14.68    Current tpmTOTAL: 264    Memory Usage: 139MB / 842MB

props.mysql info

2020-04-04 22:40:32,626  INFO - Term-00, user=root
2020-04-04 22:40:32,626  INFO - Term-00,
2020-04-04 22:40:32,626  INFO - Term-00, warehouses=1
2020-04-04 22:40:32,627  INFO - Term-00, terminals=10
2020-04-04 22:40:32,627  INFO - Term-00, runMins=1
2020-04-04 22:40:32,627  INFO - Term-00, limitTxnsPerMin=0
2020-04-04 22:40:32,627  INFO - Term-00,
2020-04-04 22:40:32,627  INFO - Term-00, newOrderWeight=45
2020-04-04 22:40:32,627  INFO - Term-00, paymentWeight=43
2020-04-04 22:40:32,627  INFO - Term-00, orderStatusWeight=4
2020-04-04 22:40:32,627  INFO - Term-00, deliveryWeight=4
2020-04-04 22:40:32,627  INFO - Term-00, stockLevelWeight=4
waterguo commented 4 years ago

it looks like you are running into dead locks. i think it is because you are using the default isolation level - repeatable read. try changing it to read-committed. please refer to this link about how to change it https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html.

repeatable read is more lock prone than read committed. let me know if it works.

by the way what's the version of mysql are you using?