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

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;

Order record table

CREATE TABLE order_record LIKE order_today;

Migration Simulation

Move data before March 8th to order_record:

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

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

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

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.