Thread: Transaction vs. Savepoints
Hi, I'm a bit confused about the handling of transactions and savepoints withing reallife applications. It's theoretically all pretty neat and I understand what's the point of transactions in general. As far as I know, PG handles 1 transaction per connection an several savepoints boxed within. E.G. T1 ( Sp1, Sp2, Sp3 ) I could commit Sp1, rollback Sp2 and commit Sp3 in a session. If I commit T1 all those actions get permanently written to to DB whereas if I rollback T1 all committed savepoints within get rolled back, too. Suppose an application that runs all the 8 workhours. It handles customers and goods/services. I obviously can't start a global transaction with the application because in the worst case something bad happens at the end of a workday and T1 can't be committed so all the work of this day would be lost. So I have to manage smaller work-packages. Like T1 (open a customer -- change his adress info -- write it back -- commit T1) He might have several adresses so I'd show them in a list and provide a dialoge that opens to edit single adresses. T1 (open a customer -- Sp1 (change adress 3 -- write it back -- commit Sp1) -- (Sp2 add a phone note -- commit Sp2) -- commit T1) What if before T1 gets committed the front-end-application crashes. T1 gets rolled back and erases the changes of Sp1 and Sp2 even though a user would expect them to be safe since those 2 dialoges might have been closed 50 minutes ago before he went to lunch. He would be a wee bit unhappy to find them lost, too. Now suppose you open a customer-form F1 and another form F2 that is semantically independent like some infos about an item in your warehouse or some color-config-dialog. Both dialogs would try to start a transaction but the second one can't. If I somehow keep track of running transactions and safepoint within the application I could let the second dialog use a safepoint instead of a real transaction. What if F2 gets closed OK (committed) but the first dialog F1 that got the transaction gets rolled back? Using separate connections everywhere where a bit of the application accesses the DB isn't desirable either because initiating connections is way slower than using an allready established one. I actually wondered if I can get away with a single connection that gets build up within the start of the application. How do you go about those problems ?
You know you can start and commit multiple transactions in one
connection...?
<open connection to pg daemon; say when user logs on at 9.00am>
BEGIN;
<transaction 1 here>
COMMIT;
BEGIN;
<transaction 2 here>
<save point 1>
<save point 2>
COMMIT;
BEGIN;
<transaction 3 here>
<save point 1>
COMMIT;
<close connection to pg daemon; say when user logs off at 5.00pm>
Just as an example...
-p
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andreas
Sent: Friday, 9 February 2007 16:05
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Transaction vs. Savepoints
Hi,
I'm a bit confused about the handling of transactions and savepoints
withing reallife applications.
It's theoretically all pretty neat and I understand what's the point of
transactions in general.
As far as I know, PG handles 1 transaction per connection an several
savepoints boxed within.
E.G. T1 ( Sp1, Sp2, Sp3 )
I could commit Sp1, rollback Sp2 and commit Sp3 in a session.
If I commit T1 all those actions get permanently written to to DB
whereas if I rollback T1 all committed savepoints within get rolled
back, too.
Suppose an application that runs all the 8 workhours. It handles
customers and goods/services.
I obviously can't start a global transaction with the application
because in the worst case something bad happens at the end of a workday
and T1 can't be committed so all the work of this day would be lost.
So I have to manage smaller work-packages.
Like T1 (open a customer -- change his adress info -- write it back --
commit T1)
He might have several adresses so I'd show them in a list and provide a
dialoge that opens to edit single adresses.
T1 (open a customer -- Sp1 (change adress 3 -- write it back -- commit
Sp1) -- (Sp2 add a phone note -- commit Sp2) -- commit T1)
What if before T1 gets committed the front-end-application crashes.
T1 gets rolled back and erases the changes of Sp1 and Sp2 even though a
user would expect them to be safe since those 2 dialoges might have been
closed 50 minutes ago before he went to lunch.
He would be a wee bit unhappy to find them lost, too.
Now suppose you open a customer-form F1 and another form F2 that is
semantically independent like some infos about an item in your warehouse
or some color-config-dialog.
Both dialogs would try to start a transaction but the second one can't.
If I somehow keep track of running transactions and safepoint within the
application I could let the second dialog use a safepoint instead of a
real transaction.
What if F2 gets closed OK (committed) but the first dialog F1 that got
the transaction gets rolled back?
Using separate connections everywhere where a bit of the application
accesses the DB isn't desirable either because initiating connections is
way slower than using an allready established one.
I actually wondered if I can get away with a single connection that gets
build up within the start of the application.
How do you go about those problems ?
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
> As far as I know, PG handles 1 transaction per connection an several > savepoints boxed within. > E.G. T1 ( Sp1, Sp2, Sp3 ) > I could commit Sp1, rollback Sp2 and commit Sp3 in a session. > If I commit T1 all those actions get permanently written to to DB > whereas if I rollback T1 all committed savepoints within get rolled > back, too. As far as I know, Postgresql defaults to one transaction from ~every~ statement, not per every connection. You do have the option to explicitly open a transaction at the beginning of a connection and commit at the end, but it certainly isn't the default. if your connection crashes, all commited transactions remain commited. Regards, Richard Broersma Jr.
Andreas <maps.on@gmx.net> writes:
> As far as I know, PG handles 1 transaction per connection an several
> savepoints boxed within.
One transaction *at a time*, not one for the whole life of the session.
A good general design rule is that no transaction should stay open for
very long: do what you need to do and commit. So the scenarios you're
envisioning are not relevant to a well-designed application.
regards, tom lane
Tom Lane wrote: > Andreas <maps.on@gmx.net> writes: > >> As far as I know, PG handles 1 transaction per connection an several >> savepoints boxed within. >> > > One transaction *at a time*, not one for the whole life of the session. > Err .. That was what I meant. ;-) > A good general design rule is that no transaction should stay open for > very long: do what you need to do and commit. I understand this in regards to a programms that run queries in a certain order e.g. like a script without user interaction. There I do "this", then "that" and afterwards "something else". A interactive application on the other hand would open records and have them in an edit-form as long as the user needs them. Probaply I just don't get it how to use transactions in such a interactive scenario the right way. I have classes that do the data handling and talk to the DB. So a form would instanciate such a class and present its data to get edited. Some time later the user might klick OK and the form puts the changed data back into the object and lets it store the changes in the DB. Would you begin the transaction with the instantiacion of the object and put the commit in the destructor or BEGIN right where the form got opened and COMMIT in the close of the form? What if I enter a new record (customer) then attach some records that reference this new customer like a phone notes or a document history. How could I do this without nested transactions? > So the scenarios you're > envisioning are not relevant to a well-designed application. > Is there documentation that describes how to do such a good design? I figure this is an issue that is not bound to PostgreSQL specifically.
Andreas <maps.on@gmx.net> writes:
> A interactive application on the other hand would open records and have
> them in an edit-form as long as the user needs them.
> Probaply I just don't get it how to use transactions in such a
> interactive scenario the right way.
Right, a lot of novices think they should hold open a transaction for
the whole edit cycle. This is not good design: what if the user goes
off to lunch and meanwhile someone else wants to edit the same record?
Usually the best approach is to fetch the data without acquiring any
lock, allow the interactive editing to happen outside a transaction,
then when the user hits SAVE, perform a transaction that locks the
row(s), checks for conflicting changes, and commits if no conflict.
On conflict you might be able to resolve the discrepancy automatically,
but if not, you just fail and let the user deal with it ...
regards, tom lane
On Fri, Feb 09, 2007 at 11:21:59AM -0500, Tom Lane wrote: > > Usually the best approach is to fetch the data without acquiring any > lock, allow the interactive editing to happen outside a transaction, > then when the user hits SAVE, perform a transaction that locks the > row(s), checks for conflicting changes, and commits if no conflict. Might you please expand on how the application could check for conflicting changes? Would this be simply fetching the record again, and comparing to the previously-fetched version, to see if the record is still as it was when the user started editing? Something more sophisticated? Thank you for your time. Jim
On Fri, February 9, 2007 2:18 pm, James Long said: > On Fri, Feb 09, 2007 at 11:21:59AM -0500, Tom Lane wrote: >> >> Usually the best approach is to fetch the data without acquiring any >> lock, allow the interactive editing to happen outside a transaction, >> then when the user hits SAVE, perform a transaction that locks the >> row(s), checks for conflicting changes, and commits if no conflict. > > Might you please expand on how the application could check for > conflicting changes? Would this be simply fetching the record > again, and comparing to the previously-fetched version, to see > if the record is still as it was when the user started editing? > Something more sophisticated? > > Thank you for your time. That would be the simple way. More complicated ways might be needed in more complicated scenarios. (Where, for instance, data has keep relational integrity between two different tables, where you would have to check that the records still existed in both before updating one.) Exactly how sophisticated depends on the application, and the data. (And the database design...) In some situations you might be able to come up with a 'merged' change, where both changes are applied, but again that depends on exactly what is being done. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
James Long <pgsql-novice@museum.rain.com> writes:
> On Fri, Feb 09, 2007 at 11:21:59AM -0500, Tom Lane wrote:
>> Usually the best approach is to fetch the data without acquiring any
>> lock, allow the interactive editing to happen outside a transaction,
>> then when the user hits SAVE, perform a transaction that locks the
>> row(s), checks for conflicting changes, and commits if no conflict.
> Might you please expand on how the application could check for
> conflicting changes? Would this be simply fetching the record
> again, and comparing to the previously-fetched version, to see
> if the record is still as it was when the user started editing?
Comparing all the fields is certainly the most portable way. If you
don't mind being Postgres-specific you can instead check xmin and ctid;
if those are the same as what you read before, the tuple hasn't been
modified. So:
SELECT xmin, ctid, * FROM mytab WHERE whatever;
... let user edit data ...
BEGIN;
SELECT xmin, ctid FROM mytab WHERE whatever FOR UPDATE;
-- abort if xmin or ctid is different from before, else:
UPDATE mytab SET ...
COMMIT;
Note the FOR UPDATE, that's to avoid race conditions by locking the
tuple momentarily.
regards, tom lane