One time MySQL (InnoDB storage engine) dead lock catch Worm

Objective

Any system needs to focus on the production environment error log at any stage, and in recent months, it has been discovered that there are occasional database deadlock situations. Previously encountered the database class errors are mostly caused by the SQL syntax errors, came to the new owner after the first encounter deadlock situation, was engaged in game development, is now engaged in the development of e-commerce, may be different projects different business reasons, look up a variety of information found, I think wrong: (. General business bottleneck in the database layer, the problem of the database layer needs to focus on, thought that the deadlock situation is very serious problem, this to divide the situation, the occasional deadlock on the business will not have much impact, I think wrong: (.

Insect discovery
The first time I found a deadlock, it was amazing. Don't know what the reason is? Don't know how to solve it? Do not know will affect the business? Did not understand this problem, fear is the business time bomb, every day in the heart is not practical. Bugs found are as follows:

Dead lock
Before the database records operations, the current thread needs to request the relevant lock before the SQL statement is executed after the lock is acquired. Locks are divided into table and row locks based on granularity (not all storage engines support row locks), and locks are divided into exclusive and shared locks based on type (different types of locks are required for various operations, and different lock types behave differently). The fewer resources, the more likely the deadlock will be, such as a storage engine that supports only table locks, is less likely to occur than a storage engine that supports row locks.
When a concurrent transaction occurs, when there are multiple transactions waiting for each other's resources to be released, the transaction has not been processed, the resources that have been locked will not be released, and we are waiting for each other to stand still, so that the deadlock will continue to occur.
Deadlock-related databases cannot be avoided, not MySQL database exclusive. The MySQL database automatically checks for deadlock conditions and, when found, rolls back a simpler transaction and returns a fault to the thread. How to determine which transaction is simpler, judging by the number of record rows affected by the transaction, the smaller the number of records is considered simpler.

Diagnostic analysis
When a deadlock occurs, in order to solve this problem, it is necessary to diagnose and analyze the context information of the deadlock and the related execution statements so as to know how to avoid it. The MySQL database only holds the last deadlock transaction, and if more than 2 transactions are deadlocked at the same time, save up to 2 transaction information. Execute mysql client command: SHOW ENGINE INNODB Status obtains information about the last transaction deadlock. If this is the version after MySQL 5.6, you can open the global variable innodb_print_all_deadlocks=on so that the deadlock related information is saved to the MySQL error log. If it is not supported by the version of this variable, you can use the timed execution of the client command to collect the deadlock information, and then save to the log file, each transaction has a unique number, can be based on this weight. When deadlocks occur frequently, attention needs to be paid, and when there are occasional occurrences, there is no need to pay attention. The deadlock Information format is as follows, the content of different MySQL version will be a little different, the relevant instructions can see resources one.

Preventive measures
Knowing what a deadlock is, and analyzing diagnostic log information, you can do the right thing, but there are some general principles that you can follow:
1 Keep transactions as simple as possible and execute quickly;
2 Minimize the number of rows affected by the transaction and the related tables involved;
3 Avoid the use of FOREIGN key constraints, in fact, most applications allow data redundancy;
4 The transactions that affect the large number of rows try to use the same sorting filter;
5 database concurrency is not high business, you can execute the statement in some way, only one at a time, by checking the data know one way: to create a table, the table always has only one record, each transaction through contention for this lock to achieve linear execution, feel this should not be used: (;
6 Some scenes deadlock will not be checked, setting the expiration time of the lock is very important, MySQL can be set by the option Innodb_lock_wait_timeout;

Degree of harm
Lock resources can not be released in a timely manner, it will affect the database concurrency processing, if there are frequent deadlocks, need to take timely measures to deal with, if only occasionally, the business logic to catch a deadlock error may take a retry to execute the transaction, the business will not have any impact, in short, the specific problem needs specific analysis:)

Postscript
First encountered this problem a little nervous, thought is very serious problem, do not know the relevant background knowledge well, it seems that people are ugly or more reading AH (:

Resources
"1" How to deal with MySQL deadlocks
https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/
"2" deadlock
Http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_deadlock
"3" Deadlock Detection and Rollback
Http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-detection.html
"4" How to Cope with deadlocks
Http://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks.html

One time MySQL (InnoDB storage engine) dead lock catch Worm