Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Question regarding read consistency in Tom Kyte's Expert 10g book
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