Lessons Learned from Misusing INSERT INTO SELECT in MySQL: A Postmortem

Blood‑like lesson: be very careful when using INSERT INTO SELECT . A colleague’s misuse caused a loss of nearly ¥100,000 and resulted in termination.

1. Origin of the Incident

The company processes millions of rows daily on a single MySQL table without sharding, so data migration was needed to keep the table performant.

Colleague Li proposed two approaches:

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

Let MySQL handle everything with INSERT INTO SELECT .

The first method caused an OOM when loading all rows at once; batch processing reduced OOM but introduced excessive I/O and latency, so the second method was chosen. Tests passed, it went live, and the colleague was later fired.

2. What Actually Happened? A Review

First Approach – Pseudocode

// 1. Query data to be migrated
$datalist = selectData();

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

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

The OOM was obvious: the code loads the entire dataset into memory.

Second Approach – Using INSERT INTO SELECT

To keep ten days of data (about 10 k rows) the colleague ran a time‑filtered statement such as:

INSERT INTO SELECT ... WHERE dateTime < (NOW() - INTERVAL 10 DAY)

This avoided pagination, eliminated OOM, and reduced code complexity.

A test with 10 k rows succeeded, and the job was scheduled as a nightly task at 20:00.

During the night the load seemed fine, but the next morning the finance team found many payment records missing. Investigation showed that after 20:00, payment inserts started failing, causing data loss.

Stopping the migration task eliminated the failures, confirming the task as the root cause.

3. Postmortem – Where Was the Problem?

Full‑Table Scan

The EXPLAIN of the statement revealed a full‑table scan. With a massive table, the migration took a long time, which explained why the issue only appeared during the nightly run.

After adding a selective condition that allowed the query to use an index, the full scan disappeared and the problem was resolved.

Conclusion: the full‑table scan caused the incident.

Lock Contention Under Default Isolation

With the default transaction isolation level, INSERT INTO a SELECT b locks table a as a whole, while rows from b are locked one by one. This explains the intermittent failures: some rows were locked and timed out, while others succeeded.

Why Tests Didn’t Reveal the Issue

The test environment used real‑world data size but did not simulate the concurrent large‑scale inserts that occur during the nightly migration, so the lock‑contention problem was missed.

4. Solution

Avoid full‑table scans by adding appropriate indexes to the columns used in the WHERE clause, ensuring the SELECT part can be satisfied by an index.

5. Can INSERT INTO SELECT Still Be Used?

Yes, it can be used safely if proper indexing and locking considerations are applied.

6. Summary

When using INSERT INTO SELECT , proceed with caution and always ensure that the query is supported by suitable indexes to prevent full‑table scans and lock contention.