Why Insert‑Into‑Select Can Crash Your MySQL and How to Prevent It

Background

The company processes millions of rows daily in a single MySQL table and needed to migrate old data without sharding.

Proposed solutions

Load data via a program, insert into a history table, then delete the original rows.

Use INSERT INTO SELECT to let the database handle the migration.

The first method caused an OOM error when loading all data at once, so the team chose the second method.

What went wrong

Typical pseudo‑code for the first approach demonstrates loading the entire result set into memory:

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

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

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

During the nightly migration the team used INSERT INTO SELECT with a date filter (retain only the last 10 days). The SELECT part performed a full table scan, locking the target table and causing intermittent insert failures, which resulted in missing payment records.

The execution plan clearly shows a full table scan.

Root cause analysis

In the default transaction isolation level, INSERT INTO a SELECT b locks table a while scanning b row‑by‑row; the full scan caused extensive locking and timeouts.

Testing missed the issue because the test environment used realistic data volume but did not simulate the large‑scale nightly insert, so the problem only appeared in production.

Solution

Avoid full table scans by adding appropriate indexes on the columns used in the WHERE clause of the SELECT statement, ensuring the query uses the index and does not lock the whole table.

Conclusion

Use INSERT INTO SELECT with caution; always verify that the SELECT part is indexed and consider its impact on transaction isolation to prevent OOM and data loss.