What is innodb_lock_wait_timeout ?
In MySql innodb_lock_wait_timeout is an Innodb transaction wait time in seconds for a row lock.
An another transaction has to wait for specified innodb_lock_wait_timeout if a transaction is taking place.
innodb-lock-wait-timeout in a system variable in global and session scope this variable can set dynamically to both scopes.
We can set this value as integer from 1 to 1073741824 by default its value is 50.
You can check default value of innodb-lock-wait-timeout by query
show variables like 'innodb_lock_wait_timeout';
OR
SHOW GLOBAL VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';
OR
SELECT @@innodb_lock_wait_timeout;
All shows the default value.
you can also chek other Server System Variables
Property Value for innodb_lock_wait_timeout
Property | Value |
---|---|
Command-Line Format | --innodb-lock-wait-timeout=# |
System Variable | innodb-lock-wait-timeout |
Scope | Global, Session |
Dynamic | Yes |
Type | Integer |
Default Value | 50 |
Minimum Value | 1 |
Maximum Value | 1073741824 |
Show affected tables
Get affected tables you can use following query
SHOW ENGINE INNODB STATUS\G
It shows locking information in MySql
When to increase or Decrease innodb-lock-wait-timeout value ?
You can descrese innodb_lock_wait_timeout for OLTP or High interactive systems.
If you have any high time consuming operation long queries in Data-ware house then you can increase its time
A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:
1 | ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction).
How to update innodb-lock-wait-timeout value ?
to show default value of innodb_lock_wait_timeout we use
1 | show variables like 'innodb_lock_wait_timeout'; |
innodb_lock_wait_timeout can be set at run time with global and session scope
1 2 3 4 5 6 | SET GLOBAL innodb_lock_wait_timeout = 100; SELECT @@innodb_lock_wait_timeout; SET SESSION innodb_lock_wait_timeout = 100; SELECT @@innodb_lock_wait_timeout; |
SELECT @@innodb_lock_wait_timeout;
will show innodb_lock_wait_status
