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: Question regarding read consistency in Tom Kyte's Expert 10g book

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

From: <georgejzhang_at_gmail.com>
Date: 5 May 2005 10:54:16 -0700
Message-ID: <1115315656.138585.193960@o13g2000cwo.googlegroups.com>


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? Received on Thu May 05 2005 - 12:54:16 CDT

Original text of this message

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