About upgrading a (tuple?) lock in a rollback'd sub-transaction

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>, pgsql-docs(at)postgresql(dot)org
Subject: About upgrading a (tuple?) lock in a rollback'd sub-transaction
Date: 2014-04-10 02:41:04
Message-ID: CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Rob Sargent 2014-04-10 13:25:50 Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction
Previous Message Greg Stark 2014-04-09 18:22:54 Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2014-04-10 03:16:57 Re: postgresql.conf question... CPU spikes
Previous Message Steve Kehlet 2014-04-10 00:39:12 Re: is there a way to firmly cap postgres worker memory consumption?