CODEWITHSUNDEEP
phpmysqllaravellocking
Aditya Rewari
Aditya Rewari

Reputation: 2707

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

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

Upvotes: 4

Views: 10078

Answers (2)

Nuryagdy Mustapayev
Nuryagdy Mustapayev

Reputation: 785

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.

Upvotes: 2

Ramadhani Baharzah
Ramadhani Baharzah

Reputation: 77

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

Upvotes: 2

Related Questions