Anyone having problem with multiple PreparedStatement for tokudb?

[Jonas Persson]

I have a project that is using innodb/mysql and last week I wanted to try out tokudb instead.

For some reason I get

"java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction"

but I don't understand why.

I managed to create a small java class (attached) that triggers the problem by doing:

1. Using jdbc

2. Open connection with rewriteBatchedStatements=true

3. Set auto commit false

4. Create 10 tables with one column: "id BIGINT UNSIGNED PRIMARY KEY NOT NULL"

5. Create one PreparedStatement for each table: "INSERT INTO <table name> (id) VALUES(?)"

6. Insert 2x2 rows of data for each of the PreparedStatement (i.e 40 rows in total)

Anyone else seen this problem?

If I only use 4 PreparedStatment or less it works ok.

I'm using Tokudb 7.5.5

I tried changing isolation level to READ-COMMIT but it didn't solve the problem. But maybe there is some other setting that can be tweaked..

The tokudb_lock_waits table has a lot of stuff right before the exception, but I can't figure out what the root cause is.

/J

[Rik Prohaska]

This doc describes how to debug locking issues with TokuDB:

https://github.com/Tokutek/tokudb-engine/wiki/Lock-Visualization-in-TokuDB

[Jonas Persson]

Yes, I have seen that doc and tried to interpret the locks information that I get from the database but I guess that I'm not experienced enough to find the root cause.

Here is the active locks when the time out occurs:

locks_trx_id, locks_mysql_thread_id, locks_dname, locks_key_left, locks_key_right, locks_table_schema, locks_table_name, locks_table_dictionary_name

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65302d6d61696e00', '2e2f746573742f7461626c65302d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65312d6d61696e00', '2e2f746573742f7461626c65312d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65322d6d61696e00', '2e2f746573742f7461626c65322d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65332d6d61696e00', '2e2f746573742f7461626c65332d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65342d6d61696e00', '2e2f746573742f7461626c65342d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65352d6d61696e00', '2e2f746573742f7461626c65352d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65362d6d61696e00', '2e2f746573742f7461626c65362d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65372d6d61696e00', '2e2f746573742f7461626c65372d6d61696e00', '', '', ''

'91532', '9', '$ydb_internal', '2e2f746573742f7461626c65382d6d61696e00', '2e2f746573742f7461626c65382d6d61696e00', '', '', ''

'91532', '9', './test/table9-main', '-infinity', '+infinity', 'test', 'table9', 'main'

'91532', '9', './test/table9-main', '000100000000000000', '000100000000000000', 'test', 'table9', 'main'

'91532', '9', './test/table9-main', '000200000000000000', '000200000000000000', 'test', 'table9', 'main'

'91532', '9', './test/table9-main', '000100000000000000', '000100000000000000', 'test', 'table9', 'main'

'91532', '9', './test/table9-main', '000200000000000000', '000200000000000000', 'test', 'table9', 'main'

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', '$ydb_internal', '-infinity', '+infinity', '', '', ''

'91532', '9', './test/table6-main', '-infinity', '+infinity', 'test', 'table6', 'main'

'91532', '9', './test/table7-main', '-infinity', '+infinity', 'test', 'table7', 'main'

'91532', '9', './test/table8-main', '-infinity', '+infinity', 'test', 'table8', 'main'

'91544', '9', '$ydb_internal', '2e2f746573742f7461626c65302d73746174757300', '2e2f746573742f7461626c65302d73746174757300', '', '', ''

'91544', '9', './test/table0-status', '0600000000000000', '0600000000000000', 'test', 'table0', 'status'

'91544', '9', './test/table0-status', '0100000000000000', '0100000000000000', 'test', 'table0', 'status'

'91544', '9', './test/table0-status', '0500000000000000', '0500000000000000', 'test', 'table0', 'status'

I can see that transaction 91544 is waiting on transaction 91532 to complete, but it seems to be stuck.

But why is 91532 stuck? It isn't waiting on any other transaction from what I can see... Or could a transaction lock itself? That would be a bit weird. Also, the sql statements are not rocket science, just a bunch of INSERTs with one column.

I'm unclear as to why tables 6,7 and 8 is part of transaction 91532 since I would expect one table (using executeBatch) to equal one transaction.

And why all the $ydb_internal locks, or they are expected to be there?

Any help appreciated

[Jonas Persson]

After doing a fresh installation with Percona 5.5, Tokudb and the operating system on a different machine I don't see the problem any more.

Case closed I guess.

/J