Lessons Learned: Risks of Using INSERT INTO SELECT for MySQL Data Migration

The post warns about the dangers of using INSERT INTO SELECT for large‑scale data migration in MySQL, recounting a case where a colleague’s implementation caused a loss of nearly 100,000 CNY and resulted in termination.

Background : The company processes about a million new rows per day in a single MySQL table without sharding, so they needed a way to migrate old data while preserving performance.

Two solutions were proposed:

Programmatically query the rows, insert them into an archive table, then delete the originals.

Let the database handle everything with INSERT INTO SELECT .

The first approach caused an out‑of‑memory (OOM) crash when all rows were loaded at once; batching reduced OOM but increased I/O time, so the team chose the second approach, which passed testing and was deployed.

After deployment, a nightly job at 20:00 began to fail inserting payment records, resulting in significant data loss. Investigation traced the issue to the migration task itself.

Examining the INSERT INTO SELECT statement with EXPLAIN revealed a full table scan. Under MySQL’s default transaction isolation level, the target table a is locked for the whole operation while the source table b is locked row‑by‑row, causing lock contention and time‑outs for many rows.

Testing did not reproduce the problem because the test environment used a realistic data set but lacked the concurrent high‑volume insert load present in production.

When the WHERE clause was refined to use an indexed column, the full table scan disappeared and the issue was resolved.

Pseudo‑code of the first (failed) approach:

// 1. Query data to be migrated
List<Object> list = selectData();

// 2. Insert data into history table
insertData(list);

// 3. Delete original rows
deleteByIds(ids);

Conclusion : The root cause was the full table scan and the resulting lock behavior of INSERT INTO SELECT . To safely use this command, ensure that the WHERE clause is indexed so the SELECT uses an index, avoid full scans, and be aware of locking implications.

Final advice: Use INSERT INTO SELECT cautiously and always verify that appropriate indexes exist.