Re: locking question when using before update trigger

On Tue, 28 Jan 2003 15:29:57 -0500, Tim Callaghan <timc_at_channel1.com> wrote:

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

I have a little more information (and a real point of confusion for me):

There is an index on the column being updated in the trigger. If I drop the index then I don't have a blocking issue anymore (finally something I can work with). Unfortunately, if I recreate this index using exactly the same script that initially created it the blocking issue does not come back (not I'm really at a loss).

-Tim Received on Tue Jan 28 2003 - 15:40:02 CST