Why ‘INSERT INTO … SELECT’ Can Lock Your MySQL Tables and How to Prevent It

Preface

Using INSERT INTO … SELECT can be very efficient because it avoids network I/O and relies on database I/O, but it must be used with caution.

Incident Details

The order_today table had grown to about 7 million rows and was increasing by 300,000 rows daily. Management instructed moving a portion of this data to order_record and then deleting it from order_today to reduce table size.

The migration was planned for after 9:00 AM to avoid business impact, but the engineer started at 8:00 AM with a test batch of 1,000 rows and then proceeded with a large‑scale migration.

During the migration users began experiencing payment failures, followed by a surge of failures and order‑initialization errors, prompting alerts from Tencent.

The engineer stopped the migration, but the system did not recover immediately.

Reproduction

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

Table Structure

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;

Simulated Migration

SQL used to move data before March 8 2020:

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

Running the migration in Navicat while simultaneously inserting new orders showed that the first few rows were inserted quickly, then the process stalled for about 23 seconds before continuing, indicating a lock contention issue.

Root Cause

Under MySQL's default transaction isolation level, the statement

INSERT INTO order_record SELECT * FROM order_today

locks the order_record table entirely and performs a row‑by‑row lock on order_today during a full‑table scan. Because the WHERE clause on pay_success_time forces a full scan, the engine locks each row sequentially, effectively locking the whole source table.

This explains why a small number of users initially experienced payment failures (only the rows already locked were affected), followed by a massive failure as more rows became locked, eventually blocking all inserts.

Solution

Adding an index on the pay_success_time column eliminates the full‑table scan. With the index, MySQL can locate only the qualifying rows, locking just those records.

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';

Execution screenshots show the statement completing quickly without extensive locking.

Conclusion

When using INSERT INTO … SELECT, ensure that the source table’s WHERE conditions are supported by appropriate indexes; otherwise the operation may lock the entire source table and cause severe service disruption.

Reference

Analysis of deadlocks caused by INSERT INTO … SELECT statements