A real‑world MySQL incident shows that using INSERT … SELECT without proper indexing can cause full‑table scans and progressive row locks, leading to massive payment failures, but adding an index on the filter column prevents the lock and restores safe batch migration.
During an Oracle‑to‑OceanBase migration, a client encountered extremely slow INSERT…SELECT batch jobs due to a global unique non‑partitioned index, and the article walks through extracting logical and physical plans, pinpointing the bottleneck operators, and demonstrates converting the index to a partitioned one to cut execution time from over 30 minutes to 14 minutes.
The article explains why an INSERT INTO SELECT statement can lock an entire InnoDB table in MySQL, demonstrates the lock behavior with transaction examples, and offers two solutions—adding an index on the filtered column and adjusting the transaction isolation level—to avoid table‑wide blocking.
