From: | "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com> |
---|---|
To: | "Manfred Koizar" <mkoi-pg(at)aon(dot)at> |
Cc: | johnsw(at)wardbrook(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transaction Question |
Date: | 2003-12-04 17:56:33 |
Message-ID: | 3537.192.168.0.64.1070560593.squirrel@mercury.wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
It would be nice if nested transactions could be (optionally) decoupled
from their enclosing transaction.
John
Manfred Koizar said:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Seum-Lim Gan | 2003-12-04 18:57:32 | Re: pg_hba.conf change in 7.4 |
Previous Message | Matthew Lunnon | 2003-12-04 17:27:43 | multiple PostgresQL installations |
From | Date | Subject | |
---|---|---|---|
Next Message | Dror Matalon | 2003-12-04 17:57:38 | Re: tuning questions |
Previous Message | Greg Stark | 2003-12-04 17:55:51 | How to get started hacking on pgsql |