Re: locking question when using before update trigger

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 28 Jan 2003 13:22:47 -0800
Message-ID: <b16sb705iu@drn.newsguy.com>

In article <6opd3vk4bnu7l0vg2vi2mjur0rcd7j696i_at_4ax.com>, Tim says...
>
>I'm trying to understand a locking issue I'm having with my database.
>
>Scenario is as follows:
>
>1. I have a before update trigger on all of my tables to update a
>timestamp column (so I know the last time a row was updated). This
>column is never updated directly by any SQL. Trigger looks like the
>following:
>
>create or replace trigger im_ins_upd_table_a
>BEFORE INSERT OR UPDATE ON table_a
>FOR EACH ROW
>BEGIN
> if not ((:new.replicated_flag = 'Y') and (:old.replicated_flag =
>'N')) THEN
> :new.last_touch_date := sysdate;
> END IF;
>END;
>
>2. I have 2 tables, 1 is named table_a and the other table_b. table_b
>has a foreign key to table_a. I have made sure that ALL foreign key
>columns in my schema are indexed.
>
>When I do an update on table_a (any column other than last_touch_date)
>any future inserts into table_b are blocked until I commit/rollback
>the update on table_a (even though I haven't even updated the column
>that was the foreign key from table_b).
>
>If I disable the trigger I have no blocking issues.
>
>Any help would be greatly appreciated.
>
>Tim Callaghan
>

something else is amiss here -- something missing from the example. Can you take my example and make it suffer from this problem:

ops$tkyte_at_ORA817DEV> create table table_a ( x int primary key, replicated_flag char(1), last_touch_date date );

Table created.

ops$tkyte_at_ORA817DEV> create table table_b ( x references table_a );

Table created.

ops$tkyte_at_ORA817DEV> create index table_b_idx on table_b(x);

Index created.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> insert into table_a select rownum, 'X', sysdate from all_users where rownum <= 5;

5 rows created.

ops$tkyte_at_ORA817DEV> commit;

Commit complete.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> create or replace trigger im_ins_upd_table_a   2 BEFORE INSERT OR UPDATE ON table_a
  3 FOR EACH ROW
  4 BEGIN
  5 if not ((:new.replicated_flag = 'Y') and (:old.replicated_flag = 'N'))   6 THEN

  7            dbms_output.put_line( 'touching ' || :new.x );
  8            :new.last_touch_date := sysdate;

  9 END IF;
 10 END;
 11 /

Trigger created.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> update table_a set x=x where rownum = 1;

touching 1

1 row updated.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> declare
  2 pragma autonomous_transaction;   3 begin

  4          insert into table_b values ( 3 );
  5          commit;

  6 end;
  7 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> lock table table_b in exclusive mode;

Table(s) Locked.

ops$tkyte_at_ORA817DEV>
ops$tkyte_at_ORA817DEV> declare
  2 pragma autonomous_transaction;   3 begin

  4          insert into table_b values ( 3 );
  5          commit;

  6 end;
  7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 4

I did the last part to show that if TABLE_B where locked -- the autonomous transaction would in fact be blocked. No matter what I do -- table_b is modifiable unless I lock it.

So, can you flesh out the example a bit more?

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jan 28 2003 - 15:22:47 CST