Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Single-statement 'write consistency' on read committed. Oh, really?

Re: Single-statement 'write consistency' on read committed. Oh, really?

From: VC <boston103_at_hotmail.com>
Date: Mon, 01 Dec 2003 03:07:24 GMT
Message-ID: <Mfyyb.376109$Fm2.392173@attbi_s04>


Hello Jonathan,

A very interesting question ...

  1. I think your summary is correct. Oracle does _not_ restart a transaction when a new set of rows satisfying the predicate P is a superset of the original set of rows : {rows_before_update|P(rows)} <= {rows_after_update|P(rows)}. Otherwise, a restart occurs.
  2. I've been familiar with this behaviour for quite a while but no one, including Oracle employees, was able to explain (rationalize) the phenomenon in a satisfactory manner. I've read carefully the relevant thread at Mr. Kyte's website but could not find any explanation there either. It's unclear and even confusing why Oracle bothers to restart the transaction at all -- if the transaction were not restarted, the READ COMMITTED IL would not be violated anyway as the original poster stated.

3.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bqdb6b$3dl$1$8300dec7_at_news.demon.co.uk...
>
> The issue is known, and has been discussed
[...skipped...]

  So Oracle has gone
> for the approach:
> update
> if we find a problem
> rollback
> select for update
> update
> end if;

Let's call the transaction experiencing restarts a 'big' update transaction: I do not think Oracle performs a 'select for update' after the 'rollback' in your pseudo-code since the fact of the matter is that multiple concurrent transactions can and often do cause multiple restarts to happen in the 'big' update transaction. Had the internal 'select for update' been executed, the 'big' update transaction wouldn't have needed to restart multiple times as any concurrent intervenening transactions would've been blocked by such 'select for update'.

Rgds. Received on Sun Nov 30 2003 - 21:07:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US