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

OR

SHOW GLOBAL VARIABLES LIKE '%INNODB_LOCK_WAIT_TIMEOUT%';

OR

SELECT @@innodb_lock_wait_timeout;

innodb_lock_wait_timeout-min

All shows the default value.

Property Value for innodb_lock_wait_timeout

PropertyValue
Command-Line Format--innodb-lock-wait-timeout=#
System Variable innodb-lock-wait-timeout
ScopeGlobal, Session
DynamicYes
TypeInteger
Default Value50
Minimum Value1
Maximum Value1073741824

Show affected tables

Get affected tables you can use following query

SHOW ENGINE INNODB STATUS\G

It shows locking information

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:

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

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

SELECT @@innodb_lock_wait_timeout;

will show innodb_lock_wait_status

innodb-lock-wait-timeout value

Read More

  1. Export and Import MySql Database using phpMyAdmin
  2. Set time zone in mysql
  3. Changing the default value of column in MySQL
  4. PHP MySQL CRUD Tutorial
  5. PHP File Upload Example
  6. How to use CKEditor in PHP
  7. Setting innodb_lock_wait_timeout
  8. Granting Privileges to users
  9. Show engine innodb status
  10. Finding all column name of table