Re: SAVEPOINT and FOR UPDATE

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