Why ‘INSERT INTO SELECT’ Can Crash Your MySQL Production – A Post‑mortem

Background

In a company with a high‑traffic MySQL payment‑flow table, a data‑migration task was needed to move old records to a history table.

Two proposed solutions

Query data in the application, insert into the history table, then delete the original rows.

Use a single INSERT INTO ... SELECT statement to let MySQL handle the whole operation.

What went wrong

The first approach caused OOM when loading all rows at once; the second seemed to work in tests but caused massive data loss in production after the nightly job ran.

First approach code

// 1. Query data to migrate
List<Object> list = selectData();
// 2. Insert into history table
insertData(list);
// 3. Delete original rows
deleteByIds(ids);

The OOM was obvious because all data were loaded into memory.

Second approach

The team kept only the last ten days of data and executed:

INSERT INTO target_table SELECT * FROM source_table WHERE dateTime < DATE_SUB(NOW(), INTERVAL 10 DAY);

Testing with 10 k rows passed, and the job was scheduled at 20:00. However, after the job ran, many payment records failed to insert, leading to financial loss.

Root cause analysis

Explain plan showed a full‑table scan, which locked the source table under the default transaction isolation level (the a table was locked, b rows locked one by one). This caused intermittent lock time‑outs and insert failures.

Testing missed the issue because the test environment did not reproduce the large‑scale concurrent writes that occurred in production.

Solution

Avoid full‑table scans by adding appropriate indexes on the WHERE clause so the SELECT part uses an index.

Can INSERT INTO SELECT still be used?

Yes, but only with proper indexing and awareness of its locking behavior.

Takeaway

Use INSERT INTO SELECT cautiously; always ensure the query is indexed to prevent full‑table scans and lock contention.