MySQL Lock-Free Tuning

Application scenario: In MySQL OLTP scenarios, resource contention and synchronization bottlenecks in the critical region are caused by:

• A large number of DML statements (Insert, Update, and Delete) are concurrently executed on key data structures in the trx_sys global structure.
• Cross-NUMA access.

Technical principle: The MySQL transaction manager uses data structures, such as linked lists and arrays, to maintain global transaction records. In read/write scenarios, the synchronous lock mechanism is used to implement transaction isolation levels and multi-version control. After reconstruction, the lock-free hash table is used to maintain transaction units to reduce lock conflicts and improve concurrency.

Feature effects

• In the OLTP sysbench write scenario, the performance is improved by 20% on average.

• The performance is stable in high concurrency scenarios.