mysql - INSERT ... SELECT, InnoDB and locking -

i came accross following behaviour innodb engine under mysql 5.5.34 (on ubuntu 12.04).

when performing insert ... select statements, unexpected rows seem locked in table being read from.

let me give example. suppose 2 tables table_source , table_dest following structure (particular attention indices):

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; 

suppose execute following transaction:

begin; insert table_dest      select * table_source group_id = 3 , created < '2014-01-04'; .... 

then source table seems locked insert group_id 2:

insert table_source (group_id, data, created)      values (2, 'data', now()); --< locks 

here other statements , if lock or not:

insert table_source (group_id, data, created)      values (3, 'data', now()); --< not lock  insert table_source (group_id, data, created)      values (1, 'data', now()); --< not lock  insert table_source (group_id, data, created)      values (3, 'data', '2014-01-01'); --< lock 

can explain me why happens (i suppose has gap locks)? there way avoid (i still want keep repeatable read isolation level)?

that's correct. rows in table being read locked shared lock (the select implicitly lock in share mode). there isn't way avoid this. it's sort of you're asking system for: copy rows match condition. way ensure in fact rows match condition , that list not change during or after execution of statement, lock rows.

as clarification regarding why unable insert group_id = 2:

this has query being where group_id = 3 , created < '2014-01-04' on key group_id_created (group_id, created). in order search rows match group_id = 3 , created < '2014-01-04' index traversed backwards starting first row exceeds condition upper bound, (3, '2014-01-14') , continuing until finding row not match condition, since created has no lower bound first row group_id < 3 of course group_id = 2.

that means first row encountered group_id = 2 also locked, row maximum created value. make impossible insert "gap" between (2, max(created)) , (3, min(created)) (not proper sql of course, pseudo-sql), although not "gap lock" specifically.