Why Insert‑Into‑Select Can Lock Your MySQL Tables and How to Fix It

Preface

Insert into select should be used with caution. A developer needed to migrate data from table A to table B for backup. He tried a programmatic batch insert but found it too slow due to network I/O, so he turned to

INSERT INTO … SELECT

to let the database handle the I/O.

Incident

The

order_today

table had about 7 million rows and grew by 300k rows daily. Management asked to move part of the data to

order_record

and delete it from

order_today

to reduce table size. The migration was scheduled after 9:00, but the developer started at 8:00 with a small test of 1,000 rows and then proceeded with a large batch.

During migration, a few users experienced payment failures, followed by many users facing payment failures and order initialization errors, triggering alerts.

The developer stopped the migration immediately.

Reproduction

A local lightweight database with 1 million rows was created to simulate the production scenario.

Table Structure

Order table

<code>CREATE TABLE `order_today` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户编号',
  `amount` decimal(15,2) NOT NULL COMMENT '订单金额',
  `pay_success_time` datetime NOT NULL COMMENT '支付成功时间',
  `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付状态  S：支付成功、F：订单支付失败',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间  --  修改时自动更新',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商户编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</code>

Order record table

<code>CREATE TABLE order_record LIKE order_today;</code>

Migration Simulation

Move data before March 8th to

order_record

:

<code>INSERT INTO order_record SELECT *
FROM order_today
WHERE pay_success_time < '2020-03-08 00:00:00';</code>

During the test, initial inserts succeeded quickly, but later the process stalled for about 23 seconds before completing, after which the migration finished and inserts resumed.

Root Cause

Under the default transaction isolation level,

INSERT INTO order_record SELECT * FROM order_today

locks

order_record

and progressively locks rows of

order_today

as it scans the table. This full‑table scan results in row‑by‑row locking, effectively locking the entire source table.

Consequently, only a small portion of users could complete payments at first; as more rows became locked, payment failures increased, eventually blocking all inserts and causing order initialization errors.

Solution

Adding an index on the filter column

pay_success_time

(e.g.,

idx_pay_suc_time

) forces MySQL to use the index instead of a full table scan, limiting locks to the qualifying rows.

Final SQL

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

Summary

When using

INSERT INTO … SELECT

, always ensure the source table’s

WHERE

clause (or

ORDER BY

, etc.) is supported by appropriate indexes to avoid locking the entire source table.