Case Study of Insert‑Into‑Select Migration Causing Table Locks and Its Resolution

The author warns that INSERT INTO ... SELECT should be used with caution, describing a scenario where a developer attempted to migrate millions of rows from order_today to order_record to reduce table size, but the operation caused severe locking and payment failures.

During the migration, the order_today table held about 7 million rows growing by 300k daily. The plan was to run the migration after business hours, but a premature test of 1,000 rows led to a full‑scale execution. Users began experiencing payment failures, and the system raised alerts.

Investigation revealed that under MySQL's default transaction isolation level, the statement INSERT INTO order_record SELECT * FROM order_today WHERE pay_success_time < '2020-03-08 00:00:00' acquires a table lock on order_record and a row‑by‑row lock on order_today . Because the query lacks an index on pay_success_time , MySQL performs a full table scan on order_today , effectively locking the entire table and preventing new inserts.

The root cause is the missing index on the filter column, which forces a full scan and incremental locking. As more rows become locked, payment failures increase until the table is completely locked, causing order initialization failures.

To resolve the issue, the author suggests adding an index idx_pay_suc_time on pay_success_time and modifying the migration SQL to force the use of this index:

INSERT INTO order_record SELECT
    *
FROM
    order_today FORCE INDEX (idx_pay_suc_time)
WHERE
    pay_success_time <= '2020-03-08 00:00:00';

With the index, MySQL can locate the qualifying rows directly, locking only the necessary rows instead of the whole table, thus avoiding the cascade of payment failures.

The article concludes with a reminder that any INSERT INTO ... SELECT operation must ensure the source table has appropriate indexes for the WHERE clause to prevent full‑table scans and extensive locking.