Search Postgresql Archives

SAVEPOINT and FOR UPDATE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux