public inbox for [email protected]help / color / mirror / Atom feed
About upgrading a (tuple?) lock in a rollback'd sub-transaction 5+ messages / 3 participants [nested] [flat]
* About upgrading a (tuple?) lock in a rollback'd sub-transaction @ 2014-04-10 02:41 Amit Langote <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Amit Langote @ 2014-04-10 02:41 UTC (permalink / raw) To: Postgres General <[email protected]>; pgsql-docs 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About upgrading a (tuple?) lock in a rollback'd sub-transaction @ 2014-04-10 13:25 Rob Sargent <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Rob Sargent @ 2014-04-10 13:25 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Postgres General <[email protected]>; pgsql-docs 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 <[email protected]> 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction @ 2014-04-11 00:45 Amit Langote <[email protected]> parent: Rob Sargent <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Amit Langote @ 2014-04-11 00:45 UTC (permalink / raw) To: Rob Sargent <[email protected]>; +Cc: Postgres General <[email protected]>; pgsql-docs On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent <[email protected]> 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 -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction @ 2014-04-11 02:52 Alvaro Herrera <[email protected]> parent: Amit Langote <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Alvaro Herrera @ 2014-04-11 02:52 UTC (permalink / raw) To: Amit Langote <[email protected]>; +Cc: Rob Sargent <[email protected]>; Postgres General <[email protected]>; pgsql-docs Amit Langote wrote: > On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent <[email protected]> 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 -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction @ 2014-04-11 03:40 Amit Langote <[email protected]> parent: Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Amit Langote @ 2014-04-11 03:40 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: Rob Sargent <[email protected]>; Postgres General <[email protected]>; pgsql-docs On Fri, Apr 11, 2014 at 11:52 AM, Alvaro Herrera <[email protected]> wrote: > Amit Langote wrote: >> On Thu, Apr 10, 2014 at 10:25 PM, Rob Sargent <[email protected]> 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 -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-04-11 03:40 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-04-10 02:41 About upgrading a (tuple?) lock in a rollback'd sub-transaction Amit Langote <[email protected]> 2014-04-10 13:25 ` Rob Sargent <[email protected]> 2014-04-11 00:45 ` Amit Langote <[email protected]> 2014-04-11 02:52 ` Alvaro Herrera <[email protected]> 2014-04-11 03:40 ` Amit Langote <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox