Single-statement 'write consistency' on read committed. Oh, really?
Tengiz Kharatishvili
single-statement read committed transactions. I do realize that for a
multi-statement read committed transaction Oracle does not guarantee
transaction set consistency without techniques like select for update
or explicit hand-coded locking.
According to the documentation Oracle guarantees 'statement-level
transaction set consistency' for queries in read committed
transactions. In many cases, Oracle also provides single-statement
write consistency. However, when an update based on a consistent read
tries to overwrite changes committed by other transactions after the
statement started, it creates a write conflict. Oracle never reports
write conflicts on read committed. Instead, it automatically handles
them based on the new values for the target table columns referenced
by the update.
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. I'm simply
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:
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.
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.
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.
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?
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?
Michael J. Moore
educational.
Mike
Jonathan Lewis
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" <ten...@Hotmail.com> wrote in message
news:3fcc40e7.03112...@posting.google.com...
>
> 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.
> read committed transaction in session 2, are inconsistent - the second
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.
VC
A very interesting question ...
1. I think your summary is correct. Oracle does _not_ restart a
transaction when a new set of rows satisfying the predicate P is a superset
of the original set of rows : {rows_before_update|P(rows)} <=
{rows_after_update|P(rows)}. Otherwise, a restart occurs.
2. I've been familiar with this behaviour for quite a while but no one,
including Oracle employees, was able to explain (rationalize) the phenomenon
in a satisfactory manner. I've read carefully the relevant thread at Mr.
Kyte's website but could not find any explanation there either. It's
unclear and even confusing why Oracle bothers to restart the transaction at
all -- if the transaction were not restarted, the READ COMMITTED IL would
not be violated anyway as the original poster stated.
3.
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:bqdb6b$3dl$1$8300...@news.demon.co.uk...
>
> The issue is known, and has been discussed
[...skipped...]
So Oracle has gone
> for the approach:
> update
> if we find a problem
> rollback
> select for update
> update
> end if;
Let's call the transaction experiencing restarts a 'big' update transaction:
I do not think Oracle performs a 'select for update' after the 'rollback' in
your pseudo-code since the fact of the matter is that multiple concurrent
transactions can and often do cause multiple restarts to happen in the 'big'
update transaction. Had the internal 'select for update' been executed,
the 'big' update transaction wouldn't have needed to restart multiple times
as any concurrent intervenening transactions would've been blocked by such
'select for update'.
Rgds.
Tengiz Kharatishvili
> (for example on Tom Kyte's website).
Yes, there is a mentioning of the fact on the website, as well as a
couple vague paragraphs in his book. However, I don't find either
satisfactory.
> 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.
>
Yes, I agree that a normalized design would formally fall into the
category mentioned in the docs (an update with a subquery) and
formally there would be no issue at all. But the problem is that the
transaction isolation concepts have nothing to do with the relational
theory. And bad application design doesn't necessarily mean that it's
automatically incorrect.
If there is a committed database state (A) being transformed into
state (B) and there also is a concurrent transaction (T) that after
the state (B) is committed into the database finally leaves behind
state (C) that could not be possibly produced from either (A) or (B) -
then I have an isolation problem, without regard to whether good or
bad the app design is.
In my example, the following is the state (A):
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -2
2 0 2
3 0 0 26-NOV-03
State (B):
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -3
2 0 2
3 0 1
Transaction (T):
update accounts
set amount = amount + pending, pending = 0, cleared = sysdate
where cleared is null;
State (C):
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 7 0 26-NOV-03
2 2 0 26-NOV-03
3 0 1
Forget the creepy business case behind the example. The state (C)
should not be produced from either (A) or (B) by (T) in a serial
execution.
> 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.
>
I disagree – any reasonable implemented locking scheduler with
row-level locking would produce the correct results with no
table-level locks. I do know it for sure.
> 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.
>
Yes, you're right.
> Surely you don't expect a serious answer to these (very sensible)
> questions.
>
Right again, I wouldn't expect them to document these things, however,
what I do expect is them to honor the fundamental promise they make.
The statement-level write consistency should either be guaranteed or
not. You cannot be possibly happy with this 'it kind of works'
behaviour with no clear explanation when exactly it's supposed to
work.
> 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;
Agree, that's basically what I've deduced from my experiments too.
Except, it doesn't have to be the 'select for update' before the
'update' that retries - in case you're willing to tolerate multiple
restarts.
> 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.
>
Precisely - I believe that it's either a documentation bug (they
should not claim that Oracle provides a 'single-statement write
consistency' without specifying the exact circumstances when it really
does) or a defect in the write conflict handling code - a namely, the
'optimization' is too aggressive. Moreover, I believe they're very
well aware of the problem, but they won't fix it because of some
non-technical reasons.
And finally - I must confess: I recently tried to post the original
detailed question on Tom's website, but he essentially refused to
answer and did not allow the question and his reply for public view.
So the whole thread was a bit of a provocation. I hope it was
educational for some of us though.
APC
>> And finally - I must confess: I recently tried to post the
original
>> detailed question on Tom's website, but he essentially refused to
>> answer and did not allow the question and his reply for public
view.
Really? Tom Kyte censored your posting? I must admit, I am shocked.
However, I think the behaviour you describe may no longer be an issue.
I think we want it to always be true that Oracle re-starts filter out
rows from the original resultset that no longer match but always
ignore new rows that now match. We may prefer some other behaviour
but it's more important to know where we stand. Guaranteeing
consistent behaviour is the bedrock of our industry.
What concerned me in in your original posting was the way some
variations on the Session 2 query pick up new rows after Session 1
commits:
>> update accounts
>> set amount = amount + pending, pending = 0, cleared = sysdate
>> where cleared is null and pending <> 0;
This statement updates only two rows if issued before Session 1
updates the table but updates _three_ rows if issued in parallel but
before Session 1 has committed. In other words, adding "and pending
<> 0" to the statement changes the result, for no apparent reason. I
was able to reproduce this behaviour on Oracle 9.0.1 for Solaris,
after reading your OTN posting.
This morning, having followed Mark Powell's link to here I tried it
again, this time on Oracle 9.2 for Windows but I could not reproduce
the results. On this install adding "and pending <> 0" does not
change the resultset: Session 2 still only updates _two_ rows.
So, this looks to me to be either a fixed bug (incidentally, why did
you say you thought Oracle didn't want to fix it, "for non-technical
reasons"?) or it's one of those funnies we need to add to our new
installation testing framework.
Cheers, APC
Tengiz Kharatishvili
> ten...@Hotmail.com (Tengiz Kharatishvili) wrote in message news:<3fcc40e7.03120...@posting.google.com>...
>
I wouldn't call it 'censorship' - they never promised to publish every
single question they get. Which makes perfect sense, otherwise Tom's
site could be flooded with trivial/recurring stuff. So, according to
the openly published policy they pre-select questions based on how
interesting the question and the answer would be for the public.
Obviously, they didn't find my original post and the following answers
worth publishing. And I can understand why – for the vast majority of
the Oracle developers there is no issue at all. It really takes a
focused conscious effort to come up with somewhat exotic yet perfectly
valid scenario that shows certain isolation anomalies (permitted by
ANSI read committed anyway).
> However, I think the behaviour you describe may no longer be an issue.
> …
> This morning, having followed Mark Powell's link to here I tried it
> again, this time on Oracle 9.2 for Windows but I could not reproduce
> the results. On this install adding "and pending <> 0" does not
> change the resultset: Session 2 still only updates _two_ rows.
>
Thanks, that's good to know that there's one less issue – this
certainly makes it slightly more predictable.
> So, this looks to me to be either a fixed bug (incidentally, why did
> you say you thought Oracle didn't want to fix it, "for non-technical
> reasons"?) or it's one of those funnies we need to add to our new
> installation testing framework.
>
Well, sorry if it appeared confusing, but let me rephrase the bit
about 'not fixing it for non-technical reasons': the real fix on my
opinion would be either better documented restart on conflicts
behavior [fix the docs] or making sure that any single-statement (with
no sub-queries) read committed transaction always produces consistent
results [fix the update conflict handling code].
But the truth is that fixing the code might in fact cause more
problems – there's hundreds of thousands already written applications
(including critical and important benchmarks) that might perform
slower because of more restarts-behind-the-scene on write conflicts
(let alone the fact that some unfortunate poorly designed apps might
even produce incorrect results.)
Would it be a clear win? Sure, it would make purists like myself
happier, but Oracle has more concerns than that. So, I believe that
they simply act cautiously and responsibly – that's why I said
'non-technical' reasons. But on 'technical' side - they know precisely
what to do and how to fix it – Oracle has a world class dev team.
However, as I said before - I would love them to document the current
behavior better.
VC
I apologize for a possibly incorrect follow-up attribution -- my newsreader
is acting up.
You wrote:
> andrew...@logicacmg.com (APC) wrote in message
news:<58428d02.03120...@posting.google.com>...
> ten...@Hotmail.com (Tengiz Kharatishvili) wrote in message
news:<3fcc40e7.03120...@posting.google.com>...
> Really? Tom Kyte censored your posting? I must admit, I am shocked.
> However, I think the behaviour you describe may no longer be an issue.
> .
> This morning, having followed Mark Powell's link to here I tried it
> again, this time on Oracle 9.2 for Windows but I could not reproduce
> the results. On this install adding "and pending <> 0" does not
> change the resultset: Session 2 still only updates _two_ rows.
>
I re-produced the original poster's results on my Windows Oracle 9.2.0.4
installation (scenario 2 where all three rows are updated as opposed to the
two-rows-update in scenario 1):
Session 1:
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 2 20:22:20 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> drop table accounts;
Table dropped.
SQL> create table accounts (
2 acc int primary key,
3 amount int,
4 pending int,
5 cleared date
6 );
Table created.
SQL>
SQL> insert into accounts values(1,10,-2,null);
1 row created.
SQL> insert into accounts values(2,0,2,null);
1 row created.
SQL> insert into accounts values(3,0,0,sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> update accounts
2 set pending = pending - 1, cleared = null where acc = 1;
1 row updated.
SQL>
SQL> update accounts
2 set pending = pending + 1, cleared = null where acc = 3;
1 row updated.
SQL>
SQL> select * from accounts;
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -3
2 0 2
3 0 1
In Session 2:
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 2 20:22:31 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> update accounts
2 set amount = amount + pending, pending = 0, cleared = sysdate
3 where cleared is null and pending <> 0;
*** Blocked by Session 1 ****
In Session 1:
SQL> commit;
Commit complete.
SQL>
In Session 2:
3 rows updated.
SQL> select * from accounts;
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 7 0 02-DEC-03
2 2 0 02-DEC-03
3 1 0 02-DEC-03
SQL>
As you can see all the three rows have been updated. The reason for
Oracle's change of mind is the 'pending <> 0' predicate presence in Scenario
2. Since the original set of rows is no longer a subset of the new set of
rows satisfying the predicate Oracle has to rollback and restart, thereby
getting an opportunity to update all the three rows. I've been able to
reproduce this sort of behaviour under Unix (Linux, Solaris, AIX) as well
as under Windows.
Please try running the experiment again and share your findings since, if
your results are correct, it would appear that Oracle's behaviour is even
more inconsistent than I'd thought before.
Thank you.
Rgds.
VC
I apologize for a possibly incorrect follow-up attribution -- my newsreader
is acting up.
You wrote:
> andrew...@logicacmg.com (APC) wrote in message
news:<58428d02.03120...@posting.google.com>...
> ten...@Hotmail.com (Tengiz Kharatishvili) wrote in message
news:<3fcc40e7.03120...@posting.google.com>...
> Really? Tom Kyte censored your posting? I must admit, I am shocked.
> However, I think the behaviour you describe may no longer be an issue.
> .
> This morning, having followed Mark Powell's link to here I tried it
> again, this time on Oracle 9.2 for Windows but I could not reproduce
> the results. On this install adding "and pending <> 0" does not
> change the resultset: Session 2 still only updates _two_ rows.
>
I re-produced the original poster's results on my Windows Oracle 9.2.0.4
Session 1:
SQL> drop table accounts;
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
SQL> commit;
Commit complete.
1 row updated.
1 row updated.
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -3
2 0 2
3 0 1
In Session 2:
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 2 20:22:31 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> update accounts
2 set amount = amount + pending, pending = 0, cleared = sysdate
3 where cleared is null and pending <> 0;
*** Blocked by Session 1 ****
In Session 1:
SQL> commit;
Commit complete.
SQL>
In Session 2:
3 rows updated.
SQL> select * from accounts;
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
APC
of SQL than Tengiz used, which explains my different results on the
9.2 setup. Having re-done the tests using Tengiz's actual code I have
duplicated his results.
However, I now understand that this is the Oracle is supposed to work,
and the results are *not* inconsistent.
In his first example Session 2 runs this statement:
SQL> update accounts
2 set amount = amount + pending, pending = 0, cleared = sysdate
3 where cleared is null;
when this becomes unblocked it updates two rows. In his alternative
scenario, this statement:
SQL> update accounts
2 set amount = amount + pending, pending = 0, cleared = sysdate
3 where cleared is null and pending <> 0;
updates three rows. Why the difference?
Because the statements run in Session 1 change the value of PENDING
for acc #1, which is in the session 2 resultset. When PENDING is in
the WHERE clause this change causes a re-start because Oracle realises
it needs to re-evaluate the predicates. When the WHERE clause simply
uses CLEARED there is no re-start, because the value of CLEARED has
not changed for acc #1. True, it has changed for acc #3, but acc #3
is not in the existing session 2 resultset, so no re-evaluation of the
predicate is required.
This is both consistent and sensible. Sensible because the
alternative is to force a re-start for every blocked statement, which
I don't think is desirable. Consistent because, well, consider this
alternative scenario:
SQL> DROP TABLE accounts;
Table dropped.
SQL> CREATE TABLE accounts (
2 acc number PRIMARY KEY,
3 amount number,
4 pending number,
5 cleared date
6 );
Table created.
SQL> INSERT INTO accounts VALUES (1,10,-2,sysdate);
1 row created.
SQL> INSERT INTO accounts VALUES (2,0,2,null);
1 row created.
SQL> INSERT INTO accounts VALUES (3,0,0,sysdate);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> UPDATE accounts
2 SET pending = pending - 1, cleared = NULL WHERE acc = 1;
1 row updated.
SQL> UPDATE accounts
2 SET pending = pending - 1, cleared = SYSDATE WHERE acc = 2;
1 row updated.
SQL> UPDATE accounts
2 SET pending = pending + 1, cleared = NULL WHERE acc = 3;
1 row updated.
SQL> SELECT * FROM accounts;
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 10 -3
2 0 1 03-DEC-03
3 0 1
-- over to Session 2:
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 3 10:02:41 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> UPDATE accounts
2 SET amount = amount + pending, pending = 0, cleared = sysdate
3 WHERE cleared IS NULL;
-- we are blocked by Session 1
-- back to Session 1
-- can you guess what's going to happen in Session 2???
SQL> COMMIT;
Commit complete.
SQL>
-- ta-da!
2 rows updated.
SQL> SELECT * FROM accounts;
ACC AMOUNT PENDING CLEARED
---------- ---------- ---------- ---------
1 7 0 03-DEC-03
2 0 0 03-DEC-03
3 1 0 03-DEC-03
SQL>
Did you guess right?
Cheers, APC
P.S. I wish to withdraw my use of the word "censor" in regards to Tom
Kyte's publication policy. Tom has to choose which posts to publish
and which to not. I'm not saying whether I think he was right not to
publish Tengiz's post on AskTom - I don't know its contents, but I
doubt there was anything sinister underlying his decision.
VC
Please see my comments below.
"APC" <andrew...@logicacmg.com <mailto:andrew...@logicacmg.com>>
wrote in message
<news:58428d02.03120...@posting.google.com>...
> Following from previous posting: I was using a slightly different set
> of SQL than Tengiz used, which explains my different results on the
> 9.2 setup. Having re-done the tests using Tengiz's actual code I have
> duplicated his results.
I am glad that Oracle _is_ behaving consistently at least with respect to
executing scenario 2 on different installations.
Thank you for re-running the test.
>
> However, I now understand that this is the Oracle is supposed to work,
> and the results are *not* inconsistent.
>
Well, I am not so sure.
Firstly, regarding your test, it's easy enough to predict its outcome iff
one knows about the Oracle re-start pecularity:
a. Session 2's selects Row 2 for the update as satisfying the predicate;
b. Session 2 is blocked by Session 1's update;
c. Session 1 commits;
d. Session 2 sees that row 2 no longer satisfies the predicate and restarts
thereby getting two new rows (1 and 3) instead of the original row 2 as
satisfying the predicate;
e. Session 2 updates Rows 1 and 3.
Now why I am not happy about the observed behaviour. Briefly, the reasons
are as follows:
1. As I mentioned earlier, why bother with the restart at all since the READ
COMMITTED IL
promise would not be broken, restart or no restart. What's the rationale for
the restarts ?
2. Oracle's behaviour in scenarios 2 and 3, as well as in your example, is
not intuitive. For example, in scenario 3, the second predicate
( ' (pending * 0) = 0' ) will always evaluate to 'true' assuming 'pending
not null'. If the CBO were smart enough, it would exclude the predicate
and then the re-scan would not happen thus producing a different result.
3. The behaviour is not described anywhere in Oracle documentation.
<...skipped...>
Rgds.
APC
> COMMITTED IL
> promise would not be broken, restart or no restart. What's the rationale for
> the restarts ?
Because the alternative is to process rows that no longer meet the
criteria.
> 2. Oracle's behaviour in scenarios 2 and 3, as well as in your example, is
> not intuitive. For example, in scenario 3, the second predicate
> ( ' (pending * 0) = 0' ) will always evaluate to 'true' assuming 'pending
> not null'. If the CBO were smart enough, it would exclude the predicate
> and then the re-scan would not happen thus producing a different result.
The CBO is not smart. It is dumb. It's a computer: all computers are
dumb. The CBO implements heuristics. What you are suggesting is that
the CBO ought to recognise patterns, and under certain circumstances
disregard those rules. Well, that sounds like a lot of complicated
programming to me, a certain amount of overhead and a whole lot of
grief.
Besides, if Session had set PENDING=NULL then (pending * 0) = 0' would
not evaluate to TRUE.
> 3. The behaviour is not described anywhere in Oracle documentation.
>
You got me there.
Cheers, APC
VC
"APC" <andrew...@logicacmg.com> wrote in message
news:58428d02.0312...@posting.google.com...
> > 1. As I mentioned earlier, why bother with the restart at all since the
READ
> > COMMITTED IL
> > promise would not be broken, restart or no restart. What's the rationale
for
> > the restarts ?
>
>
> Because the alternative is to process rows that no longer meet the
> criteria.
So what ? The READ COMMITTED IL is not violated -- it did not promise
repeatable reads. Why bother about this specific anomaly whilst ignoring
others, like ignoring new rows satisfying the predicate? They would not
be processed either.
> the CBO ought to recognise patterns, and under certain circumstances
> disregard those rules. Well, that sounds like a lot of complicated
> programming to me, a certain amount of overhead and a whole lot of
> grief.
>
> Besides, if Session had set PENDING=NULL then (pending * 0) = 0' would
> not evaluate to TRUE.
Well, in my previous posting I said assuming the column is defined as NOT
NULL.
>
> > 3. The behaviour is not described anywhere in Oracle documentation.
> >
>
> You got me there.
>
> Cheers, APC
Brgds.