Shulou(Shulou.com)05/31 Report--

This article will explain in detail about mysql deadlock deadlock related to several system variables innodb_lock_wait_timeout is how, the content of the article is of high quality, so the editor to share to you to do a reference, I hope you have a certain understanding of the relevant knowledge after reading this article.

Preface

With the further understanding of mysql deadlock knowledge, several system variables related to deadlock are found. Combing these technical concepts below, it is easy to further grasp the concept of mysql deadlock. In this way, when there are some complex deadlock problems, it also helps to solve the problem more quickly.

Innodb_deadlock_detect system variabl

Mysql 5.7.15 introduces this system variable, and there are two available values: off and on.

The default value is on, which indicates that interlock detection is enabled.

However, students with experience in operation and maintenance may have encountered that in some highly concurrent databases, if deadlock detection is enabled, because the deadlock detection process will detect whether deadlocks are found, it will lead to a decline in database performance. because the system must consume resources to detect whether deadlocks occur on a regular basis.

It is recommended that you consider turning off this feature in the case of high concurrency. Of course, in general, just keep the default value.

Innodb_lock_wait_timeout system variabl

Innodb_lock_wait_timeout can only be used for transactions on innodb tables (not for myisam tables, so it only has table locks, not row locks). When a lock occurs, etc.

When the specified value is reached, the lock wait timeout is prompted, and the current SQL statement (note: not a transaction) of the lock timeout is automatically rolled back, which will lead to data inconsistency in the transaction to which it belongs. So you must make sure that the entire transaction is rolled back, which is controlled by the system variable innodb_rollback_on_timeout or the command line option-- innodb-rollback-on-timeout.

The innodb_lock_wait_timeout unit is seconds, and the default is 50 seconds. If a lock timeout occurs, the error message is as follows:

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

For highly concurrent OLTP database environments, it is recommended that you reduce the innodb_lock_wait_timeout system variable. Just imagine, if the lock timeout check a short, you can improve the concurrency; for OLAP database, you can consider increasing the innodb_lock_wait_timeout system variable, because OLAP does not need a high degree of concurrency, if this value is too low, the lock timeout session can wait for other locking sessions to complete, rather than immediately report the wrong rollback, after all, the amount of data updated each time, a roll back for half a day for nothing.

If innodb_deadlock_detect=on is enabled (the default), innodb_lock_wait_timeout does not work. Because the former is enabled, a deadlock transaction is rolled back as soon as a deadlock is found. However, if innodb_deadlock_detect=off is configured, innodb_lock_wait_timeout works, leaving the latter to control the rollback of the deadlock.

Mysql > show variables like'% innodb%lock%timeout%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_lock_wait_timeout | 50 | |

+-+ +

1 row in set (0.00 sec)

On the mysql deadlock deadlock related to several system variables innodb_lock_wait_timeout is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.