我在MySQL 5.5.34（在Ubuntu 12.04上）下使用InnoDB引擎遇到以下行为。

在执行INSERT ... SELECT语句时，某些意外行似乎被锁定在正在读取的表中。

让我举个例子吧。假设两个表table_source，并table_dest具有下列结构（尤其要注意索引）：

CREATE TABLE table_source (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  group_id int(11) NOT NULL,
  data text NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY group_id_created (group_id,created)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE table_dest (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  group_id int(11) NOT NULL,
  data text NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY group_id_created (group_id,created)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

假设我现在执行以下事务：

BEGIN;
INSERT INTO table_dest 
    SELECT * FROM table_source WHERE group_id = 3 AND created < '2014-01-04';
....

然后，源表似乎被锁定为INSERT以group_id2：

INSERT INTO table_source (group_id, data, created) 
    VALUES (2, 'data', NOW()); --< This locks

这是其他一些语句，如果它们锁定或不锁定：

INSERT INTO table_source (group_id, data, created) 
    VALUES (3, 'data', NOW()); --< Does not lock

INSERT INTO table_source (group_id, data, created) 
    VALUES (1, 'data', NOW()); --< Does not lock

INSERT INTO table_source (group_id, data, created) 
    VALUES (3, 'data', '2014-01-01'); --< Does lock

有人可以解释一下为什么会发生这种情况吗（我想这与间隙锁有关）？有没有办法避免这种情况（我仍然想保持REPEATABLE READ隔离级别）？