Why ‘INSERT INTO SELECT’ Can Crash Your MySQL Production – A Cautionary Tale

Blood‑like lesson: use INSERT INTO SELECT with extreme caution; a colleague’s misuse caused a loss of nearly ¥100,000 and resulted in termination.

The Origin of the Issue

The company processes millions of transactions daily on a single MySQL table without sharding, so data migration was needed to maintain performance.

Colleague Li was assigned the task and proposed two solutions:

Query data via program, insert into a history table, then delete from the original table.

Let the database handle everything with INSERT INTO SELECT.

The first approach loaded all data at once, causing an OOM crash; batch processing reduced I/O but took too long, so the second approach was chosen. It passed tests, was deployed, and the colleague was later fired.

What Actually Happened? Let’s Review

First solution – pseudo code

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

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

// 3. Delete original table data
deleteByIds(ids);

The OOM occurred because the entire dataset was loaded into memory.

Second solution

To keep the table performant while preserving recent data, they decided to retain only the last 10 days (about 10k rows). They used a time‑based INSERT INTO SELECT with a dateTime < (Ten days ago) condition, avoiding pagination and OOM, and simplifying the code.

They created 10k rows in a test environment, which passed, and scheduled the migration as a nightly job at 8 PM.

During the night the job ran without issue, but the next morning the finance team found mismatched payment records; many rows had failed to insert, resulting in data loss.

Investigation traced the problem to the migration task. Stopping the task eliminated the failures.

Postmortem

Where Was the Problem?

Examining the SQL EXPLAIN revealed a full‑table scan.

A full‑table scan on such a large table means the migration can take an hour, explaining why the issue only appeared during the nightly run.

Is the full scan the root cause? By adding a selective WHERE clause, the scan became an index lookup and the problem disappeared.

Conclusion: the full‑table scan caused the incident.

Root Cause

Under the default transaction isolation level, INSERT INTO a SELECT b locks table a entirely while locking rows of b one by one. This leads to intermittent lock‑wait timeouts and failures during the migration.

Why Did Tests Pass?

The test environment used real‑world data but did not replicate the massive concurrent inserts of the production migration, so the issue was not observed.

Solution

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

Can INSERT INTO Still Be Used?

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

Conclusion

When using INSERT INTO SELECT, ensure suitable indexes are in place to prevent full‑table scans and related performance or locking problems.