From: | david(dot)turon(at)linuxbox(dot)cz |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Strange/Correct? behavior of SELECT FOR UPDATE |
Date: | 2016-01-22 09:41:09 |
Message-ID: | OF7F5E6DE1.67F582E8-ONC1257F42.0032482C-C1257F42.003534A5@notes.linuxbox.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
-- 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
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(at)linuxbox(dot)cz
-------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Geoff Winkless | 2016-01-22 09:55:43 | Re: Let's Do the CoC Right |
Previous Message | Jehan-Guillaume de Rorthais | 2016-01-22 09:18:01 | Re: Auotmated postgres failover |