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(
// 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,

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

// TenantTest.php

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

// 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,

    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!

Share This Article

Related Articles

Lazy loading background images to improve load time performance

Lazy loading of images helps to radically speed up initial page load. Rich site designs often call for background images, which can't be lazily loaded in the same way. How can we keep our designs, while optimising for a fast initial load?

Calculating rolling averages with Laravel Collections

Rolling averages are perfect for smoothing out time-series data, helping you to gain insight from noisy graphs and tables. This new package adds first-class support to Laravel Collections for rolling average calculation.

Font Subsetting - shrink down font files to speed up page loads

Fonts are one of the largest resources on any page after images, and can have a big impact on CLS when they vary in size from the underlying system font. Font subsetting allows us to radically shrink font file sizes, speed up initial page loads, and improve our page speed scores.

Minimising Cumulative Layout Shift (CLS) When Loading Responsive Ads

Responsive ads are a great way to maximise publisher revenue from display ads. Not knowing the size of the ad to be served in advance can have a big impact on Cumulative Layout Shift (CLS), and, ultimately, Google rankings. How do we maximise revenue while minimising CLS impact?