Transaction vs. Savepoints

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Transaction vs. Savepoints
Date: 2007-02-09 05:04:59
Message-ID: 45CC00FB.9090704@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 ?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Phillip Smith 2007-02-09 05:47:12 Re: Transaction vs. Savepoints
Previous Message Tom Lane 2007-02-08 19:10:46 Re: relation 12345 is still open