MySQL Locking Behaviour different from Oracle with FK invovled

 There is no locking observed to run following code in concurrent sessions in Oracle database: (S1 = first session, S2 = second session)

-- simulate FK locking in Oracle

create table p (id number, name varchar2(10), 

constraint p_pk primary key (id));

create table c (id number, name varchar2(10), pid number,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;

-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);

-- Test 2, insert into c while update p with same pid

(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;

-- Test 3, update p1 while update p2 with same pid

(S1) update p set name='c' where id=;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;

Lock Error "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction" observed in MySQL as child table update requires lock on parent table primary key.

-- simulate FK locking in MySQL

create table p (id int, name varchar(10), 

constraint p_pk primary key (id));

create table c (id int, name varchar(10), pid int,

constraint c_pk primary key (id), 

constraint c_p_fk foreign key (pid) references p(id));

insert into p values (1,'a');

insert into p values (2,'b');

insert into c values (1,'a',1);

commit;

SET autocommit=0;

-- Test 1, update p while insert into c with same pid

(S1) update p set name='c' where id=2;

(S2) insert into c values (2,'b',2);

MySQL [test]> insert into c values (2,'b',2);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 2977, ACTIVE 48 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 50, OS thread handle 47659528083200, query id 16389 10.1.1.49 admin update

insert into c values (2,'b',2)

------- TRX HAS BEEN WAITING 48 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2977 lock mode S locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000ba0; asc       ;;

 2: len 7; hex 02000000ac0151; asc       Q;;

 3: len 1; hex 63; asc c;;

------------------

---TRANSACTION 2976, ACTIVE 132 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16431 10.1.1.49 admin starting

show engine innodb status

-- Test 2, insert into c while update p with same pid

(S1) insert into c values (2,'b',2);

(S2) update p set name='c' where id=2;

MySQL [test]> update p set name='c' where id=2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 2994, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 50, OS thread handle 47659528083200, query id 16777 10.1.1.49 admin updating

update p set name='c' where id=2

------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 2994 lock_mode X locks rec but not gap waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000002; asc     ;;

 1: len 6; hex 000000000b9e; asc       ;;

 2: len 7; hex 010000013202d3; asc     2  ;;

 3: len 1; hex 62; asc b;;

------------------

---TRANSACTION 2989, ACTIVE 24 sec

3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16762 10.1.1.49 admin

-- Test 3, update p1 while update p2 with same pid

(S1) update p set name='c' where id=1;

(S2) update c set pid=2 where id=1;

(S2) update c set pid=1 where id=1;

MySQL [test]> update c set pid=2 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MySQL [test]> update c set pid=1 where id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

---TRANSACTION 3001, ACTIVE 69 sec updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2

MySQL thread id 50, OS thread handle 47659528083200, query id 16964 10.1.1.49 admin updating

update c set pid=1 where id=1

------- TRX HAS BEEN WAITING 37 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 9 page no 4 n bits 72 index PRIMARY of table `test`.`p` trx id 3001 lock mode S locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

 0: len 4; hex 80000001; asc     ;;

 1: len 6; hex 000000000bb8; asc       ;;

 2: len 7; hex 02000002040151; asc       Q;;

 3: len 1; hex 63; asc c;;

------------------

---TRANSACTION 3000, ACTIVE 78 sec

2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1

MySQL thread id 51, OS thread handle 47659548722944, query id 16942 10.1.1.49 admin