Re: locking question when using before update trigger

On Tue, 28 Jan 2003 16:40:02 -0500, Tim Callaghan <timc_at_channel1.com> wrote:

>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

I added a little more information after spending the last 4 hours trying to find the root cause of my issue. I'm baffled that when I drop the index I don't have the blocking issue and after I recreate the index the blocking issue does not return.

-Tim Received on Tue Jan 28 2003 - 15:52:44 CST