Let me respond to your two posting at one go ...
tengiz wrote:Here's another one - what if there is no predicate at all? Do you believe that in this case Oracle should never re-scan? If so, then how would you like the following scenario. Session 1:Session 2:Code: Select all
drop table a;
create table a
(
pk int primary key,
fk int null,
data int null,
constraint c_fk foreign key (fk) references a (pk)
);
insert into a values (1, null, null);
commit;
select * from a;
insert into a values (2, 1, null);
update a set fk = 2 where pk = 1;
select * from a;
...
PK FK DATA
---------- ---------- ----------
1
...
PK FK DATA
---------- ---------- ----------
1 2
2 1Session 1:Code: Select all
update a set data = fk;
select * from a;
commit;Session 2:Code: Select all
commit;Code: Select all
PK FK DATA
---------- ---------- ----------
1 2 2 <-- looks like we did see the latest committed state.
2 1 <-- nope, not true for this one
Do you believe that the result produced by session 2 is consistent? If so, then I
rest my case.
This case is no different from the cases you've presented earlier. Without a predicate, the conceptual 'consistent select' will select the whole table as of the point in time Session 2's update was issued, hence, only one row is updated. No surprise here, right ? As to my belief, see below.
tengiz wrote:Now, if in the session 2 instead of <update a set data = fk> you'd issue <update a set pk = pk, data = fk>, the result would be different:Code: Select all
PK FK DATA
---------- ---------- ----------
1 2 2
2 1 1
And this is consistent indeed.
This is not surprising either -- the locked low predicate column changed causing Sessio 2's transaction abort and restart.
In your previous posting you wrote:
tengiz wrote:vc, возможно я, как всегда, нечётко выражаюсь. Попробую слегка по-другому:
Я прекрасно понимаю, что делает Oracle. Ваши объяснения как это происходит не вызывают у меня почти никаких возражений. Проблема в том, что я считаю, что то что делает Oracle: во-первых, неправильно; во-вторых, не соответствует тому, что обещано в документации.
Почему я считаю, что результат, полученный в этих опытах неверный? Потому что транзакция атомарна по определению. Никакие других транзакции не должны видеть половину зафиксированного состояния (случай грязного чтения, очевидно, игнорируем). Или всё, или ничего. А Oracle на RC этого не обеспечивает в рассматриваемом примере не то что для транзакции, а даже для отдельного оператора, который тоже атомарен по определению - чего требует и стандарт, и формальная теория, и просто здравый смысл. Но оператор, тем не менее, отработал так, как если бы ему была представлена только часть зафиксированных данных.
Atomic transaction .. A mighty big word. Let's apply some defintion to it and see if Oracle satisfies it:
"a transaction is a unit of operation. It's atomic, i.e. either all the transaction's actions are completed or none are". That's all.
Surely Oracle conforms to the definition -- we have not observed partial rollbacks or commits yet, right ? I do not believe you showed that Oracle sees "a half fixed transaction".
Let's recall, for the benefit of those who might be reading this stuff, how Oracle performs an update/delete. A consistent set of rows is selected based on the predicate, if any, and the timestamp (each Oracle block has one) corresponding to the point in time the update statement was issued.
Say, we have two transactions A and B. Transaction A issues an update statement with the T1 timestamp and the isolation level is READ COMMITTED. When A arrives to some row, it discovers that the row is locked because B has already updated it. A blocks and waits until B commits. After B commits, there are two possible outcomes:
Case 1. The predicate value for the locked row did not change. Transaction A proceeds to update the set of rows it selected at T1. What people are usually upset about is the fact that A does not pick up the _new_ rows satisfying A's predicate. However, the READ COMMITTED isolation level never promised that, non-repeatable reads are the phenomenon explicitly allowed by this IL both in SLQ'92 and '99. How this situation is different in comparison to the case when a simple select statement runs and another transaction added/deleted/modified some rows whilst the select was being executed ? One would get exactly the same situation as with updates, namely non-repeatable reads.
Case 2. The predicate value changed. Transaction A has no other choice but to abort because it 'lost' the locked row it was going to operate on -- the original selection as of T1 is no longer consistent precisely because of the row loss. So transaction A rolls back all the changes it might have made and starts from scratch using the new T2 timestamp as of the point in time when B committed. In Case 2, getting new rows, if any, is a side benefit, not a requirement.
In both cases Oracle operates over a consistent selection, in Case 1 as of T1 and in Case B as of T2. In none of the above cases, a partially committed set of rows is used.
Demanding that A should behave in Case 1 in the same way as it did in Case 2 would be a call for REAPEATABLE READS which can be accomplished by
a) running the update transaction in Oracle's Serializable mode;
or
b) using pessimistic locking via SELECT FOR UPDATE;
or
c) implementing optimistic locking via timestamping or checksumming.
tengiz wrote:Причина - алгоритм, который применяет Oracle для разруливания конфликтующих обновлений, для оптимизации производительности с целью избежать ненужных (на взгляд Oracle) перезапусков, на мой взгляд имеет серьёзный дефект, который приводит к искажению данных. Круг замкнулся - именно с этого я и начинал эту дискуссию.
I do not think you presented a convincing proof of the purported defect, and I am easy to convince, just recall our discussion of whether Oracle's Serializable is really serializable ( No). At least, show where exactly the RC contract is violated.
tengiz wrote:Попробуйте, кстати, проделать такой же опыт на SQL Server или Sybase.
If we want to decide whether a specific implementation is 'good enough', we must use a common yardstick, like ACID, isolation levels etc. Specific implementation pecularities are irrelevant in this context, although very important in their own right if we want to use a specific implementation to its fullest.
Besides, I do not like, in general, database pissing contests. After all, it's just a tool more or less suitable for a specific task(s).
However, to spice up a little our discussion, here's a little tit-for-tat:
Under our favourite isolation level READ COMMITTED, no blocking database (Sybase, DB2, etc.), guarantees correct results for such simple queries as
'select sum(val) from t1 <predicate>'. They may and sometimes will return bogus results that have at no point in time, ever existed in the database. Now, that's really 'inconsistent', right ? ;)
Rgds.
