Geek Answers Handbook

Postgres, update and lock

I am working on Postgres 9.2.

There are 2 UPDATES, each of which has its own transactions. One is as follows:

UPDATE foo SET a=1 WHERE b IN (1,2,3,4);

Other similar:

UPDATE foo SET a=2 WHERE b IN (1,2,3,4);

They can work simultaneously and actually have 500+ in the expression "IN". Sometimes I see dead ends. Is it true that the order of the elements in the "IN" expression may not actually affect the true ordering of the lock?

+4
source share
2 answers

. , , IN , UPDATE, , , , .

WHERE UPDATE , SELECT. , UPDATE SELECT, , , , , .

SELECT , IN :

/:

create table foo
(
  id serial,
  val text
);

insert into foo (val)
values ('one'), ('two'), ('three'), ('four');

:

select *
from foo
where id in (1,2,3,4);


select *
from foo
where id in (4,3,2,1);

- id 1-4.

, ORDER BY select. , Postgres , (. 8 ORDER BY Postgres SELECT doc). , , ​​( ). , , ( , ..), .

, UPDATE. - - , , , , , , , , , .

sqlfiddle .

/ :

, , . , , , , .

, - concurrency, - , SQL, , Python. , , , , Postgres concurrency .

, SQL, . - COMMIT , - .

- Postgres, PL/pgSQL. , , Python, ( ) , , UPDATEs .

+5

UPDATE ORDER BY.
SELECT. FOR UPDATE :

UPDATE foo f
SET    a = 1
FROM (
   SELECT b FROM foo
   WHERE  b IN (1,2,3,4)
   ORDER BY b
   FOR   UPDATE
   ) upd
WHERE f.b = upd.b;

, b UNIQUE ORDER BY, .

UPDATE, DELETE SELECT .. FOR UPDATE .

, :

+7

More articles:


All Articles