Juggling Databases When Testing With Laravel: Tackling the Lock Wait Timeout Error

Recently I was building out a multi-tenant API using Laravel, and ran into a problem with my tests. I was getting a Lock wait timeout error on even the simplest of tests, and spent a frustrating chunk of my day banging my head against the wall until I found a solution.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
try restarting transaction

The scenario here is that I have a multi-tenant application - an API, built using Laravel. There is a platform database, and a database per tenant. The tenant for this application is chosen at run-time, based on an API key provided by the user. This key is checked via middleware, then once validated, the tenant is set for the duration of the request.

// config/database.php
'connections' => [
    'tenant' => [
        ...
    ],
    'platform' => [
        ...
    ],
]

// app/Http/Middleware/TenantMiddleware.php

$tenant = $this->tenantService->loadFromRequest(
    request()->header('X-Api-Key')
);
// Congure environment for this tenant
config(['database.connections.tenant.database' => $tenant->database]);
...

Within my tests, I have a fairly typical set of models and model factories to create dummy tenants for use. Each model will specify the connection it expects to use via the $connection property:

class Customer extends Model
{
    protected $connection = 'tenant';

All of my tests are set up to use the RefreshDatabase trait. This is a handy trait which ensures that each test runs in a transaction, cleaning up after itself.

// TestCase.php

use Illuminate\Foundation\Testing\RefreshDatabase;

class TestCase extends BaseTestCase
{
    use CreatesApplication,
        RefreshDatabase;
    ...

Then, within my tests, I can create a tenant, and use it for the duration of the test.

// TenantTest.php

$tenant = Tenant::factory()->create();
$this->setEnvironmentForTenant($tenant);

// Assertions
$customer = Customer::...

The problem comes when I go to run the tests. Without fail, I would see the simplest of tests hang, then complain about a lock timeout.

 FAIL  Tests\Feature\CustomerTest
⨯ customer is created successfully  50.07s
────────────────────────────────────────────────────
 FAILED  Tests\Feature\CustomerTest > customer is created successfully   QueryException
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

50 seconds! That's a long time to wait for a test to fail. And this is a particularly simple test - eventually reduced down to "create a tenant, assert that a customer can be created in the tenant's environment". So what's going on?

The Cause

These timeouts typically happen when a transaction is waiting for another transaction to finish, or when the database is under heavy load. In this case, it's just me running a single test, so there's no load. And the only transaction I'm attempting is the tenant creation.

The problem comes from the db connection switching at runtime. This throws Laravel's transaction management for a bit of a loop. The RefreshDatabase trait is trying to run a transaction on the tenant connection, but the default platform connection is already in a transaction. This is a problem, as the tenant connection is waiting for the platform connection to finish before it can start. The platform connection is waiting for the tenant connection to finish before it can start. And so on, and so on.

The Fix

The solution is to make use of the connectionsToTransact property on the TestCase class. This accepts a list of database connections, across which transactions may happen. Link to (old..) docs

// TestCase.php
abstract class TestCase extends BaseTestCase
{
    use CreatesApplication,
        DatabaseTransactions;

    protected $connectionsToTransact = ['platform', 'tenant'];

With that in place, Laravel can now handle transactions spanning the database change, and our tests are back to passing!

 PASS  Tests\Feature\CustomerTest
✓ customer is created successfully  0.24s
────────────────────────────────────────────────────

Bonus: Data not being removed after test run

This also works really well for cases where the way you're managing database connection switching means that tests aren't cleaning up after themselves. For example, test data is created on both databases, but after a test run, it is only cleaned up from one of those two databases. This is because the transaction is only running on one of the two databases, and the other is left with the data in place. Adding this property to the TestCase class fixes this issue also, ensuring that both databases are left clean after the test run!