SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded on INSERT query

Question

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":[]}

@NNem This was not solved on Database level. I did some tweaks with my existing Laravel configuration of Queues. — Aditya Rewari, Feb 21 '22 at 04:08
@NNem I tried many resolutions, the most important of which was keeping a check on the repeat attempts of queue and the time in which the job was to finish — Aditya Rewari, Feb 21 '22 at 04:10

score 2 · Answer 1 · answered Sep 15 '20 at 03:57

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?

Enter MySQL mysql -u your_user -p
See the list of locked tables mysql> show open tables where in_use>0;
See the list of the current processes, one of them is locking your table(s) mysql> show processlist;
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

My understanding on DB is limited.. I believe my prior queries should be taking a row level lock !!! — Aditya Rewari, Sep 15 '20 at 04:06
Why should some thread lock my table..any idea when can this happen?? — Aditya Rewari, Sep 15 '20 at 04:07

score 2 · Answer 2 · answered Jun 10 '22 at 09:16

I also faced the same issue. I was running PHP code that make SELECT and INSERT requests to DB. The error was thrown only upon INSERT query.

Issue was happening because I also had a MySQL Workbench open connection on the same machine. Although, I did not run any kind of query that could cause this error, Workbench connection was somehow locking the tables and causing this error in INSERT query in PHP connection.

Closing Workbench DB connection solved issue.

But, even if you not using Workbench, run SHOW processlist; command which will list MySQL processes running. The ones with Command=Sleep are sleeping open connections. Try to close those connections by running KILL <process Id>;

Basically, sessions hold the table locks, so you need to find and kill the session that holds lock to the table you are using.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded on INSERT query

2 Answers2