| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Single-statement 'write consistency' on read committed. Oh, really?
The issue is known, and has been discussed
(for example on Tom Kyte's website).
Personally I think that there may be a small
(in terms of amount of code, not in size of
implication) error in the coding here; but I'm
still working on figuring out the internals.
See notes in-line
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Tengiz Kharatishvili" <tengizk_at_Hotmail.com> wrote in message news:3fcc40e7.0311291721.46084513_at_posting.google.com...Received on Sun Nov 30 2003 - 11:59:04 CST
>
> Let's consider a simple example. Again, I do realize that the
> following design might look strange or even sloppy, but the ability to
> produce a quality design when needed is not an issue here.
Unfortunately it is part of the issue - although your example has produced an unexpected result, it has done so because of an appalling design which is inconsistent with the 'careful developer' that you cite at the end of your note. In particular, the Concepts manual warns specifically that DML that uses subqueries should use serializable transactions - but your design has denormalised the data so that you are doing an update which should have been from a subquery, but using summary data that has been written to the table, thus bypassing Oracle's warning.
> trying to understand the Oracle's behavior on write conflicts in a
> single-statement read committed transaction.
>
> A valid business case behind the example is rather common - a
> financial institution with two-stage funds transfer processing. First,
> you submit a transfer (put transfer amounts in the 'pending' column of
> the account) in case the whole financial transaction is in doubt.
> Second, after you got all the necessary confirmations you clear all
> the pending transfers making the corresponding account balance
> changes, resetting pending amount and marking the accounts cleared by
> setting the cleared date. Neither stage should leave the data in
> inconsistent state: sum (amount) for all rows should not change and
> the sum (pending) for all rows should always be 0 on either stage:
>
But your design allows you to add an unconfirmed transfer to the balance by virtue of the fact that (a) you are applying a pre-calculated summary rather than using base data to apply the confirmed transfers and (b) you are allowing new transfers to be created and added to the summaries whilst the summaries are being transfered. It is possible to make any commercial database produce unexpected results by writing sufficiently bad code - especially code that is designed to exploit specific features of the implementation. I can find several ways of making Oracle 'do things wrong' - because I know how it works, and I know how to break the rules in ways which don't look as if I'm trying to break the rules. Conversely, I've just had a conversation with someone who was very surprised that a piece of code didn't work on Oracle when it worked with {insert long list of other vendors here} - the reason it "didn't work" on Oracle was that it produced the right answer on Oracle and the wrong answer for every other listed vendor.
> Setup:
>
> create table accounts
> (
> acc int primary key,
> amount int,
> pending int,
> cleared date
> );
>
> Initially the table contains the following:
>
> ACC AMOUNT PENDING CLEARED
> ---------- ---------- ---------- ---------
> 1 10 -2
> 2 0 2
> 3 0 0 26-NOV-03
>
> So, there is a committed database state with a pending funds transfer
> of 2 dollars from acc 1 to acc 2. Let's submit another transfer of 1
> dollar from acc 1 to acc 3 but do not commit it yet in SQL*Plus
> Session 1:
>
> update accounts
> set pending = pending - 1, cleared = null where acc = 1;
>
> update accounts
> set pending = pending + 1, cleared = null where acc = 3;
>
> ACC AMOUNT PENDING CLEARED
> ---------- ---------- ---------- ---------
> 1 10 -3
> 2 0 2
> 3 0 1
>
> And now let's clear all the pending transfers in SQL*Plus Session 2 in
> a single-statement read-committed transaction:
>
> update accounts
> set amount = amount + pending, pending = 0, cleared = sysdate
> where cleared is null;
>
> Session 2 naturally blocks. Now commit the transaction in session 1.
> Session 2 readily unblocks:
>
> ACC AMOUNT PENDING CLEARED
> ---------- ---------- ---------- ---------
> 1 7 0 26-NOV-03
> 2 2 0 26-NOV-03
> 3 0 1
>
> Here we go - the results produced by a single-statement transaction
> read committed transaction in session 2, are inconsistent - the second
> funds transfer has not completed in full. Session 2 should have
> produced the following instead:
>
> ACC AMOUNT PENDING CLEARED
> ---------- ---------- ---------- ---------
> 1 7 0 26-NOV-03
> 2 2 0 26-NOV-03
> 3 1 0 26-NOV-03
>
> Please note that we would have gotten the correct results if we ran
> the transactions in session 1 and session 2 serially. Please also note
> that no update has been lost. The type of isolation anomaly observed
> is usually referred to as a 'read skew', which is a variation of
> 'fuzzy read' a.k.a. 'non-repeatable read'.
>
> But if in the session 2 instead of:
> -- scenario 1
> update accounts
> set amount = amount + pending, pending = 0, cleared = sysdate
> where cleared is null;
>
> we issued:
> -- scenario 2
> update accounts
> set amount = amount + pending, pending = 0, cleared = sysdate
> where cleared is null and pending <> 0;
>
> or even:
> -- scenario 3
> update accounts
> set amount = amount + pending, pending = 0, cleared = sysdate
> where cleared is null and (pending * 0) = 0;
>
> We'd have gotten what we really wanted.
>
> I'm very well aware of the 'select for update' or serializable il
> solution for the problem. Also, I could present a working example for
> precisely the above scenario for a major database product, providing
> the results that I would consider to be correct. That is, the
> interleaving execution of the transactions has the same effect as if
> they completed serially. Naturally, no extra hand-coded locking
> techniques like select for update or explicit locking is involved.
>
I think I could find a couple of major vendors who would also get your correct result. I think they both might lock the entire table to do so - against reads and writes.
> And now let's try to understand what just has happened. Playing around
> with similar trivial scenarios one could easily figure out that Oracle
> clearly employs different strategies when handling update conflicts
> based on the new values for the target table columns, referenced by
> the update. I have observed the following cases:
>
> A. The column values have not changed: Oracle simply resumes using the
> current version of the row. It's perfectly fine because the database
> view presented to the statement (and hence the final state of the
> database after the update) is no different from what would have been
> presented if there had been no conflict at all.
>
> B. The row (including the columns being updated) has changed, but the
> predicate columns haven't (see scenario 1): Oracle resumes using the
> current version of the row. Formally, this is acceptable too as the
> ANSI read committed by definition is prone to certain anomalies anyway
> (including the instance of a 'read skew' we've just observed) and
> leaving behind somewhat inconsistent data can be tolerated as long as
> the isolation level permits it. But please note - this is not a
> 'single-statement write consistent' behavior.
>
> C. Predicate columns have changed (see scenario 2 or 3): Oracle rolls
> back and then restarts the statement making it look as if it did
> present a consistent view of the database to the update statement
> indeed. However, what seems confusing is that sometimes Oracle
> restarts when it isn't necessary, e.g. when new values for predicate
> columns don't change the predicate itself (scenario 3). In fact, it's
> bit more complicated - I also observed restarts on some index column
> changes, triggers and constraints change things a bit too - but for
> the sake of simplicity let's no go there yet.
>
I think your descriptions can be simplified to: Oracle restarts only if it comes to a row where the predicate columns have moved the row from being 'in-scope' to being 'out of scope'. (So no-change to predicates, and a change that moves a row from 'out of scope' to 'in-scope' is ignored.
> And here come the questions, assuming that (B) is not a bug, but the
> expected behavior:
>
> 1. Does anybody know why it's never been documented in detail when
> exactly Oracle restarts automatically on write conflicts once there
> are cases when it should restart but it won't? Many developers would
> hesitate to depend on the feature as long as it's not 'official'.
> Hence, the lack of the information makes it virtually useless for
> critical database applications and a careful app developer would be
> forced to use either serializable isolation level or hand-coded
> locking for a single-statement transaction.
>
> If, on the other hand, it's been documented, could anybody please
> point me to the bit in the documentation that:
>
> a) Clearly states that Oracle might restart an update statement in a
> read committed transaction because otherwise it would produce
> inconsistent results.
> b) Unambiguously explains the circumstances when Oracle does restart.
> c) Gives clear and unambiguous guidelines on when Oracle doesn't
> restart and therefore when to use techniques like select for update or
> the serializable isolation level in a single-statement read committed
> transaction.
>
Surely you don't expect a serious answer to these (very sensible) questions. Have you ever seen a supplier produce a manual which describes in detail how their system works. Oracle in recent years seems to have become somewhat better than average, but most vendors seem to stop at "this is a command you can run, and here's a trivial example of using it"
> 2. Does anybody have a clue what was the motivation for this peculiar
> design choice of restarting for a certain subset of write conflicts
> only? What was so special about them? Since (B) is acceptable for read
> committed, then why Oracle bothers with automatic restarts in (C) at
> all?
>
My opinion, still subject to fiddling around a bit more, is that this is an example of optimistic locking gone wrong. Clearly Oracle could do: 'select for update' / 'update' as an internal response to an update command. This would avoid the issue, but at a cost which would be unnecessary in an extremely high proportion of cases. So Oracle has gone for the approach: update if we find a problem rollback select for update update end if; However, my feeling is that the code that does the 'select for update' bit has a cunning optimisation trick built in that goes for the wrong SCN. Having said that, though, my experience is that the issue only reveals itself in systems that have not been designed carefully anyway - and such systems have far more serious errors built in.
> 3. If, on the other hand, Oracle envisions the statement-level write
> consistency as an important advantage over other mainstream DBMSs as
> it clear from the handling of (C), does anybody have any idea why
> Oracle wouldn't fix (B) using well-known techniques and always produce
> consistent results?
 |
 |