Alan McDonald schrieb am 17.05.2010 um 00:32:48 (+1000):

> > In a transaction configured with READ ONLY READ COMMITTED, is it
> > possible to see just part of a commit?

It has occurred to me that READ ONLY should be irrelevant to this
problem; it should be about READ COMMITTED only.

> > Is it possible for my RO/RC transaction to see one of the two
> > rows affected by the intervening UPDATE in its state before the
> > intervening COMMIT, and the other in its state after that COMMIT?
>
> No - you select transaction is started before the update and will only
> see data committed to that point. It will never see the updated from a
> subsequent update until it commits and re-reads

I think you mean SELECT *statement*, not *transaction*?

Because for a READ COMMITTED transaction, it is not true. My SELECT is
only one of possibly several statements inside a transaction, and by
specifying an isolation level of READ COMMITTED I accept to see new
record versions of subsequently committed transactions:

SQL> commit;
SQL> SET TRANSACTION READ COMMITTED;
SQL> select count(*) from TTT;

COUNT
============
3

SQL> select count(*) from TTT;

COUNT
============
4

This is, of course, possible because my isolation is only READ
COMMITTED, and in another session I inserted a record into TTT.

> > This would not, if I understand correctly, constitute a DIRTY READ
> > (which Firebird does not allow), as both versions are committed,
> > just not by the same transaction.
>
> Subsequent commits are not visible

For the statement, probably not. But for a READ COMMITTED transaction,
definitely yes.

--
Michael Ludwig