Laravel - lock timeout exceeded

Question

I have many transactions in my code, and if an error occurs during one of these transactions that does not cause a commit or rollback, then the database is locked, and any subsequent attempts to access the database lead to 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(); 

Thus, even php-artisan: refresh or php artisan migrate: reset migration stops working. How can I fix this?

Answer 1

I see a repeating question

How to debug wait timeout wait timeout on MySQL server?

You should consider increasing the value of waiting for lock wait for InnoDB by setting innodb_lock_wait_timeout, the default value is 50 seconds

 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 a larger value in /etc/my.cnf forever 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 can also just set it for the duration of the session.

 SET innodb_lock_wait_timeout = 120; 

Answer 2

Here are some tips in my experience ...

If you are doing test development, isolate which combination of tests produces an error. Use any mechanism provided by your ecosystem to run tests selectively (example: @group only for testing methods and phpunit --group only )

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

Thirdly, pay attention to open transactions, i.e. start without rolling back or fixing. It turned out to be just my problem. My attempt / catch did not wrap enough logic, and this was a mistake between the start of the transaction and the rollback from try-catch.

Fourth, consider placing all parts of a transaction in the same try-catch, which has several advantages in making sure that all parts are easily visible. Example:

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

These are my two cents. Hope helpful to someone online.

Answer 3

This problem is related to mysql database. I came across the same step and successfully completed the following steps.

Locate usr / local / var / mysql / your_computer_name.local.err file and find out more error information

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

Probably a permission issue

• Find if mysql is running and kill it

ps -ef | grep mysql

kill -9 PID

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

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 4

Restarting the database fixed this problem for me.