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 ?
