At 11:01 AM 17/05/2010, Alan McDonald wrote:

>> > 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*?
>
>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.

True for REPEATABLE READ and SNAPSHOT TABLE STABILITY isolation levels, not true for READ COMMITTED. A RC transaction sees everything that is committed in the database, at all times. Perhaps you are confusing this with what *the application* sees in its client-side buffers....your Delphi datasets, for example, will continue to display what was stored in their buffers until you requery.

>> 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

Michael is correct for a RC transaction, while your answer applies to RR and STC transactions.

>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.

To clarify - Michael's two RC transactions see the latest committed version *at all times*. However, the RO/RC transaction can NOT see the uncommitted work from the RW/RC transaction under any circumstances.

As Ann said, if a client application needs write access to work with a stable set of data, it should use RR transactions for that work.

./heLen