Re: Concurrency problem with INSTEAD OF trigger

smauldin_at_ingrian.com wrote:
> Dave,
>
> Sorry, I did not see Michel response to my previous post. The post
> should have asked, how can an instead of update trigger be written to
> have the same semantics for update as a table has?

I must admit my first response was wrong when I said, "Fix the code, fix the problem." Let us look at your test again to see why I was wrong and why changing the code probably won't do any good:

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

So far, so good. Now for your assessment of the situation:

"I would expect statement 2 not to update any rows because statement changed the EMPNO value from 1 to 1111."

It did, and it didn't. It did for the session performing the update; it didn't because the update for that session was not committed. As such statement 2 will not execute due to the lock on the row. Session 2 cannot view the data changes from Session 1 until Session 1 issues a commit. Until such time, at least in 9.2.0.6, Statement 2 will appear to 'hang' until the transaction in Session 1 completes. Presuming a commit is issued, Session 2 will now get a current picture of the data, execute the statement and find no rows matching the criteria. And your second update fails to change any data.

Now let's look at the trigger situation, but let's modify your trigger a bit:

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
dbms_output.put_line('EMPNO is currently: '||:old.empno); update TEST_TRIG set EMPNO = :NEW.EMPNO, ENAME = :NEW.ENAME where ROWID = :OLD.THE_ROW_ID;
end;
/

Let's now look at the output from each session for the update statements you've written:

Session 1 --
SQL> UPDATE TEST_TRIG_VIEW set EMPNO = 1111 where EMPNO = 1; EMPNO is currently: 1

1 row updated.

SQL> Session 2 --

SQL> UPDATE TEST_TRIG_VIEW set EMPNO = 9999 where EMPNO = 1; EMPNO is currently: 1

1 row updated.

SQL> Notice the EMPNO value is still 1 as Session 2 sees it in the view, which is normal as the session 'sees' an EMPNO value of 1 prior to the commit of Statement 1. As such it returns the proper ROWID, and, as you've seen, the update occurs, leaving you with an EMPNO of 9999 instead of the 1111 you'd envisioned. The EMPNO DOES change to 1111 for the period between the update from Session 1 and the commit from Session 2, you simply don't get to see that change. You can't change the code to use the EMPNO in the update, as no matter what the old value for EMPNO may be the update in that case would be successful, as you'd be comparing its value to its current value, updated or not, as you'd be using :old.empno in the where clause.

There appears to be no solution to your 'problem'. But, it isn't REALLY a problem because this is the way Oracle works. Session 1 issues an update to a table; Session 2 issues another update to the same table, same row (the where clauses in both update statements reference the same original value). As I explained earlier in this post the value of EMPNO does not change for others until Session 1 issues a commit. You're querying a view, not a table, and such locks don't exist against a view, so the update proceeds until it reaches the table, when the row lock halts the update process. The commit from Session 1 releases the lock, but the update is still operating upon a data image gleaned prior to the commit. The update proceeds since the rowid is the same for both transactions. This is why we see the original value of EMPNO in both transactions prior to the updates. And this is why your trigger performs as it does. And this is due to Oracle's multiversioning, functioning as designed, to prevent any 'dirty reads' (reads of uncommitted changes).

I hope this helps.

David Fitzjarrell Received on Tue Mar 29 2005 - 21:42:04 CST