Re: transaction isolation

Ruud,

> I would think the insert logically takes place at the moment it is
> committed. Nothing undetermined about that.

Agreed. But when does a "virtual snapshot" take place? I believe it's at the start of the transaction.
In order to make reads repeatable, You have to have a virtual snapshot of the database (or at least of
a subset of it) at the time a SELECT begins returning results.

About your (unnamed) product... does it not support the "SET TRANSACTION" statement?

Two products I know, Oracle RDBMS and Oracle Rdb, both support it. Although an implicit transaction will be started if you perform any operation that requires a transaction, I think it's better programming practice to make the start of a transaction explicit. I believe, from what you have written, that you would agree.

Oracle Rdb, unlike Oracle RDBMS, supports snapshot transactions, which can offer the highest level of consistency possible. If a snapshot transaction is started, nothing that had not been committed prior to the snapshot is visible.
There are consequences as far as overhead is concerned.

> Incidently, most of my RDBMS work has been on Oracle. I have done the
> same experiment in an Oracle environment, and it works as I expect it
> would work (fair chance that my expectations are shaped by Oracle, after
> 10 years ;-). I don't know, however, whether a stricter transaction
> isolation level would make for the same behavior I observed in the other
> product.
>

This surprises me. I'm going to have to repeat your experiment for myself, when time permits.

--
Regards,
    David Cressey
    www.dcressey.com