Re: locking question when using before update trigger

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;

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

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

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

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_at_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