Transaction vs. Savepoints

Поиск
Список
Период
Сортировка
От Andreas
Тема Transaction vs. Savepoints
Дата
Msg-id 45CC00FB.9090704@gmx.net
обсуждение исходный текст
Ответы Re: Transaction vs. Savepoints  ("Phillip Smith" <phillip.smith@weatherbeeta.com.au>)
Re: Transaction vs. Savepoints  (Richard Broersma Jr <rabroersma@yahoo.com>)
Re: Transaction vs. Savepoints  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 ?


В списке pgsql-novice по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: relation 12345 is still open
Следующее
От: "Phillip Smith"
Дата:
Сообщение: Re: Transaction vs. Savepoints