Re: Concurrency problem with INSTEAD OF trigger

<fitzjarrell_at_cox.net> a écrit dans le message de news:1112075343.521716.308950_at_g14g2000cwa.googlegroups.com...
|
| 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
|

Thanks to avoid me to answer to this question once more ( and certainly give a less clear reply ;-) ).

Regards
Michel Cadot Received on Tue Mar 29 2005 - 00:20:16 CST