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

This article mainly explains "what is the method of mysql innodb_deadlock_detect detection and processing". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of mysql innodb_deadlock_detect detection and processing"?

Innodb_deadlock_detect is a system variable of MySQL.

Version: 5.7.15

Command line format:-innodb-deadlock-detect

Scope of influence: Global

Parameter type: boolean

Default value: ON

Function: this option uses the deadlock detection feature that disables MySQL. On highly concurrent systems, deadlock detection may slow down when many threads wait for the same lock. Sometimes, when a deadlock occurs, it may be more efficient if deadlock detection is disabled, so that you can rely on the settings of innodb_lock_wait_timeout for transaction rollback.

MySQL turns deadlock detection on by default, and InnoDB automatically detects the transaction that sent the deadlock and rolls back one or all of the transactions that caused the deadlock. InnoDB selects a less weighted transaction to roll back and forth among the fifteen that causes the deadlock, and this weight value may be determined by the number of rows of the transaction insert, updated, and deleted.

If innodb_table_locks = 1 (the default) and autocommit = 0, InnoDB is aware of the table lock, and the upper MySQL layer knows the row-level lock. Otherwise, InnoDB cannot detect table locks set by MySQL LOCK TABLES statements or deadlocks set by storage engines other than InnoDB. These situations are resolved by setting the value of the innodb_lock_wait_timeout system variable.

When InnoDB performs a full rollback of the transaction, all locks set by the transaction are released. However, if a single SQL statement is rolled back due to an error, some of the locks set by the statement may be retained. This is because InnoDB stores row locks in a format so that you cannot later know which lock is set by which statement.

If SELECT calls a function stored in a transaction and the statement in the function fails, the statement is rolled back. In addition, if ROLLBACK is executed after that, the entire transaction will be rolled back.

If the most recent deadlock detection section of the output of the InnoDB monitor contains a message indicating TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION, this indicates that the length of the waiting transaction list has reached the limit of200. A waiting list of more than 200 transactions is considered a deadlock and the transaction that attempts to check the waiting list will be rolled back. The same error can occur if the locking thread must view more than 1000000 locks owned by the transaction on the waiting list.

Disable deadlock detection

You can turn off deadlock detection with the option: innodb_deadlock_detect.

At this point, I believe that you have a deeper understanding of "what is the method of mysql innodb_deadlock_detect detection and processing". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.