Re: Strange/Correct? behavior of SELECT FOR UPDATE

On 01/22/2016 01:41 AM, david.turon@linuxbox.cz wrote:
> Hi,
>
> we have some question about behavior SELECT FOR UPDATE. We want find
> record with open bounds tstzrange, close it a insert new open. We use
> SELECT FOR UPDATE in function, but sometimes 2rows inserted. I show this
> on simple example with integer data type. Here is:
>
> --tested on postgresql 9.5.0
>
> CREATE TABLE test(x int);
>
> INSERT INTO test VALUES (1);
>
> -------------------------------------------------------------------------
> --transaction1
>
> BEGIN;
>
> SELECT * FROM test WHERE x=1 FOR UPDATE;
>   x
> ---
>   1
> (1 row)
>
> UPDATE test SET x=2 WHERE x=1;
> --UPDATE 1
>
> INSERT INTO test VALUES (1);
> --INSERT 0 1
>
> SELECT * FROM test ;
>   x
> ---
>   2
>   1
> (2 rows)
>
> ------------------------------------------------------------------
> --transaction2
> BEGIN;
>
> SELECT * FROM test WHERE x=1 FOR UPDATE; --here transaction hang, thats
> what we want...
> ---------------------------------------------------------------------
> --transaction1
>
> COMMIT;
> --------------------------------------------------------------------
> --transaction2
> --now lock released
> SELECT * FROM test WHERE x=1 FOR UPDATE;
>   x
> ---
> (0 row)
>
> -- but we cant see inserted row with value 1, only updated records can
> we see

I would take a look at this, in particular the *** sectioned:

http://www.postgresql.org/docs/9.5/interactive/transaction-iso.html

13.2.1. Read Committed Isolation Level

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave
the same as SELECT in terms of searching for target rows: they will only
find target rows that were committed as of the command start time.
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 rolls back, then its effects are negated and the second
updater can proceed with updating the originally found row. *** 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. *** In
the case of SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the
updated version of the row that is locked and returned to the client.

As I understand that, it means that the original row that had x=1 no
longer exists as it now has x=2. The subsequent row that was INSERTed
occurred inside transaction 1 and was not visible to transaction 2 when
it started, so when transaction 1 COMMITed the query SELECT * FROM test
WHERE x=1 FOR UPDATE found nothing.



> -- so our function here insert new row with value 1, becouse don't know
> about about existing row
> -- if we tray repeat select now we can see row that was inserted by
> transaction1

I would say you are seeing the row just created above.

> SELECT * FROM test WHERE x=1;
>   x
> ---
>   1
> (1 row)
> -------------------------------------------------------------------------
> We try prevent this situation, i know we can use EXCLUDE index on
> tstzrange column, but transaction2 rollback or we can use LOCK TABLE
> test IN EXCLUSIVE MODE - this working but locks whole table or we need
> ask table again with SELECT FOR UPDATE - some double check before insert
> ...Is there any other way how to close tstzrange with minimum locks?
>
> Its correct behavior or not?
>
> Thanks
>
> David Turoň
>
>
> --
> -------------------------------------
> Ing. David TUROŇ
> LinuxBox.cz, s.r.o.
> 28. rijna 168, 709 01 Ostrava
>
> tel.:    +420 591 166 224
> fax:    +420 596 621 273
> mobil:  +420 732 589 152
> www.linuxbox.cz
>
> mobil servis: +420 737 238 656
> email servis: servis@linuxbox.cz
> -------------------------------------
>


--
Adrian Klaver
adrian.klaver@aklaver.com