Reproduce Gap Lock and Solution to deadlock in MySql.

Usually, deadlocks are hard to debug, and often the main reason for the occurrence of deadlock is when a set of processes are in a wait state because each process is waiting for a resource that is held by some other waiting process. Therefore, all deadlocks involve conflicting resource needs by two or more processes.

Recently, in the Production system, we found a case wherein one of the API was getting a lot of failure of transactions due to deadlock. Strange thing was that it was not a total freeze deadlock of threads instead out of two conflicting transactions one was getting successfully completed.

After going through the logs and further analysis we conclude the following things:

NOTE: For finding the last deadlock details you can use “SHOW Innodb engine Status\G”. This is really helpful in identifying which two transactions and queries are giving us the deadlock condition.

• Deadlock is occurring at the MYSQL level and
• MYSQL is doing a deadlock resolution by killing one of the transactions.

#deadlock #mysql