Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-general-owner+M205039=pgsql-general-arkari@postgresql.org>)
 id 1WYEzS-0006bG-P1
 for pgsql-general@arkaria.postgresql.org; Thu, 10 Apr 2014 13:26:03 +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+M205039=pgsql-general-arkari@postgresql.org>)
 id 1WYEzS-0007M1-70
 for pgsql-general@arkaria.postgresql.org; Thu, 10 Apr 2014 13:26:02 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <robjsargent@gmail.com>)
 id 1WYEzP-0007J3-8d; Thu, 10 Apr 2014 13:25:59 +0000
Received: from mail-pa0-x235.google.com ([2607:f8b0:400e:c03::235])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <robjsargent@gmail.com>)
 id 1WYEzM-0006Uu-1M; Thu, 10 Apr 2014 13:25:58 +0000
Received: by mail-pa0-f53.google.com with SMTP id ld10so3960490pab.12
 for <multiple recipients>; Thu, 10 Apr 2014 06:25:54 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=utnQEoSHfTp3gGV7VM3Mdc15W9lqLlmSoKRZeh2EiwA=;
 b=PNI62Zd8Fo/eXB/3B+0somS3P0f0nXaGIDpd1ZQRVjg2BgtuLpMQjsLSgmkMGph0iI
 ngx4dDHRVmXyjNvOVlRoN1/Kft6ZmMwyfKzQtebwQK8cajw1Jk7HJeO60ylb0jy/RYOj
 OsECTj4aGxqfnjFzk1MLsvp9itb4yPc7Yp7RqGLoHIo/g6sm3zcYAN7EWlnS2n/hMM9w
 TE1BFs7GFBfHB5MQE19NR5dSgCzJjLCPlBX6lsjKwd7EDUMqKM3OzNUSpH092+vVAqym
 ZL7qjvTgvpEL1bl4sbLF7zsftbWPX7+h0S2tWPQwlTTInrNlLEXWIHH3yYknyYOraEII
 FNQg==
X-Received: by 10.68.211.164 with SMTP id nd4mr19690904pbc.44.1397136354743;
 Thu, 10 Apr 2014 06:25:54 -0700 (PDT)
Received: from [192.168.23.166] ([67.137.125.35])
 by mx.google.com with ESMTPSA id kl1sm9117378pbd.73.2014.04.10.06.25.52
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 10 Apr 2014 06:25:52 -0700 (PDT)
References: 
 <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>
Mime-Version: 1.0 (1.0)
In-Reply-To: 
 <CA+HiwqHaxZZKyTEtrpz=QpGpVWHzNxtYW2+GneukAsF+f0_9VQ@mail.gmail.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-Id: <7745EA48-8184-4C54-99B6-837D109C7DD5@gmail.com>
Cc: Postgres General <pgsql-general@postgresql.org>,
 "pgsql-docs@postgresql.org" <pgsql-docs@postgresql.org>
X-Mailer: iPhone Mail (11D167)
From: Rob Sargent <robjsargent@gmail.com>
Subject: Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction
Date: Thu, 10 Apr 2014 07:25:50 -0600
To: Amit Langote <amitlangote09@gmail.com>
X-Pg-Spam-Score: -2.0 (--)
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

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


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