Re: Concurrency problem with INSTEAD OF trigger

smauldin_at_ingrian.com wrote:
> I have a table called TEST_TRIG and a view on the table called
> TEST_TRIG_VIEW.
> I created an INSTEAD OF trigger on TEST_TRIG_VIEW to modify the table
> and other info
> when the view is updated. If two update statements from two
different
> session execute an
> update that will modify a column in the where clause I see unexpected
> results. The second
> statement executes using the old column value in the where clause.
>
> statement 1: update TEST_TRIG_VIEW set EMPNO = 1111 where EMPNO = 1;
>
> statement 2: update TEST_TRIG_VIEW set EMPNO = 9999 where EMPNO = 1;
>
> commit statement 1
> commit statement 2
> I would expect statement 2 not to update any rows because statement
> changed the
> EMPNO value from 1 to 1111. This problem does not occur when
performed
> on the table directly.
>
> --create table, view, and instead of trigger
> create table TEST_TRIG (EMPNO number(4), ENAME varchar2(10));
>
> insert into TEST_TRIG(EMPNO, ENAME) values (1, 'Billy');
> insert into TEST_TRIG(EMPNO, ENAME) values (2, 'Johnny');
> insert into TEST_TRIG(EMPNO, ENAME) values (3, 'Sammy');
>
> create or replace view TEST_TRIG_VIEW(EMPNO,ENAME,THE_ROW_ID)
> as
> select EMPNO, ENAME, ROWID
> from TEST_TRIG;
>
> create or replace trigger TEST_TRIG_UPD_TRIG instead of update on
> TEST_TRIG_VIEW
> referencing NEW as NEW OLD as OLD
> for each row
> begin
> update TEST_TRIG set EMPNO = :NEW.EMPNO, ENAME = :NEW.ENAME
> where ROWID = :OLD.THE_ROW_ID;
> end;
> /
>
> -- Test from sqlplus session
> -- sqlplus session 1
> update TEST_TRIG_VIEW set EMPNO = 1111 where EMPNO = 1;
>
> -- sqlplus session 2
> update TEST_TRIG_VIEW set EMPNO = 9999 where EMPNO = 1;
>
> -- sqlplus session 1
> commit;
>
> -- sqlplus session 2
> commit;
>
> -- select to get the value of EMPNO
> select * from TEST_TRIG_VIEW;
>
> -Stephen

First it was a 'race condition', now it's a 'concurrency problem'. You were answered correctly in your previous thread (the thread on the 'race condition', which is EXACTLY the same post) by Michel Cadot. His response is no less correct with THIS post as it was with your previous attempt to solicit a response. You're updating using the ROWID, not a column value, and the 'problem' would be the same whether or not you use a trigger. As such it doesn't matter WHICH update hits first, they will BOTH successfully update the same row. This isn't a 'race condition', it isn't a 'concurency problem', it's poor code. Painting the same automobile with a different color does not fix the transmission, it only changes your purple vehicle which doesn't run properly into a green vehicle with the same problem. The original evaluation is no less valid simply because the answer doesn't match your desired response. Apparently the truth is a message you cannot accept.

Fix the code, fix the 'problem'. It is, truly, that simple.

David Fitzjarrell Received on Mon Mar 28 2005 - 23:49:03 CST