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

I meant commit and continue current transaction. The transaction is opened
on the user application caching composite data from many tables regarding
a root object. Because almost all applications cache data, there is apparently
a pattern "optimistic offline lock" where orb middleware basically adds
a version field to rows , because transactions are usually begun just
when the user has done a modification to a displayed value, and now
wants to change an old cached value which he believes is the current value.
The middleware starts a transaction, and reads the version number, and
if it has been incremented since the initial transaction that read
the value and the version number, it then informs the user that
a new old value exists, and whether he wants to overwrite it.
This is basically a duplication of mvcc, which has to occur with
all applications that can't start long running transactions from
the very beginning of reading a complex object, because there are a
lot of updates per work unit, and if there is a crash during the work unit,
then a lot of updates would be lost, unless the client app also does
it's own WAL, which is another duplication.


On Sun Oct 14  1:56 , "Trevor Talbot"  sent:

>On 10/13/07, syan tan  > wrote:
>> I was wondering whether there could be an atomic commit;begin command
>> for postgresql, in order to open up a transaction at the beginning of
>> a unit of work in a client session, so that client applications don't
>> have to duplicate work with having optimistic locking and version id
>> fields in their table rows. savepoint doesn't actually commit writes
>> in a transaction upto the time savepoint is called, but it's useful
>> for detecting conflicts, whilst allowing work to continue ( e.g.
>> with timeout set) . the atomic commit;begin wouldn't be necessary
>> except a client could crash before the end of the unit of work,
>> and work done upto that point would be lost in the transaction.
>> the atomic commit;begin is so that clients can use postgresql's
>> mechanisms for detecting concurrency read/write conflicts by
>> issuing savepoints before each write, instead of the current need
>> to begin;select for update  xxx, client_versionid (or xmin) ;  ( client
>> checks version id hasn't changed against version id stored when last
>> selected for read); update; commit .
>
>I'm not following your train of thought.  It sounds as though you want
>to commit data without actually leaving your current transaction, but
>what do you need the transaction for?
>
>I don't understand how an atomic COMMIT;BEGIN would help.  Consider a
>transaction with serializable isolation: your snapshot view of the
>data exists exactly as long as your transaction does.  A COMMIT
>followed by a BEGIN, whether atomic or not, is going to change your
>view of the data.
>
>If you want it to do something else, what is that exactly?
>
>>   Also, if the transaction is in read committed mode, then if
>> a write failed ,and a rollback to savepoint was done, you could
>> do select again ,get the new value, inform the client, and if
>> the user elected to go ahead, overwrite with their new value,
>> it would work the second time, because one has read the committed
>> select.
>
>What is preventing you from doing that now?

On 10/14/07, Syan Tan <kittylitter@people.net.au> wrote:
> I meant commit and continue current transaction. The transaction is opened
> on the user application caching composite data from many tables regarding
> a root object. Because almost all applications cache data, there is apparently
> a pattern "optimistic offline lock" where orb middleware basically adds
> a version field to rows , because transactions are usually begun just
> when the user has done a modification to a displayed value, and now
> wants to change an old cached value which he believes is the current value.

Well, transactional semantics won't help you here.

In order to detect a change occurred, what you want is a SERIALIZABLE
transaction: you want the update to fail if the row it matches is no
longer the same version as your snapshot.  However, in order to read
the new value to decide if you want to update it anyway, you need to
leave your current snapshot.  As soon as you do that, ALL previously
read values lose the update checks that snapshot provided you.

If you use a READ COMMITTED transaction, there is no check, since the
update can see the new value itself.

Nested transactions or the ability to commit some data without leaving
the current transaction won't get you want you want either, since
you're really looking for per-row behavior, not per-transaction.

> The middleware starts a transaction, and reads the version number, and
> if it has been incremented since the initial transaction that read
> the value and the version number, it then informs the user that
> a new old value exists, and whether he wants to overwrite it.

A way to do this using PostgreSQL's own row version data came up
recently on this list.  Have a look at this post and the one following
it:
http://archives.postgresql.org/pgsql-general/2007-10/msg00503.php