Principles

When an undo tablespace truncation occurs in the MySQL database and the target undo tablespace is large, undo_spaces_lock is contended for.

undo_spaces_lock protects concurrent access to the following data:

• undo::spaces::m_spaces
• undo::space_id_bank
• undo truncation log

When the purge coordinator thread holds the undo_spaces_lock write lock, the DML foreground thread is blocked. The system throughput decreases.

According to the analysis of the code in the critical region, the purge coordinator thread pool holds the lock to protect undo:: space_id_bank and undo truncation log, and does not modify undo:: spaces::m_spaces. The DML foreground thread holds a lock to ensure that it is not modified when undo:: spaces::m_spaces is queried. The logic of the two types of threads does not conflict with each other.

The tuning idea of this feature is as follows: After the purge coordinator obtains the undo_spaces_lock write lock, the undo tablespaces generate a snapshot. Subsequently, the DML foreground thread does not need to hold the undo_spaces_lock read lock and directly queries the undo tablespaces snapshot. The purge coordinator thread synchronizes data with the DML foreground thread by performing atomic operations with a smaller granularity.