MySQL configuration for multiple-threading transactions

Question

I have 6 scripts/tasks. Each one of them starts a MySQL transaction, then do its job, which means SELECT/UPDATE/INSERT/DELETE from a MySQL database, then rollback.

So if the database is at a given state S, I launch one task, when the task terminates, the database is back to state S.

When I launch the scripts sequentially, everything works fine:

• DB at state S
• task 1
• DB at state S
• task 2
• DB at state S
• ...
• ...
• task 6
• DB at state S

But I'd like to speed up the process by multiple-threading and launching the scripts in parallel.

• DB at state S
• 6 tasks at the same time
• DB at state S

Some tasks randomly fail, I sometimes get this error:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

I don't understand, I thought transactions were meant for that. Is there something I'm missing ? Any experience, advice, clue is welcome.

The MySQL configuration is:

innodb_lock_wait_timeout = 500
transaction-isolation = SERIALIZABLE

and I add AUTOCOMMIT = 0 at the beginning of each session.

PS: The database was built and used under the REPEATABLE READ isolation level which I changed afterwards.

Answer 1

You can prevent deadlocks by ensuring that every transaction/process does a SELECT...FOR UPDATE on all required data/tables with the same ORDER BY in all cases and with the same order of the tables itself (with at least repeateable read isolation level in MySQL).

Apart from that, isolation levels and transactions are not meant to handle deadlocks, it is vice versa, they are the reason why deadlocks exist. If you encounter a deadlock, there are good chances that you would have an inconsistent state of your dataset (which might be much more serious - if not, you might not need transactions at all).