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?

Decoration

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.

Divest

I was getting this error when phpunit ran and couldn't figure out why. I found @Stoutie's answer here very helpful in tracking down the cause and eventually found an overridden tearDown() method where I wasn't calling parent::tearDown(). Basically, if you don't call parent::tearDown() then the transaction was still open and a rollback/disconnect never occurred. – Scare

I was missing the parent::tearDown() as well in my tearDown method and saw the same wait lock timeout problem. – Ghetto

wow, I was missing a call to parent::tearDown() too, that's after 24 hours of debugging. Thanks @Stoutie!! – Prelatism

I was getting this issue until I realised that there is a typo in the try-catch block, where I accidentally typed \Exceptio instead of \Exception – Knapp

Answer 2

I see duplicate question

How to debug Lock wait timeout exceeded on MySQL?

You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)

You can set it to higher value in /etc/my.cnf permanently with this line

[mysqld]
innodb_lock_wait_timeout=120

and restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120; 

You could also just set it for the duration of your session

SET innodb_lock_wait_timeout = 120; 

Helmholtz

I think my issues is that the lock does not expire. After the error I described in the question, I wait 5 minutes, and it's still locked. – Decoration

Yes, I experience this issue as well and the resulting lock appears to be indefinite in my case until manual intervention – Weywadt

Not sure if this will help you since you're not using queues, but I was getting your same error when processing queues using the database driver. I was using the php artisan queue:work --daemon command with supervisor and just 1 worker / process. Switching to the the queue:listen command solved it for me. – Weywadt

Answer 3

Restarting Apache and MySQL from XAMPP solved problem for me.

Girasol

Answer 4

Restarting Apache and MySQL fixed the problem.

For linux

sudo service mysql restart
sudo service apache2 restart

Radish

Answer 5

This problem is related to mysql database. I had faced the same and by following steps solved it successfully.

Find usr/local/var/mysql/your_computer_name.local.err file and understand the more information about error

Location : /usr/local/var/mysql/your_computer_name.local.err

It's probably problem with permissions

1. Find if mysql is running and kill it

ps -ef | grep mysql

kill -9 PID

where PID is second column value 2. check ownership of mysql

ls -laF /usr/local/var/mysql/

if it is owned by root, change it mysql or your user name



sudo chown -R mysql /usr/local/var/mysql/

Lockridge

Answer 6

Restarting the database fixed this issue for me.

Zipah

Restarting only kills the query holding the lock. It does not solve the issue. Running show processlist and killing the corresponding id to the transaction holding the lock is better than restarting. – Armorer

Answer 7

It is very likely your exception is not being caught because it's not an exception but a fatal error or some other kind of \Throwable.

You can use the DB::transaction method with a callback which is a safer alternative because it handles all of this for you and is almost equivalent to your code (Except it catches \Throwable as well)

DB::transaction(function () {
    $this->someMethodThatMightThrow();
});

Or make sure you catch everything with

DB::beginTransaction();
try {
  //..
  DB::commit();
} catch (\Throwable $e) {
  DB::rollback();
  throw $e;
}

Frisian