Lock conflict is a problem frequently faced by MySQL database businesses, and its symptoms vary by scenario. In some scenarios, it may directly result in business crash, while in other scenarios, it may be hard to perceive and cause problems such as incorrect and messy data.
The variety, complexity, and imperceptibility of lock conflict scenarios create issues that require a skilled DBA to resolve. Troubleshooting such issues is also time-consuming.
The exception diagnosis feature of DBbrain includes dozens of lock diagnoses such as deadlocks, row lock waits, table locks, read-only locks, DDL/SELECT/DML lock waits, and SQL MDL lock waits, helping you easily solve lock conflicts in an efficient and professional manner.
This document uses the common row lock wait and deadlock as examples to describe how you can use DBbrain to solve a lock conflict.
On the "Symptom Description" tab on the event details page, you can view the database statement running conditions when the row lock wait event occurs.
Switch to the "Intelligent Analysis" tab and view the cause of the row lock wait event.
In the row lock wait transaction, you can clearly view the status of blocked transaction statements. For example, the DELETE statement in the figure below is in LOCK WAIT state.
Based on the holdlock transaction result, quickly locate the current status and ID of the transaction with a row lock.
The performance monitoring curve displays the trends of the number of row lock waits.
Switch to the "Expert Suggestion" tab and solve the lock conflict problem as prompted. For example, you can run the
kill command to kill the session
3965158 and release the lock.
Since MySQL has deadlock monitoring and automatic transaction rollback capabilities, most deadlock scenarios can heal themselves and are imperceptible to the business. However, problems such as system crash or data inconsistency may occur due to fragile business logic or extreme conditions. Therefore, you should pay attention to deadlocks.
In the "Symptom Description" tab on the event details page, you can analyze the following information and optimize the corresponding statements to solve the deadlock event: