Re: Transaction Question

On Wed, 3 Dec 2003 08:08:49 -0000 (GMT), "John Sidney-Woollett"
<johnsw(at)wardbrook(dot)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