MySQL Row Lock Optimization
Exclusive locks (X locks) and shared locks (S locks) are InnoDB's row-level conceptual locks that are used to ensure the seriality of modifications and deletions to the same row record, thereby guaranteeing strong data consistency. They are applied in three scenarios: record locks, gap locks, and next-key locks. In high-concurrency situations, proper configuration is required to avoid excessive deadlocks.
Checking Row Lock Waits
The following SQL statement can be used to check for row lock waits.
| Return Parameter | Description |
|---|---|
| Innodb_row_lock_current_waits | Number of current row lock waits |
| Innodb_row_lock_time | Total time taken to acquire row locks, in milliseconds |
| Innodb_row_lock_time_avg | Average time taken to acquire row locks, in milliseconds |
| Innodb_row_lock_time_max | Maximum time taken to acquire row locks, in milliseconds |
| Innodb_row_lock_waits | Number of times row lock waits occurred |
Recommendations for Optimizing Row Lock Wait Issues
High Database Concurrency
When MySQL encounters too many concurrent requests, waiting situations due to contention for row locks occur. To avoid lock conflicts, it is advisable to optimize hot updates in the business and employ techniques such as paginated queries or segmented queries when processing large volumes of data. These methods can prevent locking too many data rows in a single query, reducing the risk of locking conflicts and improving system performance and response speed. Additionally, the innodb_lock_wait_timeout and interactive_timeout parameters can be adjusted to set the maximum duration for transactions waiting to acquire resources. This helps to optimize the locking mechanism and improve system availability.
In cases where deadlocks are infrequent, innodb_print_all_deadlocks=ON can be set to print detailed information about every deadlock occurrence in the error log to assist in analyzing the causes of deadlocks. However, if deadlocks are common in the business, to maintain the Error log and server performance, it is best to set innodb_print_all_deadlocks to OFF, only outputting summary information about deadlocks. If detailed deadlock analysis is needed, this parameter can be temporarily set to ON to observe detailed information about deadlocks for a period of time, and then turned off again to minimize the impact on server performance.
Missing Indexes
To avoid InnoDB escalating to table locks due to the inability to acquire row locks via indexing, it is recommended to use indexes as much as possible for data retrieval. Additionally, when adding indexes, care should be taken to be as precise as possible to avoid unnecessary locking, which can affect the performance of other queries.
Excessive Locking Range
To avoid locking records that should not be locked due to range retrieval (i.e., gap locks), it is advisable to minimize the use of range queries. Furthermore, to reduce the amount of locked resources and locking time, the size of transactions should be controlled. Regarding transaction isolation levels, it is recommended to use lower isolation levels whenever feasible to reduce the costs associated with MySQL's transaction isolation handling.