Обсуждение: 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
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.