Lessons Learned from Misusing INSERT INTO SELECT in MySQL: OOM, Full Table Scan, and Transaction Issues

The author describes a costly mistake at a company where a colleague used INSERT INTO SELECT to migrate millions of rows from a high‑traffic MySQL table without proper safeguards, leading to a loss of about 100,000 CNY and eventual termination.

Background: The table handled daily incremental data of around one million rows with no sharding. To keep performance while archiving old data, two migration strategies were considered: (1) load data into memory, insert into a history table, then delete the original rows; (2) let the database perform the whole operation via INSERT INTO SELECT .

The first approach caused an out‑of‑memory (OOM) crash when all data was loaded at once; batch processing reduced OOM but increased I/O and runtime, so the team chose the second approach, which passed tests and was deployed.

After deployment, the nightly migration ran at 8 PM. The next day, accounting discovered missing payment records. Investigation revealed that inserts started failing intermittently after the migration began, eventually causing data loss.

Analysis of the failing INSERT INTO SELECT showed it performed a full table scan (as seen in the provided EXPLAIN screenshots), locking the target table for the duration of the operation. Under the default transaction isolation level, the insert locked the destination table while rows from the source were locked one‑by‑one, leading to lock contention and timeouts.

Testing had used production‑like data volumes but did not simulate the massive concurrent inserts that occur during the nightly job, so the issue was missed.

Solution: Avoid full table scans by adding appropriate indexes on the columns used in the WHERE clause of the INSERT INTO SELECT statement, ensuring the query uses index lookups instead of scanning the entire table. This eliminates the heavy lock on the destination table and prevents the intermittent failures.

In summary, when using INSERT INTO SELECT for large‑scale data migration, always verify that the SELECT part is indexed, understand the locking behavior under the current isolation level, and test with realistic concurrent workloads.

Code example of the first (failed) approach:

// 1、查询对应需要迁移的数据
List
list = selectData();
// 2、将数据插入历史表
insertData(list);
// 3、删除原表数据
deleteByIds(ids);