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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
