Thread: atomic commit;begin for long running transactions , in combination with savepoint.
atomic commit;begin for long running transactions , in combination with savepoint.
From
syan tan
Date:
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 . set autocommit to on , wouldn't cut it would it, because between writes there is no transaction , so savepoints couldn't be used for conflict checking. 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.
Re: atomic commit;begin for long running transactions , in combination with savepoint.
From
"Trevor Talbot"
Date:
On 10/13/07, syan tan <kittylitter@people.net.au> 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?