| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Snapshot problem: Update in cursor
On Tue, 15 Jul 1997 18:12:53 GMT, dirk_at_news.drb.insel.de (Dirk Straka) wrote:
>
>Hi!
>
>We've spotted a problem while migrating our application from Informix
>Online to Oracle. Scenario is as follows:
>
> - Starting transaction
> - Opening a cursor which will get 5 rows (for example), these may
> be row 1, which contains a 10, row 2, containing a 20 and so on
> up to row 5 which contains a 50.
> - After fetching 2 rows, one after the other, we perform a select
> of row 4, modify its value from 40 to 42 and write it back using
> update - _no_ commit! (so we're not talking about updates across
> commit; the modified value is neither part of the where clause
> nor an index column)
> - Next fetch ...
> - Sooner or later row 4 is fetched by the cursor and, surprise,
> surprise, it contains the initial value 40 instead of the lately
> written 42!
>
>This is not the behavior we've expected. And it doesn't meet the
>way Informix Online reacts - it gives the IMO "correct" value 42.
>
>Oracle support says everything's fine. This is their understanding of
>consistency (long explanation of System Change Numbers and Fetches
>Across Commit snipped - because there are no commits!!!).
>
This is called Multi-versioning, Read Consistency. The instant you open a cursor, the result set is pre-ordained. Before any IO is done, before the tables are accessed, the answer is set in stone. This is the way it works in Oracle.
>Our understanding of consistency is a different one - the one which
>Informix shares (and the one, which complies common sense, we
>suppose). ;-)
>
common sense is in the eye of the beholder I guess. Informix's answer will be different under different circumstances (the behaviour you noticed above not gauranteed, there are very clear cases when in Informix you would see 40, not 42 as well).
The behaviour you see in Informix is one of the reasons they can't do something like:
create table documents ( doc_id, doc_version, document );
insert into documents values ( 1, 1, 'Hello world' ); commit;
insert into documents
select doc_id, doc_version+1, document
from documents
where doc_id = 1
and doc_version = 1;
for example (something quite easily done in Oracle). The fact that Informix doens't support consistent reads is one of the reasons they cannot read/write from a table in a single statement. If they allow something like the above syntax, what effect would:
insert into foo select * from foo;
have? Could be an infinite loop.
What about this:
for x in ( select * from T ) loop
x.c1 := f( x.c1 ); -- modify x.c1 and get a new value
insert into T values ( x.c1, x.c2, x.c3, ...., x.c4 );
end loop;
Would this be another new way to write an infinite loop? Maybe, maybe not. In Informix it could be, in Oracle no. Should the fetch cursor see the new data? If so, should it see all of the new data? If you consider the above as reading from the 'beginning' of the table to the end, what if lots of free space was at the beginning, some of the inserts would go to the 'front' of the table, some at the end. Some we would see, some we would not. running the above loop against the same set of data could and would return different answers depending on the physical organization of data if we read data that didn't exist when the select was opened.
also, you got 'lucky' with your Informix example. If you had used a cursor that forced a sort or aggragation, they would have written the answer to a temp segment. Your select cursor would read the temp segment -- NOT the real table. You would not see the changes in that case. Add an order by to the select that is not an indexed column, this will force them to build the result set and order it somewhere. They will read from this temporary result set -- not the base table -- and you won't see your changes. Consistency? nope.
>Do you have similar experiences, problems or maybe a clue or trick
>to solve this problem? Maybe it's possible to refresh the snapshot
>which was taken at the start of the cursor to get the changes I've
>made (but not those, other transactions have made)? Any suggestions?
>
Actually, in Informix, you will see changes other transactions have made and committed. As you are scrolling through your cursor, you might hit a row that you updated way in the beginning and 5 other people have likewise modified since.
this is a really bad feature of the default informix isolation mode (committed read). For example, consider a 1000 row table. Lets say it represents accounts and balances. Every account has 100 dollars in the bank. So, there is 100,000 dollars all together. I ask the question "how much money is in the bank?". A simple "select sum(bal) from accounts".
Meanwhile, a customer performs a transaction. They transfer 50 dollars from their savings to checkings account. Assume that this customers savings account is 'row 1' and their checking account is 'row 1000'.
I start my question, I read row 1, row 2, ..... and so on.
the customer does their transaction. changes row 1 (i've already read) to 50 dollars and row 1000 (which I haven't) to 150 dollars. They commit.
I read row 999, 1000. Opps. The balance I see is 100,050 dollars. A value that never actually existed in the database ever. I read one of their changes but not the other.
Oracle doesn't suffer from the above (called inconsistent analysis). We would return the correct aggregation of 100,000.
>Thnx in advance.
>
>--
>Sincerely, Dirk Straka Due to email spammers, reply
> address is invalid -- Please
>Dr. Brunthaler GmbH, Berlin change NOSPAM to Insel. Thnx
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |