Analysis of MySQL Deadlock Issues in Multi-threaded Scenarios

This article analyzes a MySQL deadlock issue encountered during multi-threaded operations. The problem occurred when using the INSERT INTO... ON DUPLICATE KEY UPDATE statement in a scenario where multiple logistics cancellations were processed concurrently.

The author first describes the problem background, where a business scenario required updating data if it existed or inserting it if it didn't. During testing with multiple threads, a deadlock occurred when canceling two logistics orders simultaneously.

The article then details how to reproduce the issue using SHOW ENGINE INNODB STATUS to obtain deadlock logs from MySQL 5.7. The deadlock logs revealed that two transactions were waiting for locks on the same records in the recycle_order_extend table.

Through testing, the author discovered that the issue couldn't be reproduced in a local MySQL 8.0 environment because one transaction would block the other rather than causing a deadlock. However, when creating a local test case with two threads, a deadlock did occur, and it was even more puzzling - only one SQL statement from each thread was needed to produce the deadlock.

The author then downloaded MySQL 8.0 source code and used debugging tools to trace the execution flow. The analysis revealed that MySQL processes each value in the INSERT statement individually, and locking is applied in batches. The key findings include:

1. MySQL executes each value in the INSERT statement separately

2. Locking is applied in batches during execution, with specific locks added at different stages

3. There's no locking during SQL processing to ensure only one SQL is processed at a time

4. Internal mini-transactions use RW-X-LATCH locks to prevent data contention

The article concludes by demonstrating how to reproduce both deadlock scenarios through controlled testing, providing valuable insights into MySQL's internal locking mechanisms and how they can lead to deadlocks in multi-threaded environments.