How to unit test concurrent read/write with PHPUnit?

Question

I recently encountered an issue on a live application. I realized I had more and more concurrency exceptions and locks with a database.

Basically I start a transaction which requires a SELECT and an INSERT on the same table to commit.

But because the load is really heavy, each transactions locks the table, in most case it is so fast it doesn't cause any problems but there is a point where the locks start waiting more and more.

I was able to somewhat fix this problem by tweaking the queries.

Though, now, I'd like to write some tests with PHPUnit to validate my fix and avoid any regressions.

I was not able to find any materials on how to do this.

Since PHP isn't multi threaded, I've no ideas how I could run concurrent queries in a single test to validate.

Basically, I would like to be able to run multiple calls in a single test to ensure everything is ok.

I know I could try to do some high level tests by directly querying the http server and load the whole application, but since my problem comes from a standalone library I'd rather like to test it against itself.

Any ideas?

Answer 1

The short answer is there's no good way to test concurrent reads/writes on an actual database with PHPUnit. It's simply not the right tool for that job.

But there are a few facets to a good solution for testing this. First the code can (and should) be written to handle every possible issue. A database system like PostgreSQL will fail immediately on locks and transaction issues. To handle it elegantly I use code that looks something like this (pseudo-code, also used to answer another question):

begin transaction while not successful and count < 5     try          execute sql         commit     except         if error code is '40P01' or '55P03'             # Deadlock or lock not available             sleep a random time (200 ms to 1 sec) * number of retries         else if error code is '40001' or '25P02'             # "In failed sql transaction" or serialized transaction failure             rollback             sleep a random time (200 ms to 1 sec) * number of retries             begin transaction         else if error message is 'There is no active transaction'             sleep a random time (200 ms to 1 sec) * number of retries             begin transaction     increment count 

Then create two sets of tests: one set should confirm the code is handling the situations correctly (i.e. unit tests). The other set of tests is for the environment (i.e. integration / functional tests).

Unit Tests

I find this to be a hard situation to reproduce in a PHPUnit test that connects to a database, and using a real database isn't appropriate for a true unit test. Instead, create PDO stubs and unit tests that throw every kind of database exception. This confirms the code is working as expected, but does not test concurrency on any real database. Remember, unit tests are only for confirming your code is written correctly, not for testing 3rd party software.

$iterationCount = 0; $db->runInTransaction(function() use (&$iterationCount) {     $iterationCount++;     if ($iterationCount === 1) {         $exception = new PDOExceptionStub('Deadlock');         $exception->setCode('40P01');         throw $exception;     } });  // First time fails, second time succeeds $this->assertEquals(2, $iterationCount, 'Expected 2 iterations of runInTransaction'); 

Write a complete suite of tests that don't connect to the DB, but confirm logic.

Integration Tests

As you have found, PHPUnit is simply not the right tool to perform a load test. It's not appropriate for anything more complex than sequential unit and integration tests. You could run multiple instances of PHPUnit concurrently to put more load on the database. However I find this goes beyond what it was meant for, plus it doesn't help you monitor the database for issues. Therefore I see no way around the higher level tests you're looking to avoid.

But your library can be tested without running your complete application. I would create the simplest possible application just for testing it. It can have one or more CLI scripts that connect to a database. Those scripts can be spawned multiple times to put load on the database. Or make a simple web page with the library and use any of the many load testing applications out there to test it.