Case Study: Insert‑Into‑Select Migration Failure and Resolution in MySQL

The author describes a scenario where a large order_today table (over 7 million rows) needed to be partially migrated to order_record for backup purposes, and the initial approach used INSERT INTO SELECT to avoid network I/O.

During the migration, a small test batch of 1,000 rows was run at 8:00 am, after which a full‑scale migration was started at 9:00 am. Shortly after, users began experiencing payment failures and order initialization errors, and the system alarmed.

To reproduce the incident, a local database with 1 million rows was created, and the following table definitions were used:

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;

CREATE TABLE order_record LIKE order_today;

The migration SQL executed in Navicat was:

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

Initial inserts succeeded quickly, but later the process stalled for 23 seconds before completing, during which many users experienced payment failures because rows were being locked progressively.

Analysis revealed that under MySQL’s default transaction isolation level, the statement locks the target table ( order_record ) and then scans and locks rows in the source table ( order_today ) one by one, effectively behaving like a full‑table lock.

The root cause was the lack of an index on the pay_success_time column, causing a full table scan and extensive row‑level locking.

The proposed solution is to add an index on pay_success_time and force its use in the query, limiting the lock scope to only the qualifying rows:

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

After applying the index, the migration runs efficiently without locking the entire source table, preventing the cascade of payment failures.

In summary, when using INSERT INTO … SELECT , always ensure that the WHERE clause (or ORDER BY) columns are indexed to avoid full‑table scans and the associated locking problems.