Shulou(Shulou.com)06/01 Report--

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)

. ...

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.