innodb_lock_wait_timeout增加超时 [英] innodb_lock_wait_timeout increase timeout

问题描述

我正在使用MySQL数据库，并尝试在插入后立即更新记录，因此出现以下错误ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction. 所以我通过以下查询减少超时:-

I am using MySQL database and trying to update records just after insert so I am getting following error ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction. So I decrease timeout by following query:-

set GLOBAL innodb_lock_wait_timeout=1

所以我的问题是: 可以吗?会导致其他问题，例如性能问题吗?

So my question is:- is it ok to do that? Will it lead to other problems like performance issue etc.?

谢谢您的帮助.

推荐答案

如果这是一个Web应用程序，并且您试图将事务从一页挂到下一页，请不要；它不起作用.

If this is a web application and you are trying to hang onto the transaction from one page to the next, don't; it won't work.

紧随其后"是什么意思?如果您在这两个语句之间什么都不做，那么即使是1秒的超时也应该足够大.

What do you mean by "just after"? If you are doing nothing between the two statements, even a timeout of 1 second should be big enough.

mysql> SET GLOBAL innodb_lock_wait_timeout = 1;
mysql> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
mysql> SET SESSION innodb_lock_wait_timeout = 1;
mysql> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                          1 |
+----------------------------+

解释变量的GLOBAL vs SESSION:连接开始时，GLOBAL值用于初始化 SESSION值.之后，您可以更改SESSION值以影响您的工作.并且更改GLOBAL值不会影响您的当前连接.

To explain GLOBAL vs SESSION for VARIABLES: The GLOBAL value is used to initialize the SESSION value when your connection starts. After that, you can change the SESSION value to affect what you are doing. And changing the GLOBAL value has no effect on your current connection.

将超时更改为1是相当安全的(一旦您了解GLOBAL vs SESSION).唯一会改变的是得到该错误的频率.

Changing the timeout to 1 is quite safe (once you understand GLOBAL vs SESSION). The only thing that will change is the frequency of getting that error.

这篇关于innodb_lock_wait_timeout增加超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！