How INSERT INTO SELECT Nearly Crashed a MySQL DB – Lessons & Fix

Preface

INSERT INTO SELECT should be used with caution. An engineer received a request to migrate data from table order_today to order_record for backup. He tried a programmatic batch insert, found it slow due to network I/O, and switched to INSERT INTO SELECT to rely on database I/O.

Incident Overview

The order_today table contained about 7 million rows and grew by 300 k rows daily. Management ordered a partial migration of data to order_record and deletion from order_today to reduce table size. The migration was scheduled after 9:00 AM, but the engineer started early, moving 1 000 rows without issue and then proceeded with a large‑scale migration.

Incident Reconstruction

A simplified local database with 1 million rows was created to reproduce the problem.

Table Structures

Order table

CREATE TABLE `order_today` (
  `id` varchar(32) NOT NULL COMMENT 'primary key',
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci 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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'S: success, F: failure',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'remark',
  `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 index'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Order record table

CREATE TABLE order_record LIKE order_today;

Simulated Migration

Move rows with a payment time earlier than March 8 2020.

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

During the test, the first inserts succeeded quickly, then the process paused for about 23 seconds before completing, after which inserts resumed normally.

Root Cause

Under the default transaction isolation level, the statement locks the target table order_record and progressively locks rows of order_today as it performs a full‑table scan. This behaves like a full table lock, causing increasing payment failures as more rows become locked.

Solution

Adding an index on the pay_success_time column (e.g., idx_pay_suc_time) allows the WHERE clause to use the index, avoiding a full scan and 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';

Conclusion

When using INSERT INTO SELECT, ensure that any filtering columns in the source table have appropriate indexes; otherwise the entire source table may be locked, leading to severe performance and availability issues.

References

https://blog.csdn.net/asdfsadfasdfsa/article/details/83030011