MySQL Deadlock detection and rollback

Recently encountered "TOO deep OR LONG SEARCH in the LOCK TABLE waits-for GRAPH, WE'll roll back following TRANSACTION".

Re-review the benefit of a lot of, which the deadlock of the rules, in fact, we have 5 years ago to solve the first version of the second kill scene has been involved, and the idea is very similar, if there is time, I will add a number of articles said if the shutdown deadlock detection on a single-row update can improve how much performance.

The following code shows:

“

If LATEST DETECTED DEADLOCK InnoDB The section of Monitor output includes a message stating, "TOO deep OR LONG SEARCH in the LOCK tabl E waits-for GRAPH, WE'll roll back to following TRANSACTION, "This indicates that number of transactions on the WAIT-FO R list has reached a limit of 200. A wait-for list that exceeds transactions is treated as a deadlock and the transaction attempting to check the WAIT-FO R list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the WA It-for list.

”

In InnoDB source code lock/in the Lock0lock.c file, two constants are defined:/*restricts the length of search we 'll do in the waits-for graph of transactions*/#defineLock_max_n_steps_in_deadlock_check 1000000/*restricts the recursion depth of the search we 'll do in the waits-for graph of transactions*/#defineLock_max_depth_in_deadlock_check 200The following code is then shown in the function lock_deadlock_occurs () that checks whether a deadlock is generated: RET= Lock_deadlock_recursive (TRX, TRX,Lock, &cost,0);Switch(ret) { Caselock_exceed_max_depth: Generate Deadlock ... Break;} where the lock_deadlock_recursive () function is a recursive function, it examines its own recursive depth, which has the following code: Ibool Too_far= depth >Lock_max_depth_in_deadlock_check|| *cost >Lock_max_n_steps_in_deadlock_check, .....if(Too_far) {return(lock_exceed_max_depth); }

So InnoDB calls the lock_deadlock_occurs () check when checking for a deadlock, and this function calls the lock_deadlock_recursive () recursive check the number of locks (I don't know if that's true?), When the recursion depth DEPTH is greater than the constant Lock_max_depth_in_deadlock_check introduced at the beginning, or cost (unclear what this represents) is greater than the constants described at the beginning Lock_max_n_steps_in_deadlock _check, it is assumed that a deadlock has occurred.

Data Source:

http://blog.csdn.net/sunmun/article/details/50088381

Https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-detection.html

Http://www.cnblogs.com/zemliu/p/3502395.html

MySQL Deadlock detection and rollback