tengiz wrote:...But one question still remains - the key didn't really change (pk = pk), why even bother to check whether the detail row existed?
This, I cannot answer, I do not know why Oracle decided not to optimize it
tengiz wrote:vc, any type of write activity (insert/update/delete) has to be presented with some database state which then is going to be transformed. No persistent data can be written based on anything but the previous database state, the application logic and some external data - and let me repeat: there must be only one observed database state for queries and for updates. Otherwise the following two statements could have produced different data:
Code: Select all
update tab set col1 = (select col2 from tab key = 123) where t.key = 123
update tab set col1 = col2 key = 123
Can you show me a scenario when it really is true for any mainstream DBMS? Would you consider it acceptable for a reasonably implemented database engine? Would you then agree that it would essentially forbid virtually any type of otherwise equivalent query transfomations?
Let's not over-dramatize the situation. Any 'mainstream' implementation is a crude hack of the original relational model but that's the best we can have. Of course, it would be nice to have serializable schedules in each and every case but the grim reality is such that, today, for performance reasons, it's simply impossible, and one has to do one's best with the crap like various isolation levels.
If you discovered other flaws in the Oracle implementation of the RCIL above and beyond phantoms and non-repeatables, I'll be happy to discuss them.
tengiz wrote:Anyway, what are you trying to say is that the view of the database sometimes might be different for queries and updates. Yes, I know it from the recent and quite surprizing experience with Oracle, but I found nothing in the documentation which confirms it. Please find me any peace of information which says otherwise.
I am surprised that you have been surprised. The phenomenon we have been observing is nothing more but a manifestation of phantom reads, and the documentation, unambiguosly, states that the RCIL does not protect from them.
Phantom reads happen under any locking scheduler as well, as you very well know, albeit under different circumstances. I'll be happy to supply an example for those who are unaware of it. Speaking of documentation, I quite agree that Oracle should have supplied some examples showing possible scenarious with phantom reads. However, Oracle reticence regarding the subject is hardly unique; no other database vendor, except IBM/DB2, offers this kind of information.
tengiz wrote:Let me decipher my previous message once it obviously needs some more explanations:
1. According to the docs, with respect to the observed database state a single statement read committed transaction is no different from the same statement executed under serializable transaction, except serializable might need to be restarted manually on write conflicts. Therefore, for any data produced by any single-statement read committed transaction there should be a way to execute it under serializable to create exactly the same database state, unless the documentation says otherwise.
Probably the choice of words in the documenation is really poor... Let me offer another description of what is happening: in an RC transaction each query looks for data consistent as of the point in time when the individual query started. In a serializable transaction all queries look for data consistent as of the timestamp when the FIRST query of the entire transaction started. Additionally, the SIL eliminates phantoms and non-repeatables.
tengiz wrote:2. If updates might transform data in such a way that the final result cannot be possibly obtained from some consistent view of the database, than I'd say, that Oracle should explicitly admit that the statement level consistency never warrants the consistent outcome of update operations. If you're saying that I mistakenly believed that you considered the results of the experiments to be 'perfectly consistent' and if you agree, that these results are inconsistent indeed, then there's nothing to argue about.
_No_ database provides a consistent outcome in all the possible scenarious in the READ COMMITTED isolation level. The ugly phenomena, like phantoms and non-repeatables, simply show up under different circumstances. As I said before, what provides consistency is the real SERIALIZABLE. But today's implementation thereof is so unperformant as to be impractical, therefore, a hypothetical developer has to use the RCIL re-enforced with, say, SELECT FOR UPDATE in Oracle's case or various locking hints in the case of the locking scheduler. Sometimes one can use other tools like Oracle's Snapshot IL to solve concurrency issues.
Oracle, by the way, used to have an initialization parameter (SERIALIZABLE=true/false) that has been deprecated in the current releases but still can be used (the documentation does not describe it). With this parameter set to 'true', Oracle behaved in a manner similar to Sybase's Serializable when a whole table was locked during any update. I believe it was deprecated because it's never enjoyed much popularity...
tengiz wrote:3. The solutions that have been mentioned are irrelevant. I'm trying to make sense out of Oracle claims and the observed facts of life that in my opinion are clearly conflicting.
I suggest we stick to the facts of life ;)
tengiz wrote:Now you've lost me. For the purposes of this particular discussion only I don't care about fine tuning or best design practices, I only care about promises explicitly or implicitly made by Oracle. And I'm really surprised by your reply. Do I need to attach a dozen paragraph disclaimer explaining all possible interpretations of any specific context-sensitive statement every time it might seem ambiguous or vague?
No, of course, you needn't, we can always eliminate any ambiguity through a friendly dialog ;) I merely wanted to stress the fact that performance is usually much more important a factor in database design than one might imagine.
tengiz wrote: So, can you show me a piece of the standard documentation which makes it obvious for a reasonable applicatiopn developer that he indeed has to use serializable for any given single-statement transaction to get predictable results?
Such a document does not exist as that would be too harsh a requirement. Database application design, as you undoubtedly know, is based on a set of various tradeoffs. In some circumstances, the RCIL alone is good enough per se, sometimes it has to be 'enhanced' with optimistic/pessimistic locking, sometimes, as I said, one can use other tools like Oracle's Serializable.
P.S. When I was writing my response, it occured to me that the example with which you started the discussion is prone to dead-locks if one assumes random transaction arrival:
Code: Select all
Session 1:
drop table fruits;
create table fruits
(
bin int primary key,
amount int default (0) not null,
pending int default (0) not null,
cleared datetime default (getdate()) null
)
go
insert into fruits (bin, amount, pending, cleared) values (1, 10, -2, null);
insert into fruits (bin, amount, pending, cleared) values (2, 0, 2, null);
insert into fruits (bin, amount, pending) values (3, 0, 0);
commit;
go
select * from fruits;
go
update fruits set pending = pending + 1, cleared = null where bin = 3;
go
Session 2:
update fruits
set amount = amount + pending, pending = 0, cleared = getdate()
where cleared is null;
select * from fruits;
go
Session 1:
update fruits set pending = pending - 1, cleared = null where bin = 1;
go
Msg 1205, Level 13, State 50, Server VJC, Line 1
Transaction (Process ID 51) was deadlocked on {lock} resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
I described the situation when Row 3 is updated first. In your original example Row 1 was updated first.
So assuming a random transaction ordering, there is a bug ); in the code or the order is implied. Still, in the latter case our hypothetical developer will be as puzzled as with Oracle's phantoms whilst trying to figure out why ordering matters.
Rgds.
