Why Did INSERT‑INTO‑SELECT Lock My MySQL Table? Lessons & Fixes

Preface

INSERT INTO SELECT is tempting for bulk data migration because it avoids network I/O and relies on database I/O, but misuse can lead to disastrous locking problems.

Incident Overview

The order_today table had grown to about 7 million rows, increasing by 300 k daily. Management ordered a partial migration of data from order_today to order_record and then deletion of the moved rows to reduce table size.

The migration was scheduled after 9:00 AM to avoid business impact, but a test run at 8:00 AM moved 1 000 rows and gave a false sense of safety, leading to a full‑scale migration.

During migration, a small number of users experienced payment failures, followed by a large wave of failures and order initialization errors, triggering alerts.

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

Reproducing the Accident

A local test database was created with 1 million rows to simulate the production environment.

Table Structures

CREATE TABLE `order_today`(
  `id` varchar(32) NOT NULL COMMENT 'primary key',
  `merchant_id` varchar(32) NOT NULL COMMENT 'merchant ID',
  `amount` decimal(15,2) NOT NULL COMMENT 'order amount',
  `pay_success_time` datetime NOT NULL COMMENT 'payment success time',
  `order_status` varchar(10) NOT NULL COMMENT 'payment status (S/F)',
  `remark` varchar(100) DEFAULT NULL COMMENT 'remarks',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
  PRIMARY KEY(`id`) USING BTREE,
  KEY `idx_merchant_id`(`merchant_id`) USING BTREE COMMENT 'merchant ID'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

order_record

CREATE TABLE order_record LIKE order_today;

Simulated Migration

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

Running this SQL in Navicat while concurrently inserting new orders showed that the first few inserts succeeded quickly, but later the process stalled for about 23 seconds before proceeding, indicating a locking bottleneck.

Root Cause Analysis

Under the default transaction isolation level, INSERT INTO order_record SELECT … locks the target table ( order_record) and then scans order_today row by row, acquiring locks on each scanned row. This effectively becomes a full‑table lock on the source table.

Because the scan is a full table scan, only a small portion of rows are locked initially, allowing some payments to succeed. As more rows become locked, payment failures increase until the entire table is locked, causing complete service disruption.

Solution

Adding an index on the pay_success_time column allows MySQL to use an index range scan instead of a full table scan, limiting locks to 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';

The execution plan after adding the index shows a fast, index‑driven scan with minimal locking.

Conclusion

When using INSERT INTO … SELECT … for large‑scale data migration, always ensure that the source table’s WHERE clause (or ORDER BY) is supported by appropriate indexes; otherwise the operation may lock the entire source table and cause severe service outages.