On 01/22/2016 01:41 AM, david.turon@xxxxxxxxxxx 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@xxxxxxxxxxx
-------------------------------------
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
