Re: spontaneous rollback and retry?

I haven't tested in parallel yet, so may be wrong, but I think what you're seeing is Oracle trying to do a consistent update.

Consider the scenario:

    4 rows in table with value 'X'
    Session 1 does:

        update table set 'X' to 'Y';

    Session 2 manages to do

        update table 'X' to 'Y' in one row;
        commit;

But session 2 does it's commit BEFORE
session 1 gets to that one row.

When session 1 gets to the row, it has a problem - to be "read-consistent" with
the start of transaction. The update ought to see the original 'X' - so should it change the row that is now a 'Y', or should it crash, or ignore it, or what ?

What Oracles does is

    rollback the update
    restart with a 'select for update'
    when the select for update completes     update all rows selected.

    Moreover, if the select for update
    also hits problems with other updates     that start after the select, and commit     before the select gets to the changed     row, there is another (expensive) effect     that appears - though I haven't worked     out exactly what is happening, it's a bit     like the select for update restarts without     rolling back.

In a serial scenario, the recorded scnb in v$transaction doesn't change (which looks like an error, given what happens) but in a parallel update, I guess it's the parent scnb that may be locked, and the child (PX)
scnb's that tell you when the select for update restarts.

In a serial update, the 'solution' to the collisions would be to lock the table
to stop other sessions updating it -
but I'm not sure you will be able to do
that in parallel update, because you
might deadlock your own PX slaves
(to be tested).

Note - if you tried to 'set transaction serializable' in this scenario which (in principle) is what you want, at the point where the spontaneous restart occurs, you would get the "unable to serialize transaction" error.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Richard Kuhler" <noone_at_nowhere.com> wrote in message
news:AU0Lb.48871$nG3.25381_at_twister.socal.rr.com...


> I'm trying to make sense of what I'm seeing for a process that's issuing

> a parallel merge statement.  For about the first 45 minutes I see the

> undo blocks allocated rising (v$transaction.used_ublk).  Then the number

> slow goes back down to zero over the next 45 minutes.  Both "transaction

> rollbacks" and "rollback changes - undo records applied" statistic goes

> up as well.  At that point, Oracle seems to be stopping all the parallel

> sessions, starting a new transaction (v$transaction.start_scnb changes)

> and creating new parallel sessions.  Then undo allocation starts to go

> back up again.  It's been doing this for several hours longer than this

> process should really take.  This process is not in any sort of loop

> however so it appears that Oracle is doing all of these "retries" on

> it's own.

>

> What exactly is Oracle doing here?

>

>

> Thanks,

> Richard Kuhler

>