Re: BUG #10315: Transactions seem to be releasing locks early?

On Wed, May 14, 2014 at 12:15:13PM -0400, Tim Channell wrote:
> Here's the relevant documentation: 
>
>  However, such a target row might have already been updated (or deleted or
> locked) by another concurrent transaction by the time it is found. In this
> case, the would-be updater will wait for the first updating transaction to
> commit or roll back (if it is still in progress)... If the first updater
> commits, the second updater will ignore the row if the first updater deleted
> it, otherwise it will attempt to apply its operation to the updated version of
> the row. The search condition of the command (the WHERE clause) is re-evaluated
> to see if the updated version of the row still matches the search condition. If
> so, the second updater proceeds with its operation using the updated version of
> the row

I think you want to read about this example:

http://www.postgresql.org/docs/9.3/static/transaction-iso.html#XACT-READ-COMMITTED

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session: DELETE FROM website WHERE hits = 10;
COMMIT;

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +