Re: locking question when using before update trigger

Brian,

I've "full analyzed compute statistics" and "analyze table <table> validate structure cascade" all objects in the schema to try and redo the statistics. Same results.

Any other ideas? Your post at least gives me some hope as to a reasonable explanation being possible.

-Tim

On Wed, 29 Jan 2003 14:32:57 GMT, "Brian E Dick" <bdick_at_cox.net> wrote:

>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 - 09:09:08 CST