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:


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.

1 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.

  • I actually wen ahead and somehow managed to override the connection from one to another, but since you did write such a good answer I can not NOT accept it. autocommit was off for both of the connections and no commit actually occurs, since well the database should remain untouched, so both connections start transactions and non of them ever commits, at the end they both will rollback (if lock error doesn't happen). GET_LOCK and ALTER TABLE are not used anywhere Nov 12, 2022 at 19:50
  • "Never" commits? Perhaps takes more than 50 seconds for some Unit tests? Sounds like PHPUnit is not designed to run in multiple threads.
    – Rick James
    Nov 12, 2022 at 22:25
  • The test actually takes less than 2 seconds and it is just a single test (when I remove that one sql command on the second connection) , looks like the lock stays indefinitely on the first connection Nov 13, 2022 at 6:43
  • What is the value of autocommit?
    – Rick James
    Nov 13, 2022 at 23:38
  • It is off since we don't want any commits just a final rollback. Nov 14, 2022 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.