public inbox for [email protected]  

help / color / mirror / Atom feed

From: Amit Langote <[email protected]>
To: Postgres General <[email protected]>
To: [email protected]
Subject: About upgrading a (tuple?) lock in a rollback'd sub-transaction
Date: Thu, 10 Apr 2014 11:41:04 +0900
Message-ID: <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-general>

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

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]
  Subject: Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction
  In-Reply-To: <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>

* 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