SAVEPOINT and FOR UPDATE
I'm curious to know more about the postgres implementation of
subtransactions via SAVEPOINT.
If I wanted to set up a multi-statement transaction in which I needed
multiple SELECT ... FOR UPDATE + UPDATE blocks, it would seem
advantageous to be able to combine the SELECT ... FOR UPDATE clauses
with the corresponding UPDATE clauses in a subtransaction in order to
avoid locking rows for the duration of the entire outer transaction.
In my experimentation, I'm not seeing this behavior, so I'm wondering
if I'm misreading or overlooking something in the docs about how to
use SAVEPOINT to create subtransactions.
Here's what I set up as a basic test case in psql:
postgres=# CREATE TABLE updateable1 ( id int primary key );
postgres=# INSERT INTO updateable1 VALUES ( 1 );
postgres=# START TRANSACTION;
postgres=# SAVEPOINT u1;
postgres=# SELECT id FROM updateable1 WHERE id = 1 FOR UPDATE;
Then, in a separate session, I do this:
postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;
This, appropriately, waits.
In the original session, I now do this:
postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;
postgres=# RELEASE u1;
Unfortunately, the second session is still waiting and continues to
do so until I commit the transaction started in the first session. I
sort of expected the release of the savepoint to be tantamount to a
commit of the subtransaction, but it doesn't appear to have been.
I'd like a method for doing the following:
START TRANSACTION;
// do work
// start subtransaction
SELECT ... FOR UPDATE;
UPDATE ...;
// commit subtransaction
// do more work
COMMIT;
Is there any way to achieve the behavior I'm after?
--
Thomas F. O'Connell
Sitening, LLC
http://www.sitening.com/
3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)
"Thomas F. O'Connell" <tfo@sitening.com> writes:
I sort of expected the release of the savepoint to be tantamount to a
commit of the subtransaction, but it doesn't appear to have been.
But you still haven't committed the outer transaction: rolling it back
must undo the effects of the subtransaction, no? So a "release" merely
destroys the separate identity of the subtransaction and assigns all its
effects (including locks) to the parent transaction.
regards, tom lane
On Thu, 2006-08-03 at 12:43 -0500, Thomas F. O'Connell wrote:
I'm curious to know more about the postgres implementation of
subtransactions via SAVEPOINT.
Locks are held until the end of the outer transaction, see:
<http://www.postgresql.org/docs/8.1/static/sql-lock.html>
in the first paragraph it reads:
"Once obtained, the lock is held for the remainder of the current
transaction. (There is no UNLOCK TABLE command; locks are always
released at transaction end.)"
If you want to release a lock before completing more tasks, you should
use two transactions. If a subtransaction could create and release locks
before the outer transaction finished, that would violate the ACID
properties of the outer transaction.
postgres=# CREATE TABLE updateable1 ( id int primary key );
postgres=# INSERT INTO updateable1 VALUES ( 1 );
postgres=# START TRANSACTION;
postgres=# SAVEPOINT u1;
postgres=# SELECT id FROM updateable1 WHERE id = 1 FOR UPDATE;
[ snip ]
postgres=# UPDATE updateable1 SET id = 0 WHERE id = 1;
postgres=# RELEASE u1;
This RELEASE makes it as though you never created a SAVEPOINT.
[ snip ]
I'd like a method for doing the following:
START TRANSACTION;
// do work
// start subtransaction
SELECT ... FOR UPDATE;
UPDATE ...;
// commit subtransaction
// do more work
COMMIT;
What you are trying to do violates ACID because the work done while the
lock was held has not actually completed (because the outer transaction
has not committed).
Regards,
Jeff Davis
Tom Lane <tgl@sss.pgh.pa.us> writes:
"Thomas F. O'Connell" <tfo@sitening.com> writes:
I sort of expected the release of the savepoint to be tantamount to a
commit of the subtransaction, but it doesn't appear to have been.But you still haven't committed the outer transaction: rolling it back
must undo the effects of the subtransaction, no? So a "release" merely
destroys the separate identity of the subtransaction and assigns all its
effects (including locks) to the parent transaction.
There seems to be a recurring confusion between subtransactions and autonomous
transactions. Perhaps there should be a caveat in the SAVEPOINT documentation
explaining that they aren't autonomous transactions and the work inside the
savepoint is still subject to the regular transaction semantics for the
outermost transaction and isn't actually committed until the outer transaction
commits.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
On Fri, 2006-08-04 at 18:01, Gregory Stark wrote:
Tom Lane <tgl@sss.pgh.pa.us> writes:
"Thomas F. O'Connell" <tfo@sitening.com> writes:
I sort of expected the release of the savepoint to be tantamount to a
commit of the subtransaction, but it doesn't appear to have been.But you still haven't committed the outer transaction: rolling it back
must undo the effects of the subtransaction, no? So a "release" merely
destroys the separate identity of the subtransaction and assigns all its
effects (including locks) to the parent transaction.There seems to be a recurring confusion between subtransactions and autonomous
transactions. Perhaps there should be a caveat in the SAVEPOINT documentation
explaining that they aren't autonomous transactions and the work inside the
savepoint is still subject to the regular transaction semantics for the
outermost transaction and isn't actually committed until the outer transaction
commits.
Note that one CAN get autonomous transactions using dblink. I've used
it once or twice in the past to do that.