innodb_lock_wait_timeout increase timeout

Question

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.?

Thank You for your help.

Answer 1

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.

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 |
+----------------------------+

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.

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.