Why INSERT INTO SELECT Cost My Company $10k – A MySQL Migration Nightmare

A blood‑curdling lesson: misuse of INSERT INTO SELECT caused a loss of nearly ¥100k and a termination.

Origin of the Issue

The company processes millions of rows daily in a single MySQL table without sharding, so a data‑migration task was needed to maintain performance.

Colleague Li proposed two solutions:

Programmatically select data, insert into a history table, then delete the original rows.

Let the database handle everything with INSERT INTO SELECT.

The first approach OOMed when loading all data at once; the second seemed to work in tests, so it was deployed.

What Happened – Review

First solution (pseudocode)

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

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

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

The OOM occurred because all rows were loaded into memory.

Second solution

The team kept only the last 10 days of data (about 10 k rows) and used a time filter in the INSERT INTO SELECT statement, avoiding pagination and OOM, and simplifying the code.

They created 10 k rows in a test environment, scheduled the task at 8 pm, and saw no issues during the test.

However, the next morning the finance team found many payment records missing; inserts started failing after the scheduled run.

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

Post‑mortem

Where was the problem?

Why did stopping the migration fix it? The INSERT INTO SELECT statement was examined with EXPLAIN.

The query performed a full‑table scan, which explains the long migration time (about an hour) and why the issue only appeared during the night run.

Root Cause

Under the default transaction isolation level, INSERT INTO SELECT locks the target table entirely while locking the source rows one by one. This caused intermittent lock‑wait timeouts and failures as the payment table rows were being scanned and locked.

Why did tests pass?

Testing used production‑like data but not the exact volume and concurrency of the live environment; the massive simultaneous inserts during the night were not reproduced.

Solution

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

Can INSERT INTO SELECT Still Be Used?

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

Conclusion

When using INSERT INTO SELECT, ensure indexes are in place to prevent full‑table scans and understand the locking implications to avoid costly data loss.