Alberto -- Thanks for the question regarding "write "consistency"", version 8.1.7/9.0.1

Submitted on 2-Aug-2003 8:22 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 29-Aug-2008 15:45

You Asked

Hi Tom,

I have always worked under the assumption that a statement like

update t set ... where ...

is evaluated as

1) take a consistent snapshot of the table at statement start time
2) for every row in the snapshot, 
   wait on the row if the row is locked by another transaction,
   and then modify the row if the where clause is satisfied.

I.e. any modification made by transactions started and committed 
after the statement start time (SCN) are not seen by the update statement, since they are 
not part of the "consistent snapshot".

The following lab experiment (that develops from an actual business case) has made me 
rethink about it.

We will update a big table with an update statement (transaction labeled LONG),but we 
will make it block in the middle of the table waiting for the transaction BLOCKER to 
release the lock. While LONG is waiting, we will modify the first and last row  with 
another transaction FIRSTLAST, commit it, and then rollback BLOCKER to free LONG. 
Then, we will repeat all of the above but committing BLOCKER instead of rollbacking it.

create table t (id int, y int, filler char(10) default ' ') initrans 2 nologging storage 
(initial 1M next 1m pctincrease 0 maxextents unlimited);

insert /*+ APPEND */ into t select rownum, 0, ' '  from 
all_objects,all_objects,all_objects where rownum <=1000000;
commit;

BLOCKER>  update t set y = -1 where id = 500000;

LONG>   update t set y = rownum where y = -1 or id=500000;

-- it blocks on row 500000

FIRSTLAST> update t set y = -1 where id in (1,1000000);
FIRSTLAST> commit;

BLOCKER> rollback;

LONG > select * from t where y <> 0;

        ID          Y FILLER
---------- ---------- ----------
         1         -1
    500000          1
   1000000         -1

This works as I expected; since FIRSTLAST started and committed after LONG, its 
modifications are not part of the "where clause snapshot" of LONG, and so the first and 
last rows do not get modified by LONG [ie LONG sees the old value (0) of y on the first 
and last row, even if the new value (-1) would satisfy the where clause of LONG]. So far, 
so good.

-----------------

Now we will perfom the very same experiment (resetting the table with
all the y set to zero), but now committing BLOCKER instead of rollbacking it:

update t set y = 0 where y <> 0;
commit;

BLOCKER>  update t set y = -1 where id = 500000;

LONG>   update t set y = rownum where y = -1 or id=500000;

-- it blocks on row 500000

FIRSTLAST> update t set y = -1 where id in (1,1000000);
FIRSTLAST> commit;

BLOCKER> commit; -- this is the only difference

LONG > select * from t where y <> 0;

        ID          Y FILLER
---------- ---------- ----------
         1          1
    500000          2
   1000000          3

Now, even if FIRSTLAST started and committed after LONG, and so its modifications are not 
part of the "where clause snapshot" of LONG
shot at start time, LONG actually has seen the modifications of FIRSTLAST (in addition to 
those of BLOCKER, of course).

This has been reproduced in both 8.1.7 and 9.0.1.

If the update statement of LONG had been rollbacked and then restarted 
after detecting that BLOCKER had modified a row, we would have been observed this result. 


Question a) Has Oracle really restarted the statement ? Or better, may I develop my 
software taking the stmt restart as consistency/concurrency rule, even if Oracle has made 
something different, but equivalent to a restart ?

Question b) What's the rationale for this behaviour ? I intuitively understand that it's 
in order to see all-or-none of the modifications made by BLOCKER, that is, to preserve 
the atomicity of BLOCKER ... but I would like to have this intuition confirmed, or not. 

[Side note: LONG updates y with the rownum, and assuming that the rownum is assigned to 
each row as the stmt scans them, it seems that LONG has updated the rows starting with 
id=1, then 500000, then 1000000, even if it was blocked on id=500000 and had already 
visited id=1 and had discarded it (my assumption of the actual Oracle's work in this 
case, i know that in general the order of scanning is absolutely neither predictable nor 
reproducibile). Just food for thought, no practical implications whatsoever.]

--

The lab experiment performed above was actually motivated by a real business case, since 
we need to modify, with an update stmt similar to that of LONG, a big table subject to (a 
lot of) concurrent modifications similar to the one of BLOCKER and FIRSTLAST. I may 
easily imagine other business cases in which we need to batch update a big OLTP-type 
table, subject to a lot of updates (or inserts, deletes) per seconds, 24x7. Say, "raise 
the salary of all employees around midnight, where ...". "around" means "best-effort", 
it's ok if we update as per the table image of 00:05 AM, but of course not 01:00 PM.

Assuming that the answer of question (a) is "yes", I can't simply fire the batch update 
around midnight, since the row-to-modify-set snapshot is not simply taken at stmt start, 
but actually re-evaluated each time some OLTP transactions modifies a row. 
In extreme, the stmt may be restarted forever ... and not consistently with the "around 
midnight" snapshot anyway.

Probably the best way would be to, in a nutshell (the real implementation should be more 
sophisticated, using temp tables, etc):

create table snap (row_id rowid) as select rowid row_id from t where ...

And then, update t using snap as the row-to-modify-set.

Question c) Am i being too cautious, or plainly wrong, considering such a strategy ? 


Thanks for your time and attention
Alberto

and we said...

<code>oh geez -- i'm almost afraid to publish this one. It is going to cause quite a bit of followup I believe.

The result set is consistent -- but it may well be consistent as of a restart time. We can see this with a much much smaller example -- I just used 5 rows in a controlled manner, very similar to your example.

we'll use a row trigger and an autonomous transactions to see whats going on under the covers though. Here is the setup:

ops$tkyte@ORA920> create sequence s;
Sequence created.

ops$tkyte@ORA920> create table msg1
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.

ops$tkyte@ORA920> create table msg2
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.

ops$tkyte@ORA920> create table t
2 as
3 select rownum id, 0 y
4 from all_users
5 where rownum <= 5;
Table created.

ops$tkyte@ORA920> select count(*)
2 from t;

COUNT(*)
----------
5

so, t is our 5 row table we'll do concurrent things on. msg1 will be a table we'll log the before/after image row by row transactionally. msg2 will be a table we'll do the same but using an autonomous transaction:

ops$tkyte@ORA920> create or replace procedure log_msg2( p_seq in int,
2 p_sid in int,
3 p_old_id in int,
4 p_old_y in int,
5 p_new_id in int,
6 p_new_y in int )
7 as
8 pragma autonomous_transaction;
9 begin
10 insert into msg2
11 (seq, sid, old_id, old_y, new_id, new_y )
12 values
13 (p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y );
14 commit;
15 end;
16 /
Procedure created.

ops$tkyte@ORA920> create or replace trigger t_trigger before update on t for each row
2 declare
3 l_seq number;
4 begin
5 select s.nextval into l_seq from dual;
6
7 insert into msg1
8 (seq, sid, old_id, old_y, new_id, new_y )
9 values
10 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
11
12 log_msg2
13 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
14 end;
15 /

Trigger created.

that trigger will log entries in both tables -- we'll be able to see the atrans entry immediately, we'll only see committed entries in msg1 however

ops$tkyte@ORA920> update t set y = -1 where id = 3;
1 row updated.

set the "middle" row to -1, this locks it as well of course

ops$tkyte@ORA920> set echo off
in another session issue:
update t set y = 1 where y = -1 or id = 2 or id = 3;
then come back and hit enter
ops$tkyte@ORA920> pause

I did that, it blocked of course. it updated a single row -- where id=2 and blocked on id=3

ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;

DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1

we can see it updated row id=2 -- the trigger fired. It is waiting on id=3...

ops$tkyte@ORA920> set echo off
in another session issue:
update t set y = -1 where id in ( 1,5 );
commit;
ops$tkyte@ORA920> pause

that is your "update the first and last row". I did that, that transaction went right off -- no problem:

ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;

DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1

we can see now 4 rows in our log table. seq=1 is the first update that we did and is blocking session2 over there. seq=2 is the one row that blocked session updated so far. seq=3/4 are the entries for the committed "first/last" row update

ops$tkyte@ORA920> commit;
Commit complete.

here is where it gets *very* interesting

ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;

DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1

NOT VISIBLE 5 12566 3 0 3 1

NOT VISIBLE 6 12566 1 -1 1 1
NOT VISIBLE 7 12566 2 0 2 1
NOT VISIBLE 8 12566 3 -1 3 1
NOT VISIBLE 9 12566 5 -1 5 1

9 rows selected.

hmmm -- when we committed the first thing to happen was the row with id=3 was updated -- we can see that -- seq=5 is that update. HOWEVER, that was actually rolled back (as was the update to id=2 as we'll see below further) and the update itself was restarted from the beginning. It "reupdated" id=2 and id=3 and added ids 1 and 5 to the mix (y=-1 part of the predicate picked them up).

ops$tkyte@ORA920> set echo off
commit in the formerly blocked session
ops$tkyte@ORA920> pause

ops$tkyte@ORA920> select * from t;

ID Y
---------- ----------
1 1
2 1
3 1
4 0
5 1

so we can clearly see it updated 4 rows and

ops$tkyte@ORA920> select decode(msg1.seq,null,'UNDONE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;

DECODE SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
------ ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
UNDONE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
UNDONE 5 12566 3 0 3 1
6 12566 1 -1 1 1
7 12566 2 0 2 1
8 12566 3 -1 3 1
9 12566 5 -1 5 1

9 rows selected.

now that everything is "committed" we can see the seq=2 and seq=5 were "undone" -- rolled back. It was worked performed that was "unperformed" in order to allow us to "redo it" (don't want to give that 10% raise 2 times, or 3 times or N times do you?)

so, Oracle just change the time at which your update started on you -- slid it a bit if you will.

The statement will should never be restarted forever as others will queue, blocked, behind this long running statement -- they (the ones that get blocked) will stay blocked until this long running statement compl

Reviews

very interesting thread.. August 3, 2003 - 8am Central time zone