Setting innodb_lock_wait_timeout

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';

SHOW GLOBAL VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';

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:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

show variables like 'innodb_lock_wait_timeout';

1	show variables like 'innodb_lock_wait_timeout';

innodb_lock_wait_timeout can be set at run time with global and session scope

 SET GLOBAL innodb_lock_wait_timeout = 100;
 SELECT @@innodb_lock_wait_timeout;


 SET SESSION innodb_lock_wait_timeout = 100;
 SELECT @@innodb_lock_wait_timeout;

SET GLOBAL innodb_lock_wait_timeout = 100;

SELECT @@innodb_lock_wait_timeout;

SET SESSION innodb_lock_wait_timeout = 100;