Trigger impact on table locks

View All Public Scripts Login to Run Script

Script Name Trigger impact on table locks
Visibility Unlisted - anyone with the share link can access
Description Wrong locks on child table with: - single/separate insert/update trigger on parent table - with/without index on child table foreign key
Area SQL General
Contributor Iudith Mentzel
Created Friday July 17, 2020

Statement 1

create table parent ( 
   p     integer primary key, 
   descr varchar2(10), 
   created date, 
   updated date 
)

Table created.

Statement 2

create table child ( 
   c     integer primary key, 
   p     integer references parent (p), 
   descr varchar2(10), 
   created date, 
   updated date 
)

Table created.

Statement 3

create sequence parent_seq

Sequence created.

Statement 4

create sequence child_seq

Sequence created.

Statement 5

begin 
   insert into parent (p, descr, created) values (parent_seq.nextval, 'p1', sysdate); 
   insert into child (c, p, descr, created)  values (child_seq.nextval, parent_seq.currval, 'c1', sysdate); 
   commit; 
end;

Statement processed.

Statement 6

Helper procedure for checking table locks in LiveSQL

create or replace procedure p_chk_tab_lock (p_table in varchar2) 
as 
   pragma autonomous_transaction; 
 
   e_locked  exception; 
   pragma exception_init (e_locked, -54); 
begin 
   execute immediate 'lock table '||p_table||' in exclusive mode nowait'; 
   rollback; 
   dbms_output.put_line('Table '||p_table||' is not locked.'); 
exception 
     when e_locked then 
          dbms_output.put_line('Table '||p_table||' is locked.'); 
end;

Procedure created.

Statement 7

single trigger on parent, no index on child(p)

TEST 1

create or replace trigger parent_trg 
  before insert or update on parent 
  for each row 
begin 
   if inserting then 
       if :new.p is null then 
          :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!! 
       end if; 
      :new.created := sysdate; 
   elsif 
      updating then 
      :new.updated := sysdate; 
   end if; 
end;

Trigger created.

Statement 8

update parent row & check locks - CORRECT !

update parent 
set descr = 'pp1' 
where p = 1

1 row(s) updated.

Statement 9

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is not locked.

Statement 10
```
rollback 
```
```
Statement processed.
```

Statement 11

insert parent row & check locks - WRONG !!!

insert into parent (descr) 
values ('p2')

1 row(s) inserted.

Statement 12

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is locked.

Statement 13
```
rollback 
```
```
Statement processed.
```
Statement 14
separate triggers on parent, no index on child(p)
TEST 2
```
drop trigger parent_trg 
```
```
Trigger dropped.
```

Statement 15

create or replace trigger parent_ins_trg 
  before insert on parent 
  for each row 
begin 
   if :new.p is null then 
      :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!! 
   end if; 
  :new.created := sysdate; 
end;

Trigger created.

Statement 16

create or replace trigger parent_upd_trg 
  before update on parent 
  for each row 
begin 
  :new.updated := sysdate; 
end;

Trigger created.

Statement 17

update parent row & check locks - CORRECT !

update parent 
set descr = 'pp1' 
where p = 1

1 row(s) updated.

Statement 18

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is not locked.

Statement 19
```
rollback 
```
```
Statement processed.
```

Statement 20

insert parent row & check locks - WRONG !!!

insert into parent (descr) 
values ('p2')

1 row(s) inserted.

Statement 21

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is locked.

Statement 22
```
rollback 
```
```
Statement processed.
```

Statement 23

create index on child(p)

create index child_ix_p on child(p)

Index created.

Statement 24
single trigger on parent, with index on child(p)
TEST 3
```
drop trigger parent_ins_trg 
```
```
Trigger dropped.
```

Statement 25

drop trigger parent_upd_trg

Trigger dropped.

Statement 26

create or replace trigger parent_trg 
  before insert or update on parent 
  for each row 
begin 
   if inserting then 
       if :new.p is null then 
          :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!! 
       end if; 
      :new.created := sysdate; 
   elsif 
      updating then 
      :new.updated := sysdate; 
   end if; 
end;

Trigger created.

Statement 27

update parent row & check locks - WRONG !!!

update parent 
set descr = 'pp1' 
where p = 1

1 row(s) updated.

Statement 28

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is locked.

Statement 29
```
rollback 
```
```
Statement processed.
```

Statement 30

insert parent row & check locks - WRONG !!!

insert into parent (descr) 
values ('p2')

1 row(s) inserted.

Statement 31

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is locked.

Statement 32
```
rollback 
```
```
Statement processed.
```
Statement 33
separate triggers on parent, with index on child(p)
TEST 4
```
drop trigger parent_trg 
```
```
Trigger dropped.
```

Statement 34

create or replace trigger parent_ins_trg 
  before insert on parent 
  for each row 
begin 
   if :new.p is null then 
      :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!! 
   end if; 
  :new.created := sysdate; 
end;

Trigger created.

Statement 35

create or replace trigger parent_upd_trg 
  before update on parent 
  for each row 
begin 
  :new.updated := sysdate; 
end;

Trigger created.

Statement 36

update parent row & check locks - CORRECT !

update parent 
set descr = 'pp1' 
where p = 1

1 row(s) updated.

Statement 37

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is not locked.

Statement 38
```
rollback 
```
```
Statement processed.
```

Statement 39

insert parent row & check locks - WRONG !!!

insert into parent (descr) 
values ('p2')

1 row(s) inserted.

Statement 40

begin 
   p_chk_tab_lock ('parent'); 
   p_chk_tab_lock ('child'); 
end;

Statement processed.
Table parent is locked.
Table child is locked.

Statement 41
```
rollback 
```
```
Statement processed.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database