8.3.3. Dynamically Changing innodb_lock_wait_timeout

When a transaction is waiting for a resource, it will wait for the resource to become free, or stop waiting and return with the error

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

The length of time a transaction will wait for a resource before giving up is determined by the value of the configuration parameter innodb_lock_wait_timeout. The default setting for this parameter is 50 seconds. The minimum setting is 1 second, and values above 100,000,000 disable the timeout, so a transaction will wait forever. Following a timeout, the SQL statement that was executing will be rolled back. (In MySQL 5.0.12 and earlier, the transaction rolled back.) The user application may try the statement again (usually after waiting for a while), or rollback the entire transaction and restart.

Before InnoDB Plugin 1.0.2, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server. Beginning with the InnoDB Plugin 1.0.2, the configuration parameter innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION commands. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_lock_wait_timeout, which affects only that client.


User Comments
  Posted by Wagner Bianchi on November 29, 2013
Actually, you don't need to user the GLOBAL keyword to configure that environment variable globally, just set the new value and it will persists for the new connections on. The following code list testify it:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3168334
Server version: 5.5.13-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 300 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 300 |
+----------------------------+
1 row in set (0.00 sec)

mysql> set innodb_lock_wait_timeout=900;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 900 |
+----------------------------+
1 row in set (0.00 sec)

# reconnecting again

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3168334
Server version: 5.5.13-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 900 |
+----------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
Sign Up Login You must be logged in to post a comment.