Case Study: MySQL INSERT INTO SELECT Caused Data Loss Due to Full Table Scan

Root Cause

A colleague attempted to migrate large MySQL tables (≈1 million rows per day) without sharding, using two possible approaches: (1) fetch data via program, insert into a history table, then delete the source; (2) let the database perform the whole operation with INSERT INTO SELECT .

What Happened

The first approach loaded all data into memory, causing an OOM error. The second approach seemed to work in testing, so it was deployed as a nightly scheduled job at 20:00.

After deployment, the finance team noticed mismatched payment records; many rows failed to be inserted during the night, resulting in data loss.

Investigation revealed that the nightly migration task was the culprit. When the task was stopped, the issue disappeared.

Review

Problem Analysis

Examining the first approach's pseudo‑code:

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

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

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

The OOM occurred because all rows were loaded into memory at once.

For the second approach, the developer filtered rows older than ten days and executed:

INSERT INTO target SELECT * FROM source WHERE dateTime < (NOW() - INTERVAL 10 DAY);

This avoided paging and seemed to work in the test environment, which used only a subset of real data.

However, the production table held about 10 k rows for ten days, and the nightly job performed a full table scan, as shown in the EXPLAIN image.

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. The full‑table scan caused prolonged locks, leading to intermittent insert failures and time‑outs for payment records.

Why Testing Missed the Issue

The test environment used realistic data volume but did not replicate the concurrent heavy insert load that occurs in production, so the problem was not observed.

Solution

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

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 for large data migrations, always verify that the query uses indexes to prevent full‑table scans, and test with production‑scale data and concurrency to avoid hidden lock‑contention bugs.