What is the method to reduce the InnoDB deadlock occurrence? MySQL Learning

The deadlock situation occurs when different transactions have a lock on each other's needs, which leads to infinite waiting between each other. Deadlocks can occur when different transactions impose locks on multiple identical tables and identical rows, but the order in which transactions operate on tables differs.

To reduce the deadlock, avoid using the lock table statement, and try to make the range of data changes as small and fast as possible, and when different transactions want to modify more than one table or large amount of data, make sure that the order of the modifications is consistent between the transactions as much as possible. By default, the deadlock detection feature under InnoDB is turned on, and when InnoDB discovers a deadlock, one of the transactions is rolled back as a victim.

The Innodb_lock_wait_timeout parameter configures whether the auto-detect function is on, and if it is off, InnoDB uses the innodb_lock_wait_timeout parameter to automatically roll back the transaction waiting for enough time. The last occurrence of a deadlock can be viewed through the show Engine INNODB status statement.

InnoDB deadlock detection and rollback? The deadlock detection feature is turned on by default, and when a deadlock occurs, InnoDB automatically detects and sacrifices one or more of these transactions to allow other transactions to continue. InnoDB chooses to sacrifice the transaction is often the cost relatively small transaction, its cost computation is according to the transaction insert,update, the delete's data row scale decides. If a statement in a transaction is rolled back because of an error, the lock on the statement may also remain because InnoDB only stores the row lock information, not the row lock, which is generated by which statement in the transaction. If a SELECT statement invokes a function in a transaction, and a statement in the function fails, the statement is rolled back and the entire transaction is rolled back if rollback is executed at the end of the entire transaction. The deadlock detection function can be turned off by the Innodb_deadlock_detect parameter, but only with the Innodb_lock_wait_timeout function to release the lock wait.

So, what is the way to reduce the occurrence of deadlocks?

In transactional databases, deadlocks are a classic problem, but as long as the frequency of occurrence is not high, the deadlock problem does not need to worry too much. There are two ways to view deadlocks: 1. The show Engine InnoDB Status command allows you to view the last deadlock condition. 2. The innodb_print_all_deadlocks parameter configuration allows you to print all of the deadlock information to the MySQL error log.

Ways to reduce deadlocks: 1. Minimizing the size of transactions and reducing the time for transaction execution can reduce the probability of impact; 2. Timely implementation of commit or rollback, to release the lock as soon as possible; 3. You can choose a lower isolation level, such as if you want to use Select...for The update and Select...lock in share mode statements can use the read-commit data isolation level; 4. When you want to access multiple table data or to access different sets of rows for the same table, ensure that the order of each access is the same as possible. For example, multiple statements can be encapsulated in a stored procedure, and the method of invoking the same stored procedure can reduce the occurrence of deadlocks.

Internet companies mostly use the database is MySQL, want to stand out from the many IT workers, need to have advanced technology, learning to add value is essential. The way of learning is your insistence. Old boy Education MySQL DBA course, after several update courses, to eliminate the theory, the whole enterprise real case combined with theoretical teaching, want to deep learning MySQL DBA knowledge, can pay attention to the old boy education.

What is the method to reduce the InnoDB deadlock occurrence? MySQL Learning