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

Поиск
Список
Период
Сортировка
Искать

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

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
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

Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

От:
Rob Sargent <robjsargent@gmail.com>
Дата:
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  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

Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent  wrote:
> And it also tells you how to stop it --bibtex iirc
>

Yeah, it's a caution against a potentially harmful usage anyway. Users
should not use it at all.

I was just wondering if the description of the behavior, that is,
potential disappearance of certain locks is complete enough.

--
Amit

Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

От:
Alvaro Herrera <alvherre@2ndquadrant.com>
Дата:
Amit Langote wrote:
> On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent  wrote:
> > And it also tells you how to stop it --bibtex iirc
> 
> Yeah, it's a caution against a potentially harmful usage anyway. Users
> should not use it at all.
> 
> I was just wondering if the description of the behavior, that is,
> potential disappearance of certain locks is complete enough.

You do realize that this is no longer the case in 9.3, right?
I don't see a point in changing old releases' documentation.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

От:
Amit Langote <amitlangote09@gmail.com>
Дата:
On Fri, Apr 11, 2014 at 11:52 AM, Alvaro Herrera
 wrote:
> Amit Langote wrote:
>> On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent  wrote:
>> > And it also tells you how to stop it --bibtex iirc
>>
>> Yeah, it's a caution against a potentially harmful usage anyway. Users
>> should not use it at all.
>>
>> I was just wondering if the description of the behavior, that is,
>> potential disappearance of certain locks is complete enough.
>
> You do realize that this is no longer the case in 9.3, right?
> I don't see a point in changing old releases' documentation.

I see, okay.
And yes, I'm aware that this's no longer an issue in 9.3.

Thanks,
Amit

FAQ