Skip to Main Content
  • Questions

  • "BEFORE EACH ROW" Trigger causes table locks without updating primary key column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Juergen.

Asked: January 09, 2018 - 5:55 pm UTC

Last updated: July 18, 2020 - 4:02 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I've found a strange behaviour with our "BEFORE EACH ROW" triggers.

In our database we have some tables that are refenrenced very often (50-80 references).
All our tables have five common columns PK, CREATIONTIME, CREATIONUSER, UPDATETIME, UPDATEUSER.
There is also only one "BEFORE EACH ROW" trigger for each table, so we can control the order of execution of necessary statements. The most important functionality is setting the primary key from sequence.nextVal on INSERT. Also the four other common fields will be set in this trigger.

If I run an UPDATE against one table that is refernced by other tables all referenced tables will also be locked, although there is no primary key involved.

Please find my LiveSQL script with an example table structure.
Unfortunately the restrictions of LiveSQL don't allow the access to v$locked_objects or v$lock. Please find the comments with the expected results in this script.

Could you explain why all tables are locked if we use only one trigger? Is there any way to tell the database to lock the tables only if we update key columns or insert nwe records?

Could you also explain what kind of effect will these locks have on DML on the locked tables?

Kind regards,
Juergen


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_F3WE9J7YZ585BQ9IKV5LL1C28.html

and Connor said...

hi Juergen,

Thanks for the awesome test case. We actually answered a similar question to this not too long ago on AskTOM, but after 10mins of searching, I cannot locate it.

It is a limitation of triggers - from what I can work out, we basically examine the trigger content, and if there is *any* reference to the primary key column(s) as the target of an assignment, we are going to "assume the worst" that you might be updating it.

For example, even if I make the trigger patently false - you will still see the additional lock:

SQL> CREATE OR REPLACE TRIGGER TR_BR_A
  2  BEFORE UPDATE ON A
  3  FOR EACH ROW
  4  DECLARE
  5      tmpVar NUMBER;
  6  BEGIN
  7      IF false THEN
  8         :new.ID := 0;
  9      END IF;
 10  END;
 11  /

Trigger created.

SQL> UPDATE A set description = 'Live' where id = 1;

1 row updated.

SQL> select locked_mode, OBJECT_NAME from v$locked_object l, all_OBJECTS ob where ob.OBJECT_ID =l.OBJECT_ID;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 A
          3 B


So I'd recommend separating the triggers at this point. This can be avoided entirely when you get to 12c because you can have the sequence as just part of the table definition

SQL> CREATE TABLE A (
  2    ID           NUMBER(10)    default seq_a.nextval NOT NULL,
  3    DESCRIPTION  VARCHAR2(50)  NOT NULL,
  4    CREATIONUSER VARCHAR2(50)  NOT NULL,
  5    UPDATEUSER   VARCHAR2(50)  NOT NULL
  6    );

Table created.




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Do trigger locks may cause any problems?

Juergen, January 10, 2018 - 8:43 am UTC

Hi Connor,

Thank you for the very quick response.
Unfortunately we cannot use the 12c functionality, as most of our customers still uses 11g databases. So I think we will have to split the triggers for now.

But could you explain if the created locks have any restrictions for the DML on the locked tables, or is this merely an indication of possible changes to the primary key?

For example if I change the description of record 1 in my example and then try any DML on the record in table B that is referencing on record 1 in table A, would this cause any possible problems?

Kind regards,
Juergen
Connor McDonald
January 12, 2018 - 3:45 am UTC

The lock is TM,3 so whilst that is "low risk", there are some things that are still going to need care taken with, eg

SQL> truncate table b;
truncate table b
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> lock table b in exclusive mode nowait;
lock table b in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> alter table B add new_col int;
[stuck]


Separate insert trigger ?

lh, January 10, 2018 - 9:02 am UTC

Hi

Could workaround be, that there is separate insert trigger for table where primary key is populated.
This is assuming that in update,delete triggers there is not reference to primary key column PK


lh
Connor McDonald
January 12, 2018 - 3:27 am UTC

Yes, this is what is needed. As long as the update trigger does not contain an assignment (even if not run) to the primary key

Very strange result in 19c ...

Iudith Mentzel, July 16, 2020 - 10:26 pm UTC

Hello All,

@Connor, I guess that this is the thread that saved as basis for one of the topics in today's OH session.

I tried to reproduce the test today in LiveSQL (19c), and the behavior is even stranger ...

Using a trigger similar to the one of the original poster:

create or replace trigger tr_br_a
before insert or update on a
for each row
begin
if inserting then
if :new.id is null then
:new.id := seq_a.nextval;
end if;
:new.CREATIONUSER := USER;
elsif
updating then
:new.UPDATEUSER := USER;
end if;
end;
/

and the following helper procedure to check for 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;
/


then:

a. If UPDATING a parent row, only the parent table is locked, as it is expected :)

UPDATE A
set description = 'Live2'
where id = 2
/

1 row(s) updated.


begin
p_chk_tab_lock ('A');
p_chk_tab_lock ('B');
end;
/

Table A is locked.
Table B is not locked.

rollback
/


b. But, if we INSERT a parent row, then both the parent and the child tables are locked !!!

insert into A (description)
values ('Live2')
/

1 row(s) inserted.


begin
p_chk_tab_lock ('A');
p_chk_tab_lock ('B');
end;
/

Table A is locked.
Table B is locked.

rollback
/

And, the *SAME* happens if we separate the trigger into two separate triggers, one for INSERT and one for UPDATE,
as shown in the Office Hours demo !!!

So, Oracle is "clever enough" to distinguish between the INSERTING and UPDATING branches in the trigger text,
and it "knows" that when updating it should NOT lock the child table,
but NOT clever enough to realize that for INSERTING the assignment of :NEW.ID is not relevant for the child table locking.


I don't know what DB version you used in the Office Hours session demo, but it looks like 19c does have a bug here !

Thanks a lot & Best Regards,
Iudith Mentzel

My full test

Iudith Mentzel, July 17, 2020 - 4:46 am UTC

Hello All again,

Here is a full script with my test performed for trigger impact on table locks:

https://livesql.oracle.com/apex/livesql/s/kdwf9o8e4qpnlcznls8vgyq8f


Thanks a lot in advance & Best Regards,
Iudith Mentzel


Connor McDonald
July 17, 2020 - 7:31 am UTC

I don't see any problems with the script because we're talking different things here (this is why I had the index present in the office hours session)

- Without the index - I *do* need locks on the child because ... I don't have the FK index.

- Since 11, we've been doing level 2 locks on tables related by FKs because of some of the niche scenarios, eg online move or index rebuilds whilst there are uncomitted transactions etc etc....I can't remember the specifics, but we need them.

Here's your script with the advantage of seeing v$lock which we don't get in livesql :-(

SQL>
SQL> create table parent (
  2     p     integer primary key,
  3     descr varchar2(10),
  4     created date,
  5     updated date
  6  ) ;

Table created.

SQL>
SQL> create table child (
  2     c     integer primary key,
  3     p     integer references parent (p),
  4     descr varchar2(10),
  5     created date,
  6     updated date
  7  ) ;

Table created.

SQL>
SQL> create sequence parent_seq ;

Sequence created.

SQL>
SQL> create sequence child_seq ;

Sequence created.

SQL>
SQL> begin
  2     insert into parent (p, descr, created) values (parent_seq.nextval, 'p1', sysdate);
  3     insert into child (c, p, descr, created)  values (child_seq.nextval, parent_seq.currval, 'c1', sysdate);
  4     commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> -- TEST 1
SQL> -- single trigger on parent, no index on child(p)
SQL> create or replace trigger parent_trg
  2    before insert or update on parent
  3    for each row
  4  begin
  5     if inserting then
  6         if :new.p is null then
  7            :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!!
  8         end if;
  9        :new.created := sysdate;
 10     elsif
 11        updating then
 12        :new.updated := sysdate;
 13     end if;
 14  end;
 15  /

Trigger created.

SQL>
SQL> -- update parent row & check locks
SQL> update parent
  2  set descr = 'pp1'
  3  where p = 1 ;

1 row updated.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT

1 row selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- insert parent row & check locks
SQL> insert into parent (descr)
  2  values ('p2') ;

1 row created.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT
          2 CHILD

2 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- TEST 2
SQL> -- separate triggers on parent, no index on child(p)
SQL> drop trigger parent_trg ;

Trigger dropped.

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

Trigger created.

SQL>
SQL> create or replace trigger parent_upd_trg
  2    before update on parent
  3    for each row
  4  begin
  5    :new.updated := sysdate;
  6  end;
  7  /

Trigger created.

SQL>
SQL> -- update parent row & check locks
SQL> update parent
  2  set descr = 'pp1'
  3  where p = 1 ;

1 row updated.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT

1 row selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> --  insert parent row & check locks
SQL> insert into parent (descr)
  2  values ('p2') ;

1 row created.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT
          2 CHILD

2 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- create index on child(p)
SQL> create index child_ix_p on child(p) ;

Index created.

SQL>
SQL> -- TEST 3
SQL> -- single trigger on parent, with index on child(p)
SQL> drop trigger parent_ins_trg ;

Trigger dropped.

SQL>
SQL> drop trigger parent_upd_trg ;

Trigger dropped.

SQL>
SQL> create or replace trigger parent_trg
  2    before insert or update on parent
  3    for each row
  4  begin
  5     if inserting then
  6         if :new.p is null then
  7            :new.p   := parent_seq.nextval;   --> this row has impact, it will cause the child table to be locked !!!
  8         end if;
  9        :new.created := sysdate;
 10     elsif
 11        updating then
 12        :new.updated := sysdate;
 13     end if;
 14  end;
 15  /

Trigger created.

SQL>
SQL> -- update parent row & check locks
SQL> update parent
  2  set descr = 'pp1'
  3  where p = 1 ;

1 row updated.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT
          3 CHILD

2 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- insert parent row & check locks
SQL> insert into parent (descr)
  2  values ('p2') ;

1 row created.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT
          2 CHILD

2 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- TEST 4
SQL> -- separate triggers on parent, with index on child(p)
SQL> drop trigger parent_trg ;

Trigger dropped.

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

Trigger created.

SQL>
SQL> create or replace trigger parent_upd_trg
  2    before update on parent
  3    for each row
  4  begin
  5    :new.updated := sysdate;
  6  end;
  7  /

Trigger created.

SQL>
SQL> -- update parent row & check locks
SQL> update parent
  2  set descr = 'pp1'
  3  where p = 1 ;

1 row updated.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT

1 row selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL> -- insert parent row & check lock
SQL> insert into parent (descr)
  2  values ('p2') ;

1 row created.

SQL>
SQL> select locked_mode, object_name
  2  from v$locked_object l, all_objects ob
  3  where ob.object_id =l.object_id;

LOCKED_MODE OBJECT_NAME
----------- ----------------------------------------
          3 PARENT
          2 CHILD

2 rows selected.

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL>
SQL>


Martin ROSE, July 17, 2020 - 8:57 am UTC

Nobody has pointed out the elephant in the room, that using sequence numbers for PKs universally is bad database design.

They should only be used as surrogate PKs ie; where there is no natural primary key present in the data. Using seq. nos. everywhere slows down performance (which you can never get back), and complicates code for all those who have to read it.


Also, using row-level triggers universally is bad design too. A much more serious slowdown occurs during bulk operations. If you really must assign things at INSERT/UPDATE-time, do so in the issuing statement. Ie; include the seq. no. PK in the INSERT statement, or the update date/time in the UPDATE statement like any other column.

Using row-level triggers for these operations may be handy, but they're really poor for performance. (And ofc. if you avoid them altogether & your locking problem just dissolves away...).

There are other benefits too. You may in some scenarios wish to synchronise the times of the insert/updates across many records, so you can say they all occured at the same logical time. With row-level triggers stamping each record individually as they come thru, you cannot do this.
Connor McDonald
July 18, 2020 - 4:02 am UTC

that using sequence numbers for PKs universally is bad database design


Huh? I hope you mean as natural keys. For surrogates, they are a perfect choice imo.

Also, using row-level triggers universally is bad design too


In general I agree. Sometimes they are forced upon us though.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database