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
