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

Question

我在我的 laravel 作业中经常收到超出锁定等待超时错误

我无法追踪，INSERT 语句怎么可能没有得到LOCK

据我了解，INSERT 将在新行进行，因此没有等待锁定的问题

MySQL 用作数据库

请建议如何处理，或者根本原因是什么？

日志:

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

Answer 1

您正在使用交易； autocommit 不会禁用事务，它只是让它们在语句末尾自动提交。

发生的情况是，某些线程在某些记录上持有记录锁的时间过长，您的线程正在超时。

如果你使用 MySQL，你可以检查状态

SHOW ENGINE INNODB STATUS

或者

你应该在 MySQL 中对锁定的表进行 FORCE UNLOCK，但这很危险，我只在你需要快速修复它时才推荐它

怎么做？

1. 进入MySQL mysql -u your_user -p

2. 查看锁定表列表 mysql> show open tables where in_use>0;

3. 查看当前进程列表，其中之一正在锁定您的表 mysql> show processlist;

4. 终止其中一个进程 mysql> kill <put_process_id_here>;

您也可以在这里找到这个答案:Getting “Lock wait timeout exceeded; try restarting transaction” even though I'm not using a transaction