Re: [GENERAL] Transaction Question

[ General removed, hackers added.]

Where are we on nested transactions.  Is it something we can get for 7.5?

---------------------------------------------------------------------------

Manfred Koizar wrote:
> On Wed, 3 Dec 2003 08:08:49 -0000 (GMT), "John Sidney-Woollett"
> <johnsw@wardbrook.com> wrote:
> >Issue - nested transactions
> 
> >This is an issue for us because some procedures make use of a function
> >which issues a row level lock on a table (select ... for update) in order
> >to read and then update a counter, and which then commits to release the
> >lock. The nested function returns the new counter value on return.
> 
> AFAICS nested transactions - at least in the way we plan to implement
> them - won't help, because subtransaction commit will not release locks.
> We see a subtransaction as part of the main transaction.  If a
> subtransaction commits but the main transaction aborts, the
> subtransaction's effects are rolled back.
> 
>     START TRANSACTION;   -- main xact
>     ...
>     START TRANSACTION;   -- sub xact
>     UPDATE t SET n=n+1 WHERE i=42;
> 
> This locks the row with i=42, because if another transaction wants to
> update this row, it cannot know whether to start with the old or the new
> value of n before our transaction commits or rolls back.
> 
>     COMMIT;              --sub xact
> 
> Here we are still in the main transaction.  Nothing has changed for
> other backends, because they still don't know whether our main
> transaction will succeed or fail.  So we have to keep the lock...
> 
> >Is there a simple/elegant solution to this problem?
> 
> Perhaps dblink?  Just a thought, I don't have any personal experience
> with it.
> 
> Servus
>  Manfred
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073