Laravel - 超出锁定等待超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38866560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel - Lock wait timeout exceeded
提问by Snowball
I have a lot of transactions in my code, and if an error occurs in executing in one of these transactions that doesn't trigger commit or rollback, then the database is locked and any subsequent attempts to access the database results in this:
我的代码中有很多事务，如果在这些不触发提交或回滚的事务之一中执行时发生错误，则数据库将被锁定，任何后续访问数据库的尝试都会导致：
production.ERROR: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction in /home/forge/default/vendor/laravel/framework/src/Illuminate/Database/Connection.php:390
production.ERROR: PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction in /home/forge/default/vendor/laravel/framework/src/Illuminate/Database/Connection.php:390
In the Controller:
在控制器中：
DB::beginTransaction();
try {
//Code that uses exec() to process some images. <-- If code breaks here, then the above error appears on subsequent requests.
//Code that accesses the database
}
catch(\Exception $e){
DB::rollback();
throw $e;
}
DB::commit();
So even php artisan migrate:refresh or php artisan migrate:reset stops working as well. How should I go about fixing this?
因此，即使 php artisan migrate:refresh 或 php artisan migrate:reset 也会停止工作。我应该如何解决这个问题？
回答by Tính Ng? Quang
I see duplicate question
我看到重复的问题
How to debug Lock wait timeout exceeded on MySQL?
如何调试 MySQL 上超出的锁定等待超时？
You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec
您应该考虑通过设置 innodb_lock_wait_timeout 来增加 InnoDB 的锁等待超时值，默认为 50 秒
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.01 sec)
You can set it to higher value in /etc/my.cnf permanently with this line
您可以使用此行在 /etc/my.cnf 中永久将其设置为更高的值
[mysqld]
innodb_lock_wait_timeout=120
and restart mysql. If you cannot restart mysql at this time, run this:
并重新启动mysql。如果此时无法重新启动 mysql，请运行以下命令：
SET GLOBAL innodb_lock_wait_timeout = 120;
You could also just set it for the duration of your session
您也可以在会话期间设置它
SET innodb_lock_wait_timeout = 120;
回答by Stoutie
Here's some tips from my experience ...
以下是我的一些经验的提示...
If you are doing test driven development, isolate which combination of tests produce the error. Utilize whatever mechanism your ecosystem provides to selectively run tests (Example: @group onlyon test methods and phpunit --group only)
如果您正在进行测试驱动开发，请隔离哪些测试组合会产生错误。利用您的生态系统提供的任何机制来有选择地运行测试（例如：@group only关于测试方法和phpunit --group only）
Next, reduce the lock wait timeout (SET GLOBAL innodb_lock_wait_timeout = 10). This way you get quick feedback and don't spend your whole day waiting for tests to run. Mileage may vary. Adjust to your specific conditions.
接下来，减少锁定等待超时 ( SET GLOBAL innodb_lock_wait_timeout = 10)。通过这种方式，您可以获得快速反馈，而不必花费一整天时间等待测试运行。里程可能会有所不同。根据您的具体情况进行调整。
Thirdly, look for unclosed transactions, ie begin without rollback or commit. This turned out to be exactly what my problem was. My try/catch was not wrapping enough of the logic and it was erroring between begin transaction and try-catch-rollback.
第三，寻找未关闭的事务，即开始时没有回滚或提交。结果证明这正是我的问题所在。我的 try/catch 没有包装足够的逻辑，并且在开始事务和 try-catch-rollback 之间出错。
Fourth, consider placing all parts of transaction in the same try-catch, which has some benefits in making sure all parts are there and easily visible. Example:
第四，考虑将事务的所有部分放在同一个 try-catch 中，这有利于确保所有部分都在那里并且很容易看到。例子：
try {
DB::beginTransaction();
$this->someMethodThatMightThrow();
DB::commit();
} catch (Exception $e) {
DB::rollBack();
throw $e;
}
That's my two cents. Hopefully useful to someone on the internet.
那是我的两分钱。希望对互联网上的人有用。
回答by Dazzle
Restarting the database fixed this issue for me.
重新启动数据库为我解决了这个问题。
回答by Amitesh
This problem is related to mysql database. I had faced the same and by following steps solved it successfully.
这个问题与mysql数据库有关。我遇到了同样的问题，并通过以下步骤成功解决了它。
Find usr/local/var/mysql/your_computer_name.local.err file and understand the more information about error
找到 usr/local/var/mysql/your_computer_name.local.err 文件并了解有关错误的更多信息
Location : /usr/local/var/mysql/your_computer_name.local.err
位置：/usr/local/var/mysql/your_computer_name.local.err
It's probably problem with permissions
应该是权限问题
- Find if mysql is running and kill it
- 查找mysql是否正在运行并杀死它
ps -ef | grep mysql
kill -9 PID
ps -ef | mysql 查询
杀死 -9 PID
where PID is second column value 2. check ownership of mysql
其中 PID 是第二列值 2. 检查 mysql 的所有权
ls -laF /usr/local/var/mysql/
ls -laF /usr/local/var/mysql/
if it is owned by root, change it mysql or your user name
?
sudo chown -R mysql /usr/local/var/mysql/
须藤 chown -R mysql /usr/local/var/mysql/
