Laravel MySQL Query Return Empty When there is a Deadlock

Suppose you have two processes executing the following PHP code at the same time, what results will be printed?

Surprisingly, one process prints the query results, while another process prints an empty collection (array). The code that returned an empty collection did not trigger a deadlock exception as expected.

However, if you have checked the source code of the transaction method, you will find that this method should automatically retry the transaction after a deadlock is found. When the number of deadlocks reaches a set value, the deadlock will be thrown as an exception finally. But, the current situation is that the query looks normal because it returns an empty collection!

This unexpectedly returned empty array and un-thrown deadlock exception is actually an old and tricky PHP-PDO bug. Before 7.4.13 was released, it had been widely present in a large number of PHP versions. There are lots discussions related to this, such as:

  1. https://bugs.php.net/bug.php?id=76742
  2. https://github.com/php/php-src/pull/5937
  3. https://github.com/php/php-src/pull/6203
  4. https://github.com/php/php-src/commit/b03776adb5bbb9b54731a44377632fcc94a59d2f

Before PHP7.4.13, there was almost no way to detect this bug directly. Never try to open PDO::ATTR_EMULATE_PREPARES to solve this problem! Because this option will cause all your PDO query results to become strings, and the loss of data types will cause more serious consequences.

Therefore, the only feasible way is to manually compile the repaired PHP source code, or install the new pre-compiled version of PHP7.4.13.

Leave a Reply

Your email address will not be published.