9

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.

Umesh Sehta
  • 10,555
  • 5
  • 39
  • 68

1 Answers1

13

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • At least for me on MySQL 5.6 it did not work out: ```mysql> SET GLOBAL innodb_lock_wait_timeout = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ | 50 | +----------------------------+ 1 row in set (0.00 sec) ``` – Ain Tohvri Dec 07 '17 at 14:13
  • 1
    @AinTohvri - Note `GLOBAL` versus `SESSION`. – Rick James Dec 07 '17 at 16:18
  • I change both variables to 1 and it still takes 50s for the 2nd tx to timeout while the 1st tx locks the same row in table. Would you please explain why "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." ? Thanks @RickJames – David Jun 21 '22 at 06:22
  • @David - Web servers start fresh with each invocation. Nothing can be saved from one page to the next unless you save it outside. Places to save info: files, cookies, database. The database transaction goes away when the connection goes away. And the connection goes away when the web page terminates. – Rick James Jun 21 '22 at 16:21
  • This does not explain the long timeout. Expectation: LockTimeout=1; try to get lock that's acquired already; wait 1s; fail. Reality: it waits much longer before timeout. – Tomáš Fejfar Jul 05 '22 at 16:01
  • 1
    @TomášFejfar - Please start a new Question with your specifics. Please include the code inside the transactions, when and where timeouts are set, etc. (I'm worried that we are not seeing a subtle difference between this Question and your situation.) – Rick James Jul 05 '22 at 16:06
  • https://stackoverflow.com/questions/72915956/innodb-lock-wait-timeout-timeout-is-ignored – Tomáš Fejfar Jul 08 '22 at 19:04