Understanding of innodb_lock_wait_timeout parameters
In the work of managing ORACLE, it is often found that the application downtime is caused by lock waiting. Mysql takes into account its own performance and architecture and other factors, InnoDB database engine added the parameter innodb_lock_wait_timeout to avoid too much lock waiting in the case of limited resources
1. Parameter description of innodb_lock_wait_timeout
Innodb_lock_wait_timeout refers to the longest time a transaction waits to acquire a resource. If the resource is not allocated beyond this time, the application failure will be returned.
The time unit of the parameter is seconds, the minimum can be set to 1s (usually not so small), and the maximum can be set to 1073741824 seconds (34 years, a statement lock waits for more than 30 minutes to estimate that there should be feedback from the business)
The default installation value is 50s (the company's default parameter setting)
Second, the modification of parameters
Parameters support Session and Global, and can be modified dynamically, so they can be modified in two ways.
2.1 modify through statement
Set innodb_lock_wait_timeout=100
Set global innodb_lock_wait_timeout=100
Note that the modification of global does not take effect on the current thread, only if a new connection is established
2.2 modify the parameter file / etc/my.cnf
Innodb_lock_wait_timeout = 50
III. The situation when waiting for the threshold value to be exceeded
When the lock waits for more than the set time, the following error is reported
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Summary: this document is only a simple understanding of the parameters related to MySQL lock waiting, and subsequent documents on lock waiting and management will be sorted out
. ...
The author of this article: JOHN, DBA of a listed company, focuses on the technical management of the database in his spare time, and uses technology from the perspective of management.
ORACLE technology blog: ORACLE hunter's note database technology group: 367875324 (please note ORACLE management)
. ...
