Subject Re: Read Only Transactions
Author Adam
--- In, Guido Klapperich
<guido.klapperich@...> wrote:
> > Read-committed mode violates the Isolation and Consistency
> > properties of transactions. I don't know why anybody uses
> > it - at least not anybody who cares about consistent results.
> Sorry, I don't understand that. Can you please explain it a little bit.

A transaction that is influenced by actions of a concurrent
transaction is hardly 'isolated'. A single transaction that runs the
same select query twice and gets two different pieces of information
despite making no change to the tables involved is hardly being

If you run the query:

select id
from mytable
order by id

within a single transaction, then it should ideally always return the
same result (repeatable read / snapshot) unless your transaction has
done something to mytable.

Readcommitted transactions allow your transaction to see changes made
by other committed transactions regardless when they started, so to
that end you can not be sure that the same results will occur.

In a snapshot transaction, you could do something like:

select ID, Name
from mytable;

delete from mytable where id in (select ID, Name
from mytable);

You would always get to see all of the Names in mytable (providing
no-one else was deleting them). In a read-committed transaction, you
run into trouble because if another transaction committed between
these two operations, and that transaction had inserted a record, then
the new record would be deleted without being selected.