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.

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; 

Answer 3

Restarting Apache and MySQL from XAMPP solved problem for me.

Answer 4

Restarting Apache and MySQL fixed the problem.

For linux

sudo service mysql restart
sudo service apache2 restart

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/

Answer 6

Restarting the database fixed this issue for me.

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;
}