Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-general-owner+M205030=pgsql-general-arkari@postgresql.org>)
 id 1WY4vq-0002Fu-3Q
 for pgsql-general@arkaria.postgresql.org; Thu, 10 Apr 2014 02:41:38 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-general-owner+M205030=pgsql-general-arkari@postgresql.org>)
 id 1WY4vp-0005L7-9G
 for pgsql-general@arkaria.postgresql.org; Thu, 10 Apr 2014 02:41:37 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <amitlangote09@gmail.com>)
 id 1WY4vm-0005Ks-Rj; Thu, 10 Apr 2014 02:41:34 +0000
Received: from mail-ob0-x230.google.com ([2607:f8b0:4003:c01::230])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <amitlangote09@gmail.com>)
 id 1WY4vf-0006Wa-95; Thu, 10 Apr 2014 02:41:33 +0000
Received: by mail-ob0-f176.google.com with SMTP id wp18so3708349obc.35
 for <multiple recipients>; Wed, 09 Apr 2014 19:41:24 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=mime-version:from:date:message-id:subject:to:content-type;
 bh=tLgkH9PnmhdhirU2H9LJkhOUflvw2P6TvXRZSlOFUCM=;
 b=rPk/yUkWwlgpkeMIuGf0aMb7N9kU4rT1rAd+Ko2C0Hmg7VN30BpBfT0s17SmckyAst
 aiuPVLhuckplaU+bVQw5BArKd74xYMN6d5FvExZ13aM0ay2YHrLGV6CGyHRC17qa41WP
 jSQRYWm7ZfzrsJDezcmd4gg0Kldy/sNohd4YM0kT+LetpAawgNp8P5UPDRGEss61eoGi
 yy7w9jInxRUIIUn9Wwt06xSlaUlSW+ny1XA6LA6wx5kEsnBpyQ3VC/uXn1Xrr/A8XrcG
 tn7cWmTbUB4gYPBLp9sPyGUPfEYABSEWDut60d4nnZ+6ThItbFS8IAuJC1WZ4u1mXOh3
 UzUw==
X-Received: by 10.60.229.228 with SMTP id st4mr11739924oec.16.1397097684509;
 Wed, 09 Apr 2014 19:41:24 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.76.169.98 with HTTP; Wed, 9 Apr 2014 19:41:04 -0700 (PDT)
From: Amit Langote <amitlangote09@gmail.com>
Date: Thu, 10 Apr 2014 11:41:04 +0900
Message-ID: 
 <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>
Subject: About upgrading a (tuple?) lock in a rollback'd sub-transaction
To: Postgres General <pgsql-general@postgresql.org>, pgsql-docs@postgresql.org
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -1.8 (-)
List-Archive: <http://archives.postgresql.org/pgsql-general>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-general.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@postgresql.org>
List-Post: <mailto:pgsql-general@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-general>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
X-Mailing-List: pgsql-general
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org

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