Laravel worker failure because of Dead lock at JOBS table causing SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

user_12237837 0
Viewed 3961

Issue -

I have failing workers because of Deadlock caused at JOBS table, while deleting entries of jobs.

This behaviour is frequent in occurrence, even over slight increase in traffic.

Need suggestions to avoid this Deadlock ?

Configuration -

  • Using Laravel queues with QUEUE_DRIVER=database

  • Supervisor as queue manger

  • DB is MySQL

Logs -

{"message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; 
try restarting transaction (SQL: delete from `jobs` where `id` = 215520)","context":{"exception":{"class":"Illuminate\\Database\\QueryException","message":"SQLSTATE[40001]: 
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction 
(SQL: delete from `jobs` where `id` = 
215520)","code":40001,"file":"/home/aditya/Desktop/code/Chqbook-
api/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664","previous":
{"class":"PDOException","message":"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found 
when trying to get lock; try restarting 
transaction","code":40001,"file":"/home/aditya/Desktop/code/Chqbook-
api/vendor/laravel/framework/src/Illuminate/Database/Connection.php:483"}}},"level":400,"level_n
ame":"ERROR","channel":"local","datetime":{"date":"2020-09-17 
07:22:56.546154","timezone_type":3,"timezone":"Asia/Kolkata"},"extra":[]}

{"message":"SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: insert into `jobs` (`queue`, `attempts`,
laravel supervisord innodb mysql
2 Answers
user_130489190

Deadlock and lock-wait-timeout are related problems.

You have 2 (or more) connections that are doing something with the same (or adjacent) row(s) of the same table(s).

MySQL has two actions it might take:

  • Stall one connection until the other one releases the rows. If the releasing takes "too" long, the waiting connection gives up with "wait lock timeout". Moral of the story: Don't write transactions that take more than a few seconds to complete:
  • If each is waiting for the other, you have a "deadlock". One connection's transaction is aborted. Making the queries faster may decrease the frequency of deadlocking. Being ready to re-execute a transaction is the ultimate way to deal with deadlocks.

Using MySQL as a queue manager tempts you to

  1. grab an item from the queue
  2. process it
  3. remove the item from the query

It is logical to do all 3 of those steps in a transaction. But, if step 2 takes "a long time", you are begging for one of the problems listed above.

A better approach is:

BEGIN  
1. Grab an item from the queue
2. Mark it as taken
COMMIT  

3. Process it at your leisure

BEGIN
4. Remove the item from the queue
COMMIT
user_17668310
Related