Thread: Re: atomic commit;begin for long running transactions , in combination with savepoint.

On Mon Oct 15  5:28 , "Trevor Talbot"  sent:

>The only difference is in the data you _can_ read.  For the
>SERIALIZABLE isolation level, that data was decided at the beginning
>of the transaction.  A row that was updated by another transaction
>will make the version that you can see effectively "read only", so
>when the UPDATE tries to change it, there will be a transactional
>conflict due to the isolation level.
>
>> You DONT want a serializable transaction, because then you can't read
>> the other committed value after rolling back to the savepoint.
>
>Correct.  But the READ COMMITTED isolation level does not limit what
>data you can see at the beginning of the transaction, so an UPDATE
>will always find the latest version of a row.  There is no conflict as
>far as the transaction is concerned.
>
>
>Transaction state is not based on what you've read; it doesn't know.
>
>> if x has been changed by another transaction's commit, then the catch will
>> execute ,
>
>The UPDATE will find the latest version of the row.  The change made
>by the other transaction is not a problem at this isolation level (you
>can see it), so the UPDATE will simply proceed and change it anyway.
>The catch block will never execute.
>
>> If the transaction was serializable , and another transaction has committed,
>> this would never work, because a commit would nullify the other
>> transactions write without this transaction ever having seen the other
>> transactions write, so this transaction would always be forced to rollback.
>
>Exactly.  But without SERIALIZABLE isolation, any operations you
>perform always see the other transaction's write, so there is never
>any conflict.
>
>Also keep in mind that MVCC is not the only way to implement
>transactions; pure locking is more common in other databases.  In the
>locking model, most transactions prevent others from writing until
>after they are finished.  Rows simply can't have different versions
>(and of course concurrent performance is awful).

what about postgresql doing something like snapshot isolation level as per
the enemy M$ ?

On 10/15/07, Syan Tan <kittylitter@people.net.au> wrote:

> >Also keep in mind that MVCC is not the only way to implement
> >transactions; pure locking is more common in other databases.  In the
> >locking model, most transactions prevent others from writing until
> >after they are finished.  Rows simply can't have different versions
> >(and of course concurrent performance is awful).
>
> what about postgresql doing something like snapshot isolation level as per
> the enemy M$ ?

SQL Server is normally a pure locking database; from what I can tell,
its snapshot isolation level adds a limited form of MVCC above that,
making its concurrent behavior closer to PostgreSQL's:
http://msdn2.microsoft.com/en-us/library/ms345124(d=printer).aspx