Risks of Using INSERT INTO SELECT in MySQL: A Postmortem

Background

A large‑scale MySQL table (≈10k rows per day) required data migration without sharding. Two approaches were considered: load data via application code and move it, or let MySQL handle the whole operation with INSERT INTO SELECT .

First Approach

Pseudo‑code:

// 1. Query data to migrate
List
list = selectData();

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

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

Loading all rows at once caused OOM; batch processing reduced memory pressure but increased I/O and runtime, so the team chose the second approach.

Second Approach and Failure

The job ran nightly at 20:00, selecting rows older than ten days with INSERT INTO SELECT ... WHERE dateTime < … . In production the task appeared to work, but the next morning financial reconciliation showed missing payment records. Investigation revealed that the migration caused intermittent insert failures after the job started.

Root Cause

Under the default transaction isolation level, INSERT INTO a SELECT b locks table a fully while locking rows of b one by by. The full‑table scan on the large payment table held locks long enough that concurrent transactions timed out, leading to lost rows.

Why Tests Passed

Test data volume and environment did not reproduce the heavy‑load scenario; the real production load caused the lock contention.

Solution

Avoid full‑table scans by adding appropriate indexes on the WHERE clause so the SELECT part uses an index. This eliminates long‑running locks and prevents the intermittent failures.

Conclusion

Use INSERT INTO SELECT with caution. Ensure the query is indexed and consider its locking behavior, especially on large tables.