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
