Re: locking question when using before update trigger

Tom, in your example, would creating the trigger before inserting the rows make a difference?

If everything is created before rows inserted, the referential integrity plan would be created with bad statistics, and the referential integrity check would do a table scan instead of an index scan/rowid table access. Therefore, DML that causes the referential integrity plan to be executed would lock the table and produce the deadlock.

To explain Tim's experience with dropping/re-creating the index, I would say that this invalidated the referential integrity plan and caused it to be re-compiled. Presto, the deadlock is gone. He could probably gotten the same result by updating the statistics.

Without the before/after explain plan output, who knows what went on. Also, I am a bit sketchy with Oracle's referential integrity execution.

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:b16sb705iu_at_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 Wed Jan 29 2003 - 08:32:57 CST