public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rob Sargent <[email protected]>
To: Amit Langote <[email protected]>
Cc: Postgres General <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction
Date: Thu, 10 Apr 2014 07:25:50 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>
References: <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-general>

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 <[email protected]> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox