Re: Question regarding read consistency in Tom Kyte's Expert 10g book

In article <1115315656.138585.193960_at_o13g2000cwo.googlegroups.com>, georgejzhang_at_gmail.com says...
>
>The first 2 chapters (pdf) is online at the link above.
>
>Anyway, here is the example:
>
>Table accounts is created as:
>
>create table accounts
> ( account_number number primary key,
> account_balance number
>);
>
>There are 4 rows in it:
>Row Account Number Account Balance
>1 123 $500.00
>2 234 $250.00
>3 345 $400.00
>4 456 $100.00
>
>Suppose you are running a query to get the total of balance:
>
>select sum(account_balance) from accounts;
>
>While the query is reading row 2 and 3, a transaction come in and moves
>$400 from account 123 to 456. What will be your total?
>
>Tom explained in more detail about it:
>
>Time Query Account transfer transaction
>T1 Reads row 1, sum = $500 so far
>T2 Updates row 1, puts an exclusive lock on row 1 preventing other
>updates. Row 1 now has $100
>T3 Reads row 2, sum = $750 so far
>T4 Reads row 3, sum = $1150 so far
>T5 Updates row 4, puts an exclusive lock on block 4 preventing other
>updates (but not reads). Row 4 now has $500.
>T6 Reads row 4, discovers that row 4 has been modified. It will
>actually rollback the block to
>make it appear as it did at time = T1. The query will read the value
>$100 from this block
>T7 Commits transaction
>T8 Presents $1250 as the answer
>
>--------------------------------------------------------------------------------------------
>My question is, what if T6 and T7 interchange their timing position?

As long as T7 is AFTER T1 -- the results are identical (assume T1 is the open of the query)

In Oracle, the results to be returned by your are "as of" a point in time.

By default that point in time is when you OPEN the result set (eg: open a query at 7am, but don't fetch. At 10am come back and fetch a row -- it'll be whatever was in the database at 7am)

You can make that point in time be "as of the start of your transaction" using READ ONLY or SERIALIZABLE isolation.

And in 9i, you can make the "as of" time be some arbitrary point in time using FLASHBACK query.

So, in the above, as long as T7 happens AFTER you open the cursor -- assuming read committed isolation (default), the results would be consistently the same.

>

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation