View Franck Pachot profile on LinkedIn

Blog: http://blog.developpez.com/pachot/
Auteur: Franck Pachot, DBA Oracle en Suisse Romande. (contact@pachot.net)
 This page is referenced by the micro-learning serie (short posts to present an Oracle feature or concept) from this French Blog Post.

Which locks are requested by some DML operations (including with referential integrity)

1. I create my tables

1.1. the parent table

create table TEST_PARENT (id constraint TEST_PARENT_PK primary key,col) as select 1000*rownum,1000*rownum from dual connect by level<=5;
Table created.
select * from TEST_PARENT;
ID COL
1000 1000
2000 2000
3000 3000
4000 4000
5000 5000

1.2. child table references parent - on delete cascade - no index on foreign key

create table TEST_CASCDEL (id number references TEST_PARENT on delete cascade,subid number);
Table created.
insert into TEST_CASCDEL select id,n from (select id from TEST_PARENT),(select rownum n from dual connect by level <=2);
10 rows created.
commit;
Commit complete.
select * from TEST_CASCDEL order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.

I test the delete on parent:

delete from TEST_PARENT where id=1000;
1 row deleted.
select * from TEST_CASCDEL order by 1,2;
ID SUBID
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
8 rows selected.
‣ child rows have been deletedrollback;
Rollback complete.

1.3. child table references parent - on delete set null - no index on foreign key

create table TEST_CASCNUL (id number references TEST_PARENT on delete set null,subid number);
Table created.
insert into TEST_CASCNUL select id,n from (select id from TEST_PARENT),(select rownum n from dual connect by level <=2);
10 rows created.
commit;
Commit complete.
select * from TEST_CASCNUL order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.

I test the delete on parent:

delete from TEST_PARENT where id=1000;
1 row deleted.
select * from TEST_CASCNUL order by 1,2;
ID SUBID
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
  1
  2
10 rows selected.
‣ child rows are now orhpanedrollback;
Rollback complete.

1.4. child table references parent - no cascading (ORA-02292)- no index on foreign key

create table TEST_CASCERR (id number references TEST_PARENT,subid number);
Table created.
insert into TEST_CASCERR select id,n from (select id from TEST_PARENT),(select rownum n from dual connect by level <=2);
10 rows created.
commit;
Commit complete.
select * from TEST_CASCERR order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.

I test the delete on parent:

delete from TEST_PARENT where id=1000;
delete from TEST_PARENT  where id=1000
*
ERROR at line 1:
ORA-02292: integrity constraint (FRANCK.SYS_C0011682) violated - child record found
‣ ORA-02292 where trying to delete

rollback;

Rollback complete.

1.5. and now I add some parent rows with no child

1.6. I've rolled back everything - I check my tables

select * from TEST_PARENT order by 1;
ID COL
1000 1000
2000 2000
3000 3000
4000 4000
5000 5000
select * from TEST_CASCERR order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.
select * from TEST_CASCNUL order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.
select * from TEST_CASCDEL order by 1,2;
ID SUBID
1000 1
1000 2
2000 1
2000 2
3000 1
3000 2
4000 1
4000 2
5000 1
5000 2
10 rows selected.

2. Locks acquired by the different DML on the PARENT

2.1. insert into parent

(connected to new session)

insert into TEST_PARENT values(9000,9000);

1 row created.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64366811710  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

9 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Row-S on referential integrity opposite tables 11.2.0.3 Row-X where DML occurs, Row-X on referential integrity opposite tables 10.2.0.4 Row-X where DML occurs, Row-S on referential integrity opposite tables

2.2. update parent referenced column

(connected to new session)

update TEST_PARENT set id=9001 where id=9000;

1 row updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64368321409  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

15 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Share on child table (aquired/release for each row) - because of non indexed foreign key 11.2.0.3 idem 10.2.0.4 idem

2.3. update parent non referenced column

(connected to new session)

update TEST_PARENT set col=9001 where id=9001;

1 row updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
XCTEND rlbk=0, rd_only=0, tim=64368711702  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-X where DML occurs, no impact of referential integrity
11.2.0.3  idem
10.2.0.4  idem

2.4. delete from parent

(connected to new session)

delete from TEST_PARENT where id=9001;

1 row deleted.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64369108686  
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

17 rows selected.
‣ Locks in different versions: 12.1.0.1 Same as updating the key with an additional Row-X on child that have 'on cascade' - So Share becomes SSX. 11.2.0.3 idem 10.2.0.4 idem

2.5. merge that inserts

(connected to new session)

merge into TEST_PARENT using (select 9000 id,9000 col from dual) source on (TEST_PARENT.id=source.id) when not matched then insert values (source.id,source.col) /

1 row merged.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64369535769  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

9 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Row-S on referential integrity opposite tables 11.2.0.3 Row-X where DML occurs, Row-X on referential integrity opposite tables 10.2.0.4 Share on all child released immediately (for each row)

2.6. merge that updates non key column

(connected to new session)

merge into TEST_PARENT using (select 9000 id,9001 col from dual) source on (TEST_PARENT.id=source.id) when matched then update set TEST_PARENT.col=source.col /

1 row merged.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
XCTEND rlbk=0, rd_only=0, tim=64369929543  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-X where DML occurs, no impact of referential integrity
11.2.0.3  Row-X where DML occurs, no impact of referential integrity
10.2.0.4  Share on all child released immediately (for each row)

2.7. merge that updates key column

(connected to new session)

merge into TEST_PARENT using (select 9001 id,9001 col from dual) source on (TEST_PARENT.col=source.col) when matched then update set TEST_PARENT.id=source.id /

1 row merged.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64370304906  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

15 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Share on child table (aquired/release for each row) - because of non indexed foreign key 11.2.0.3 Row-X where DML occurs, Share on child table (aquired/release for each row) - because of non indexed foreign key 10.2.0.4 Share on all child released immediately (2x for each row)

2.8. merge that deletes

(connected to new session)

merge into TEST_PARENT using (select 9001 id,9002 col from dual) source on (TEST_PARENT.id=source.id) when matched then update set TEST_PARENT.col=source.col delete where TEST_PARENT.col=9002 /

1 row merged.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64370729414  
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

17 rows selected.
‣ Locks in different versions: 12.1.0.1 Same as updating the key with an additional Row-X on child that have 'on cascade' - So Share becomes SSX. 11.2.0.3 Same as updating the key with an additional Row-X on child that have 'on cascade' - So Share becomes SSX. 10.2.0.4 Share on all child released immediately (2x for each row)

2.9. merge that does all of that

to avoid error as I 'll touch existing rows

delete from TEST_CASCERR where id=1000;
2 rows deleted.

now the merge

(connected to new session)

merge into TEST_PARENT using (select 1000 id,1001 col from dual union all select 2000 id,2001 col from dual union select 9000 id,9001 col from dual) source on (TEST_PARENT.id=source.id) when not matched then insert values (source.id,source.col) when matched then update set TEST_PARENT.col=source.col delete where TEST_PARENT.id=1000 /

3 rows merged.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018451-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_CASCNUL"
ksqcnv: TM-00018452-00000000-00000003-00000000 mode=3 timeout=21474836 convert TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64371131541  
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

17 rows selected.
‣ Until 11g all merge behaves as if it can do all operations

2.10. Some DML on the child

insert

(connected to new session)

insert into TEST_CASCERR values(2000,2000);

1 row created.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64371545920  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-X on opposite side
11.2.0.3  Row-X on opposite side
10.2.0.4  Row-S on opposite side

update non foreign key

(connected to new session)

update TEST_CASCERR set subid=0 where id=2000;

3 rows updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64371931571  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"


‣ Locks in different versions:
12.1.0.1  no lock outside of the updated table
11.2.0.3  idem
10.2.0.4  idem

update foreign key

(connected to new session)

update TEST_CASCERR set id=3000 where id=2000;

3 rows updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64372304509  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-X on opposite side
11.2.0.3  Row-X on opposite side
10.2.0.4  Row-S on opposite side

delete

(connected to new session)

delete from TEST_CASCERR where id=3000;

5 rows deleted.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64372702545  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-S on opposite side
11.2.0.3  Row-X on opposite side
10.2.0.4  Row-S on opposite side

getback to original values

update TEST_PARENT set col=id;
5 rows updated.
delete TEST_PARENT where id=9000;
1 row deleted.
commit;
Commit complete.

3. Same operations with an index on the foreign key

create index TEST_CASCERR_FK on TEST_CASCERR(id);
Index created.
create index TEST_CASCNUL_FK on TEST_CASCNUL(id);
Index created.
create index TEST_CASCDEL_FK on TEST_CASCDEL(id);
Index created.

3.1. insert into parent

(connected to new session)

insert into TEST_PARENT values(9000,9000);

1 row created.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=2 flags=0x401 timeout=21474836 *** get lock TM Row-S on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64373142363  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

9 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Row-S on referential integrity opposite tables 11.2.0.3 Row-X where DML occurs, Row-X on referential integrity opposite tables 10.2.0.4 Row-X where DML occurs, Row-S on referential integrity opposite tables

3.2. update parent referenced column

(connected to new session)

update TEST_PARENT set id=9001 where id=9000;

1 row updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64373562431  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

9 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Row-X on child table until the end of transaction 11.2.0.3 Row-X where DML occurs, Row-X on referential integrity opposite tables 10.2.0.4 Row-X where DML occurs, Row-S on referential integrity opposite tables

3.3. update parent non referenced column

(connected to new session)

update TEST_PARENT set col=9001 where id=9001;

1 row updated.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
XCTEND rlbk=0, rd_only=0, tim=64373946490  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"


‣ Locks in different versions:
12.1.0.1  Row-X where DML occurs, no impact of referential integrity
11.2.0.3  idem
10.2.0.4  idem

3.4. delete from parent

(connected to new session)

delete from TEST_PARENT where id=9001;

1 row deleted.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=0, tim=64374295629  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

9 rows selected.
‣ Locks in different versions: 12.1.0.1 Row-X where DML occurs, Row-X on child table until the end of transaction 11.2.0.3 Row-X where DML occurs, Row-X on referential integrity opposite tables 10.2.0.4 Row-X where DML occurs, Row-S on referential integrity opposite tables

4. Second level locks (1st level have index on foreign key, second level does not)

4.1. I create those tables

now I empty all the child tables as that do not make a difference.

delete TEST_CASCERR;
4 rows deleted.
delete TEST_CASCNUL;
10 rows deleted.
delete TEST_CASCDEL;
8 rows deleted.

first I need a new pk in the child, that has no referential integrity with parent

alter table TEST_CASCERR add surrogate_key number;
Table altered.
alter table TEST_CASCERR add constraint TEST_CASCERR_PK primary key (surrogate_key);
Table altered.
create table TEST_CASCERR_SUB (surrogate_key number references TEST_CASCERR);
Table created.
alter table TEST_CASCNUL add surrogate_key number;
Table altered.
alter table TEST_CASCNUL add constraint TEST_CASCNUL_PK primary key (surrogate_key);
Table altered.
create table TEST_CASCNUL_SUB (surrogate_key number references TEST_CASCNUL);
Table created.
alter table TEST_CASCDEL add surrogate_key number;
Table altered.
alter table TEST_CASCDEL add constraint TEST_CASCDEL_PK primary key (surrogate_key);
Table altered.
create table TEST_CASCDEL_SUB (surrogate_key number references TEST_CASCDEL);
Table created.

4.2. And delete from the parent

(connected to new session)

delete from TEST_PARENT;

4 rows deleted.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018451-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCDEL"
ksqgtl *** TM-00018452-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCNUL"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_CASCERR"
ksqgtl *** TM-0001847C-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCNUL_SUB"
ksqrcl: TM-0001847C-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL_SUB"
ksqgtl *** TM-0001847E-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCDEL_SUB"
ksqrcl: TM-0001847E-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL_SUB"
XCTEND rlbk=0, rd_only=0, tim=64375064672  
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018452-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCNUL"
ksqrcl: TM-00018451-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCDEL"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

13 rows selected.
‣ Locks in different versions: 12.1.0.1 The on delete cascade cascades the delete to TEST_CASCDEL, so Share lock is requested for its child that don't have index on foreign key but I don't expect the same with TEST_CASCDEL as the 'set null' touches a non referenced column. [ Bug 12313173 ]

5. And now a funny thing.

5.1. In another session I lock only in Row-S for 15 seconds

declare j number; begin -- define the job dbms_job.submit(j,' lock table TEST_PARENT in row share mode nowait; dbms_lock.sleep(20); commit; '); commit; -- wait for it to start dbms_lock.sleep(10); end; /
PL/SQL procedure successfully completed.
select * from dba_jobs;
JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME B INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE
1001 FRANCK FRANCK FRANCK     09-AUG-13 16:59:19 09-AUG-13 16:59:17 8 N null   lock table TEST_PARENT in row share mode nowait; dbms_lock.sleep(20); commit; NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000000000000 0
select * from dba_jobs_running;
SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
402 1001       09-AUG-13 16:59:19 0

checking the locks on TEST_PARENT

select * from v$lock where type='TM' and id1=(select object_id from user_objects where object_name='TEST_PARENT');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
00007F048A7946D0 00007F048A794738 402 TM 99407 0 2 0 8 0 3

‣ Now we have a concurrent session having Row-S lock

(connected to new session)

set timing on lock table TEST_PARENT in share row exclusive mode;

Table(s) Locked.
Elapsed: 00:00:00.00
set timing off ‣ Row-X is compatible with Row-S so we were able to acquire it

but now for the fun we rollback;

rollback;
Rollback complete.

check that the concurrent session is still there:

select * from v$lock where type='TM' and id1=(select object_id from user_objects where object_name='TEST_PARENT');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
00007F6A2F35A480 00007F6A2F35A4E8 402 TM 99407 0 2 0 8 0 3

and we do exactly the same

set timing on lock table TEST_PARENT in share row exclusive mode;
Table(s) Locked.
Elapsed: 00:00:11.45
set timing off ‣ We had to wait several seconds until the concurrent session finishes. Because of our rollback, it seems we needed an eXclusive lock on the table for a short moment ! http://www.freelists.org/post/oracle-l/Lock-Table-Oddity

let's look at the locks

select * from v$lock where type='TM' and id1=(select object_id from user_objects where object_name='TEST_PARENT');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
00007F6A2EDB7520 00007F6A2EDB7588 282 TM 99407 0 5 0 0 0 3

‣ we hold only the 'S/Row-X' as requested

commit;

Commit complete.

but here are all the locks that were aqauired and released (from event 10704):

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_PARENT"
XCTEND rlbk=1, rd_only=1, tim=64385901938  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=5 flags=0x401 timeout=21474836 *** get lock TM S/Row-X on "FRANCK"."TEST_PARENT"
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=6 flags=0x401 timeout=21474836 *** get lock TM Exclusive on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-0001844F-00000001-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqcnv: TM-0001844F-00000000-00000003-00000000 mode=5 timeout=21474836 convert TM S/Row-X on "FRANCK"."TEST_PARENT"
ksqrcl: TM-0001844F-00000001-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"
XCTEND rlbk=0, rd_only=1, tim=64397609237  
ksqrcl: TM-0001844F-00000000-00000003-00000000 release TM on "FRANCK"."TEST_PARENT"

11 rows selected.
‣ the first 'share row exclusive' required only 'S/Row-X'. But the second one, after the rollback: has released the 'S/Row-X' to acquire a 'Exclusive' and then acquire 'Row-X' and convert to 'S/Row-X'

6. and another funny thing (http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/)

6.1. build the test case

I need only two tables

drop table TEST_CASCERR_SUB;
Table dropped.
drop table TEST_CASCNUL_SUB;
Table dropped.
drop table TEST_CASCDEL_SUB;
Table dropped.
drop table TEST_CASCNUL;
Table dropped.
drop table TEST_CASCDEL;
Table dropped.
drop index TEST_CASCERR_FK;
Index dropped.

6.2. we know that a delete from parent locks the child in Share mode because there is no index on the foreign key

(connected to new session)

delete from TEST_PARENT;

0 rows deleted.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-0001844F-00000000-00000003-00000000 mode=3 flags=0x401 timeout=21474836 *** get lock TM Row-X on "FRANCK"."TEST_PARENT"
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"


6.3. We can prevent that kind of operation:

disable DML locks on child

alter table TEST_CASCERR disable table lock;
Table altered.
lock table TEST_CASCERR in share mode;
lock table TEST_CASCERR in share mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for TEST_CASCERR

Now I delete from parent

(connected to new session)

delete from TEST_PARENT;

0 rows deleted.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
XCTEND rlbk=0, rd_only=1, tim=64398848305  


‣ It should raise an error

(connected to new session)

insert into TEST_PARENT values(1,1);

1 row created.
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
XCTEND rlbk=0, rd_only=0, tim=64399223228  


(connected to new session)

delete from TEST_PARENT;

delete from TEST_PARENT
            *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for TEST_CASCERR
commit;
Commit complete.

(script to read trace from events 10704 and 10046)

/
trace line TM locking info (decoding object_id)
ksqgtl *** TM-00018453-00000000-00000003-00000000 mode=4 flags=0x401 timeout=21474836 *** get lock TM Share on "FRANCK"."TEST_CASCERR"
ksqrcl: TM-00018453-00000000-00000003-00000000 release TM on "FRANCK"."TEST_CASCERR"
XCTEND rlbk=0, rd_only=1, tim=64399601684  


7. Version Information

select sysdate,banner from v$version where rownum=1;
SYSDATE BANNER
09-AUG-13 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
select * from v$parameter where isdefault='FALSE' and num not in (41,890,919,1306,2008,2046,2204,2234,2258,2260,3266,2030,888,1213,1305,1652,2432);
NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES ISSYS_MOD ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION UPDATE_COMMENT HASH CON_ID
940 db_block_size 3 8192 8192 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE Size of database block in bytes   3433134853 3
1104 compatible 2 12.1.0.0.0 12.1.0.0.0 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE Database will be completely compatible with this software version   2586206788 3
3322 enable_pluggable_database 1 TRUE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Enable Pluggable Database   515850361 3
select dbid,log_mode,platform_name,cdb from v$database;
DBID LOG_MODE PLATFORM_NAME CDB
483990585 ARCHIVELOG Linux x86 64-bit YES
spool off

note:

spool used for comments about other versions: 10g spool here 11g spool here 12c spool here

8. More information on Oracle table locks

Foreign key indexing: Lock modes: http://www.soug.ch/fileadmin/user_upload/Newsletter/NL_public/NL_2013_1_Award_Article.pdf