Understanding Table Locks in MySQL When Using INSERT INTO SELECT and the Impact of Isolation Levels

In Oracle, only SELECT ... FOR UPDATE acquires locks, while other queries are lock‑free; MySQL, however, can lock tables when executing INSERT INTO SELECT under certain isolation levels.

Running insert into test_1 select * from test_2; on MySQL 5.7 shows that the operation can acquire record locks on the source table, even when only a single row is read, because InnoDB places an intention lock (IS) on the whole table and shared locks on the matching rows.

Table definitions used in the test:

mysql> show create table test_1\G;
*************************** 1. row ***************************
       Table: test_1
Create Table: CREATE TABLE `test_1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> show create table test_2\G;
*************************** 1. row ***************************
       Table: test_2
Create Table: CREATE TABLE `test_2` (
  `id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Enabling detailed lock monitoring with set global innodb_status_output_locks=on; reveals that a transaction inserting a row from test_1 into test_2 creates five record locks on test_1 , plus an intention lock (IS) on the table.

Solution 1 – Create an Index on the Filtered Column

Adding an index on name reduces the lock scope to the indexed row only:

mysql> alter table test_1 add index idx_test_1_01 (name);

Repeating the transaction shows only a shared lock on the indexed record, without any table‑level locks.

Solution 2 – Change the Transaction Isolation Level

MySQL’s default isolation level is REPEATABLE READ (RR) , which provides snapshot reads based on the transaction start time. Switching to READ COMMITTED (RC) changes the visibility of data during a transaction.

Experiments demonstrate that under RR, a concurrent update does not affect the reading transaction, while under RC the reading transaction sees the committed change, eliminating the need for the extensive locking observed with the INSERT INTO SELECT operation.

mysql> set session transaction_isolation='read-committed';
mysql> begin;
mysql> insert into test_2 select * from test_1 where name = 'test_1';

After setting RC, show engine innodb status shows no record locks on test_2 , confirming that the lock‑heavy behavior is tied to the isolation level.

In summary, the table‑wide lock caused by INSERT INTO SELECT in MySQL can be mitigated by indexing the filtered column or by using a less strict isolation level such as READ COMMITTED, each representing a trade‑off between concurrency and consistency.