Using two transactions at the same time without getting Lock wait timeout exceeded

Question

I use MySQL 5.6

I'm trying to make a test framework on PHPunit, I use two different db connections (from two different systems it has to be 2 different db connections not a choice). By default many testing frameworks enable transactions, so whatever you do in your tests would not really be saved in the DB.

Right now both connections can have this option separately, so even though they do not affect each other results at all they both work completely separately, meaning I insert a row in connectionA that won't exist on connectionB.

What I am trying to do is running all the queries from connectionA on connectionB and vice-vercia so I can get the same result in queries on both connections, I already could manage to get the queries from the other connection but when I try to run it on the other connection for some queries I get some lock errors eg:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('category_children', 'a:0:{}', 'yes'))

Is there any ways, I can totally disable locks in the transactions? or remove all the locks before running the query?

I tried:

UNLOCK TABLES

But it doesn't do anything helpful, I guess since there is no actual locking involved the transaction from connectionA does this (internal locking?) to the connectionB I suppose.

This is a testing framework so I can go crazy and do any hack in it, it's not a production software so anything comes to your mind even if it's totally a hack I'm okay with it and it's highly appreciated.

Answer 1

(Some debugging suggestions)

INSERTing a single row cannot, by itself, get such a timeout. Was it part of a longer transaction? That is between START TRANSACTION and COMMIT`? If so, we need to discuss the rest of the queries inside that transaction.

What is the value of autocommit? If that is OFF and you do not have an explicit transaction, then there is actually an implicit transaction.

Was something else going on, such as ALTER TABLE? You suggest that there are only two INSERTs, but maybe something unrelated was happening.

You should not be using LOCK/UNLOCK TABLES with InnoDB tables. (I don't know the innards of PHPUnit.)

Is GET_LOCK() used anywhere, by any process?

Increasing innodb_lock_wait_timeout from the already-too-high 50 seconds is a possible cure. But I don't recommend it because it will probably lead to other problems.

When it happens again, run SHOW ENGINE INNODB STATUS and provide at least the section about the last deadlock. (This may be futile since the "timeout" was not really a "deadlock".)

Turn on the "general log" to capture all the activity from both connections. This may be easier than reading the code of PHPUnit.