Why INSERT … SELECT Can Lock Your Table and How an Index Saves the Day

Background

A developer needed to back up data by moving rows from order_today to order_record without incurring network I/O. The plan was to use a single INSERT INTO … SELECT statement in MySQL.

Incident Details

The order_today table contained roughly 7 million rows and grew by about 300 k rows per day. The goal was to migrate a subset of historical rows (those with a payment time before a certain date) to order_record and then delete them from order_today to keep the active table small.

After a small test (1 000 rows) succeeded at 8:00 am, a large‑scale migration was started after business hours. Within minutes users experienced payment failures, order‑initialization errors, and alerts from the monitoring system.

Reproduction

A local MySQL instance was populated with 1 million rows to reproduce the issue. Table definitions:

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 time',
  `order_status` varchar(10) NOT NULL COMMENT 'S: success, F: failure',
  `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`),
  KEY `idx_merchant_id` (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE order_record LIKE order_today;

Migration SQL used:

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

When the statement was executed in a client (e.g., Navicat) while another session kept inserting new orders, the first few thousand rows were copied quickly, then the operation paused for about 23 seconds before continuing. During the pause, incoming orders failed.

Root‑Cause Analysis

MySQL’s default transaction isolation level (REPEATABLE‑READ) causes the following lock behavior for the statement above:

Target table ( order_record ) : a full table lock is acquired because the statement writes to it.

Source table ( order_today ) : rows are locked one by one as they are read. Since the WHERE clause does not use an index, MySQL must perform a full‑table scan, locking each row sequentially. This progressive locking quickly escalates to a situation that is effectively a full table lock.

As more rows become locked, concurrent INSERTs into order_today are blocked, leading to payment and order‑initialization failures. When the lock covers the entire table, new orders cannot be inserted at all.

Solution

Provide an index on the filter column so MySQL can use an index range scan instead of a full table scan. The index limits locking to only the qualifying rows.

CREATE INDEX idx_pay_suc_time ON order_today (pay_success_time);

After the index is in place, the migration can be executed with a forced index hint to guarantee its use:

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

With the index, the statement scans only the relevant rows, acquires locks on a small subset, and allows concurrent inserts into order_today. The migration completes quickly without causing service disruption.

Conclusion

When using INSERT INTO … SELECT, always ensure that the WHERE clause on the source table is supported by appropriate indexes. Without such indexes, MySQL may lock the entire source table during the operation, leading to severe performance degradation and service outages.