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?