2

I am getting Lock wait timeout exceeded error at my laravel jobs at frequent intervals

I am unable to trace, how could an INSERT statement not get a LOCK

As per my understanding an INSERT will be made at a new row, so no issues of waiting for lock

MySQL is used as database

Kindly suggest how to deal, or what could be the root cause ?

Logs:

{"message":"SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting 
transaction (SQL: insert into `jobs` (`queue`, `attempts`, `reserved_at`, `available_at`, 
`created_at`, `payload`) values (default, 0, , 1599989867, 1599989867, 
{\"displayName\":\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusinessReward
Job\",\"job\":\"Illuminate\\\\Queue\\\\CallQueuedHandler@call\",\"maxTries\":null,\"timeout\":nu
ll,\"timeoutAt\":null,\"data\":
{\"commandName\":\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusinessReward
Job\",\"command\":\"O:68:\\\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusi
nessRewardJob\\\":8:{s:7:\\\"\\u0000*\\u0000data\\\";a:4:
{s:7:\\\"user_id\\\";s:10:\\\"1000210290\\\";s:10:\\\"event_type\\\";s:20:\\\"KHATA_BUSINESS_ADD
ED\\\";s:10:\\\"user_count\\\";i:1;s:12:\\\"global_count\\\";i:890;}s:6:\\\"\\u0000*\\u0000job\\
";N;s:10:\\\"connection\\\";N;s:5:\\\"queue\\\";N;s:15:\\\"chainConnection\\\";N;s:10:\\\"chainQ
ueue\\\";N;s:5:\\\"delay\\\";N;s:7:\\\"chained\\\";a:0:
{}}\"},\"NewRelicID\":\"VgUGUlBTChADVVFQAAEEX1M=\",\"NewRelicTransaction\":\"PxQDUgNUCFcABlkDBVB
UV1UBFB8EBw8RVU4aVgtdAwJXVVtZUFNSAVUHB0NKQQ1QCF1WUlYFFTs=\"}))",
"context":{"exception":{"class":"Illuminate\\Database\\QueryException","message":"SQLSTATE[HY000]: 
General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: insert into `jobs` (`queue`, `attempts`, `reserved_at`, `available_at`, `created_at`, `payload`) values (default, 0, , 1599989867, 1599989867, 
{\"displayName\":\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusinessReward
Job\",\"job\":\"Illuminate\\\\Queue\\\\CallQueuedHandler@call\",\"maxTries\":null,\"timeout\":nu
ll,\"timeoutAt\":null,\"data\":
{\"commandName\":\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusinessReward
Job\",\"command\":\"O:68:\\\"Modules\\\\RewardAnalyzer\\\\Jobs\\\\RewardEvents\\\\KhataFirstBusi
nessRewardJob\\\":8:{s:7:\\\"\\u0000*\\u0000data\\\";a:4:
{s:7:\\\"user_id\\\";s:10:\\\"1000210290\\\";s:10:\\\"event_type\\\";s:20:\\\"KHATA_BUSINESS_ADD

ED\\\";s:10:\\\"user_count\\\";i:1;s:12:\\\"global_count\\\";i:890;}s:6:\\\"\\u0000*\\u0000job\\
\";N;s:10:\\\"connection\\\";N;s:5:\\\"queue\\\";N;s:15:\\\"chainConnection\\\";N;s:10:\\\"chain
Queue\\\";N;s:5:\\\"delay\\\";N;s:7:\\\"chained\\\";a:0:
{}}\"},\"NewRelicID\":\"VgUGUlBTChADVVFQAAEEX1M=\",\"NewRelicTransaction\":\"PxQDUgNUCFcABlkDBVB
UV1UBFB8EBw8RVU4aVgtdAwJXVVtZUFNSAVUHB0NKQQ1QCF1WUlYFFTs=\"}))","code":0,"file":"/var/www/api/ve
ndor/laravel/framework/src/Illuminate/Database/Connection.php:664","previous":
{"class":"PDOException","message":"SQLSTATE[HY000]: General error: 1205 Lock wait timeout 
exceeded; try restarting 
transaction","code":0,"file":"/var/www/api/vendor/laravel/framework/src/Illuminate/Database/Conn
ection.php:458"}}},"level":400,"level_name":"ERROR","channel":"production","datetime":
{"date":"2020-09-13 15:08:38.660818","timezone_type":3,"timezone":"Asia/Kolkata"},"extra":[]}
Aditya Rewari
  • 1,782
  • 15
  • 27

1 Answers1

1

You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statement.

What is happening is, some thread is holding a record lock on some record for too long, and your thread is being timed out.

If you use MySQL you can check the status in

SHOW ENGINE INNODB STATUS

Or

You should FORCE UNLOCK for locked tables in MySQL, but this is dangerous, i only recommend it if you need to fix it fast

How to do that?

  1. Enter MySQL mysql -u your_user -p

  2. See the list of locked tables mysql> show open tables where in_use>0;

  3. See the list of the current processes, one of them is locking your table(s) mysql> show processlist;

  4. Kill one of these processes mysql> kill <put_process_id_here>;

You can also find this answer here: Getting “Lock wait timeout exceeded; try restarting transaction” even though I'm not using a transaction

Ramadhani Baharzah
  • 17
  • 7