Alan McDonald schrieb am 17.05.2010 um 09:01:34 (+1000):

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

> No I mean transaction. The transaction where your initial select is
> executed will continue to see only the records which existed at the
> time the transaction started.

My READ COMMITTED transaction will see what is committed by others.
It is not isolated from other transactions' commits. It is only isolated
from dirty reads: thankfully, Firebird does not allow dirty reads under
any circumstances.

> > 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:
>
> only if you commit the intervening transaction

Yes, of course. Otherwise it would be what's termed a "dirty read".
But as soon as the intervening transaction has committed, its versions
become visible for me in READ COMMITTED mode. Even in the very statement
where I've read rows that have been updated since that moment I might
encounter other rows affected by the same UPDATE, and I will read their
new version. It's still not a dirty read (as per the definition), but
it's not a clean read either.

> You said:
> > 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?
>
> i.e. you said 'before the commit'.
>
> I read this to mean before the commit of the update. in which case my
> response stands, if this is a wrong interpretation and you mean
> "before the commit of the select statement transaction, then that's
> different.

You read me correctly. This is a problem with READ COMMITTED isolation.
In one and the same transaction, and even in one and the same statement,
out of a set of rows (33, 34, 777, 778) affected by an intervening
UPDATE, you may see (33, 34) in the version before the intervening
COMMIT, and (777, 778) in the version after the intervening COMMIT.

My doubt (and hence the reason to ask) was only with regard to whether
this skewed vision in READ COMMITTED was possible within one and the
same *statement*, not transaction. It has become clear to me that a
statement is not a meaningful context of execution, as the server is
built to operate in terms of *transactions*, not statements.

So, READ COMMITTED is less consistent than I had erroneously assumed
before posting my question.
--
Michael Ludwig