MySQL Bugs: #61026: INNODB: Insert as select from a void table produce an unexpected index deadlock

Bug #61026	INNODB: Insert as select from a void table produce an unexpected index deadlock
Submitted:	2 May 2011 14:37	Modified:	5 May 2011 19:15
Reporter:	Thomas Blohorn	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.56 and 5.5.11	OS:	Linux (Suse 10 32 bits)
Assigned to:		CPU Architecture:	Any
Tags:	deadlock

Description:
An unexpected index deadlock appears in this case:

Transaction  Operation                      Commentary
  T1         insert parent X                no child for the moment
  T1         insert child of X in fratrie   no child so nothing to insert
  T2         insert parent Y                no child for the moment
  T2         insert child of Y in fratrie   no child so nothing to insert
  T2         insert child A of Y            T2 is lock!
  T1         insert child B of X            deadlock!!

The deadlock is with 2 X index lock (FK Parent/Child)

How to repeat:

ROOTPASS=XXXXX

function test
{
mysql --user=root --password=$ROOTPASS <<! &
  drop database if exists testdl;
  create database testdl;
  use testdl;
  create table tfratrie (id int) engine=innodb ;

  create table parent (id int, primary key (id)) engine=innodb;

  create table child (
    id int,
    parent_id int not null,
    primary key (id),
    constraint child_of_ibfk foreign key (parent_id) references parent (id)
  ) engine=innodb;
    
  start transaction;
  insert into parent (id) values (1);
  insert into tfratrie select id from child where parent_id=1;
  select sleep(1);
  insert into child (id, parent_id) values (1,1);
!

sleep 0.5

mysql --user=root --password=$ROOTPASS <<!
  use testdl;
  start transaction;
  insert into parent (id) values (2);
  insert into tfratrie select id from child where parent_id=2;
  insert into child (id, parent_id) values (2,2);
!
}

test  > out.log

Suggested fix:

No deadlock!

Change to innodb category

whith READ-COMMITTED no deadlock !!!

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #25847

Thanks for your analyse.
Sorry for the duplicate but I do not identify it was the same bug!
Now I feel less alone ;)