Laravel - Lock wait timeout exceeded

Question

I have a lot of transactions in my code, and if an error occurs in executing in one of these transactions that doesn't trigger commit or rollback, then the database is locked and any subsequent attempts to access the database results in this:

production.ERROR: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction in /home/forge/default/vendor/laravel/framework/src/Illuminate/Database/Connection.php:390

In the Controller:

DB::beginTransaction();

try {
    //Code that uses exec() to process some images. <-- If code breaks here, then the above error appears on subsequent requests.
    //Code that accesses the database
}
catch(\Exception $e){
    DB::rollback();
    throw $e;
}
DB::commit();

So even php artisan migrate:refresh or php artisan migrate:reset stops working as well. How should I go about fixing this?

Answer 1

Here's some tips from my experience ...

If you are doing test driven development, isolate which combination of tests produce the error. Utilize whatever mechanism your ecosystem provides to selectively run tests (Example: @group only on test methods and phpunit --group only)

Next, reduce the lock wait timeout (SET GLOBAL innodb_lock_wait_timeout = 10). This way you get quick feedback and don't spend your whole day waiting for tests to run. Mileage may vary. Adjust to your specific conditions.

Thirdly, look for unclosed transactions, ie begin without rollback or commit. This turned out to be exactly what my problem was. My try/catch was not wrapping enough of the logic and it was erroring between begin transaction and try-catch-rollback.

Fourth, consider placing all parts of transaction in the same try-catch, which has some benefits in making sure all parts are there and easily visible. Example:

    try {
        DB::beginTransaction();
        $this->someMethodThatMightThrow();
        DB::commit();
    } catch (Exception $e) {
        DB::rollBack();
        throw $e;
    }

That's my two cents. Hopefully useful to someone on the internet.