Overview

Caution is advised when using INSERT INTO SELECT statements.

During a routine maintenance window, a developer needed to archive historical records from a large production table. Rather than fetching data through an application and performing batch inserts, the developer discovered that INSERT INTO SELECT could transfer data directly within the database, eliminating network overhead and leveraging database I/O instead.

That decision ultimately led to immediate termination.

Incident Timeline

The production database contained approximately 7 million records in the orders_current table, with an additional 300,000 records being added daily. Management directed the developer to archive older records to orders_archive and remove them from the source table to reduce storage pressure and improve query performance.

The migration was scheduled for after business hours (9:00 PM) to minimize user impact. How ever, the developer began testing with 1,000 records at 8:00 PM, observed no immediate issues, and proceeded with the full migration.

Within minutes, the emergency response channel reported isolated payment failures, followed by cascading failures affecting numerous users—unable to complete transactions or create new orders. Monitoring systems triggered critical alerts.

The developer immediately halted the migration, but recovery took considerably longer than expected.

Reproducing the Issue

A simplified local environment with 1 million records was created to demonstrate the problem.

Table Definitions

Active Orders Table

CREATE TABLE `orders_current` (
    `order_id` varchar(32) NOT NULL COMMENT 'Primary key',
    `merchant_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Merchant identifier',
    `total_amount` decimal(15,2) NOT NULL COMMENT 'Order value',
    `transaction_time` datetime NOT NULL COMMENT 'Transaction timestamp',
    `status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Status: S=Success, F=Failed',
    `notes` varchar(100) CHARACTER SET utf8 COLLATE utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Additional notes',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
    `modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last update time',
    PRIMARY KEY (`order_id`) USING BTREE,
    KEY `idx_merchant_code` (`merchant_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Archived Orders Table

CREATE TABLE orders_archive LIKE orders_current;

Simulation Test

Records older than March 8th were selected for archival:

INSERT INTO orders_archive
SELECT *
FROM orders_current
WHERE transaction_time < '2020-03-08 00:00:00';

During execution, a separate connection attempted concurrent inserts. The results showed initial inserts succeeding normally, followed by severe blocking lasting 23 seconds before the archive operation completed and normal operations resumed.

Root Cause Analysis

Under MySQL's default transaction isolation level (REPEATABLE READ), the INSERT INTO orders_archive SELECT * FROM orders_current statement acquires locks as follows: the destination table gets a full table lock, while the source table receives row-level locks incrementally as records are scanned.

Examining the execution plan revealed that orders_current underwent a full table scan. Each record scanned during the INSERT INTO SELECT operation was locked immediately, effectively serializing access to the entire source table.

This explains the progressive failure pattern: initial requests succeeded because few records were locked, but as the scan progressed, increasingly more records became unavailable for modification, causing widespread transaction failures. Eventually, all records were locked, preventing any new orders from being created.

Resolution

Since the WHERE condition on transaction_time triggered a full table scan, the solution involved adding an appropriate index on that column:

ALTER TABLE orders_current ADD INDEX idx_transaction_time (transaction_time);

With index optimizaton, the query accesses only matching records instead of scanning the entire table. Row-level locks are acquired only for relevant rows, leaving other records available for concurrent operations.

Optimized Archive Query

INSERT INTO orders_archive
SELECT *
FROM orders_current FORCE INDEX (idx_transaction_time)
WHERE transaction_time <= '2020-03-08 00:00:00';

Key Takeaway

When executing INSERT INTO tableA SELECT * FROM tableB statements, always verify that any WHERE conditions, ORDER BY clauses, or other filtering criteria have corresponding indexes on tableB. This prevents entire-table locking scenarios that can bring production systems to a standstill.