Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

And it also tells you how to stop it --bibtex iirc

Sent from my iPhone

> On Apr 9, 2014, at 8:41 PM, Amit Langote <amitlangote09@gmail.com> wrote:
>
> Hi,
>
> Currently there is a warning against the following in manual:
>
> BEGIN;
> SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
> SAVEPOINT s;
> UPDATE mytable SET ... WHERE key = 1;
> ROLLBACK TO s;
>
> here: http://www.postgresql.org/docs/9.2/static/sql-select.html
>
> IIUC, it says if the lock-upgrading sub-transaction is rollback'd, as
> an undesirable effect, any lock held by the parent transaction is
> effectively lost.
>
> A few tests suggest that the lock is still effective for a concurrent
> transaction started before the lock-upgrading operation (UPDATE) in
> the later savepoint. The lock is forgotten, though, if a concurrent
> transaction acquired the lock after the UPDATE on the tuple in the
> later savepoint. As soon as the UPDATE is rollback'd, the concurrent
> transaction, blind to any lock the parent transaction had on the
> tuple, gets the lock.
>
>
> --------------------------------------------------
> 1] -- session-1
>
> $ BEGIN;
> $ SELECT * FROM mytable WHERE Key = 1 FOR UPDATE
>
> 2] -- session-1
>
> $ SAVEPOINT s;
> $ UPDATE mytable SET ... WHERE key = 1;
>
> 3] -- session-2
>
> $ SELECT * FROM mytable WHERE Key = 1 FOR UPDATE
>
> 4] -- session-1
>
> $ ROLLBACK TO s;
>
> 5] -- session-2
>
> -- gets the lock and free to modify the tuple (inconistently, off course)
> ------------------------------------------------------
>
> Although, if [3] were before [2], this wouldn't happen
>
> I know it is still a warned-against usage; but, is it useful to
> clarify this nuance of the behavior?
>
> --
> Amit
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general