Pro mě překvapivé chování při souběžném mazání více vlákny z jedné tabulky

[Jan Michálek]

Zdar

Narazil jsem na zvláštní věc, která mě, přiznám se, dost překvapila.

Vysvětlím. Chtěl jsem si udělat jednoduchou frontu. Mám dvě jednosloupcové tabulky a dotaz, který vypadá nějak takto.

WITH del AS (
    DELETE FROM zpracovat
    WHERE kod IN (
        SELECT kod FROM zpracovat
        ORDER BY kod
        LIMIT :batch_size)
    RETURNING kod
), hotovo AS (
    INSERT hotovo
    SELECT * FROM del
)
SELECT * FROM del;

To mi přišlo jako vcelku robustní konstrukce, na který není co posrat, že je to jeden dotaz, tudíž atomický hotovo dvacet, postgre se o vše postará. Odbaví toho, kdo první dojde k lizu a ostatní si chvilku počkaj. Nicméně, když pustím něco jako

./get_batch.sh 1000 | wc -l & ./get_batch.sh 1000 | wc -l & ./get_batch.sh 1000 | wc -l &

přičemž get_batch je pouze nějaký to psql -qAtf...

tak první, co se spojí vrátí správně řádky, ostatní žádné řádky nevrátí. (samozřejmě jsem se ujistitl, že to není proto, že by zdrojová tabulka již byla prázdná, zas tak blbej nejsem). Osobně se domnívám, že nastane to, že si všechny vlákna naberou stejných n prvků, akorát první je zvládne smazat - vložit - vrátit.

Problém vyřešilo, když jsem to napral mezi

BEGIN WORK;
LOCK TABLE zpracovat IN EXCLUSIVE MODE;
LOCK TABLE hotovo IN EXCLUSIVE MODE;
...
COMMIT WORK;

Předpokládám, že správnější řešení by bylo použít 'for update ... skip locked'.

Tak mě napadlo, že se podělím o tuhle zkušenost a případně si nechám vyvrátit mylné doměnky od někoho, kdo ví víc.

Je;

--

Jelen

Starší čeledín datovýho chlíva

[Pavel Stehule]

čt 14. 7. 2022 v 7:44 odesílatel Jan Michálek <godzil...@gmail.com> napsal:

Zdar

Narazil jsem na zvláštní věc, která mě, přiznám se, dost překvapila.

Vysvětlím. Chtěl jsem si udělat jednoduchou frontu. Mám dvě jednosloupcové tabulky a dotaz, který vypadá nějak takto.

WITH del AS (
    DELETE FROM zpracovat
    WHERE kod IN (
        SELECT kod FROM zpracovat
        ORDER BY kod
        LIMIT :batch_size)
    RETURNING kod
), hotovo AS (
    INSERT hotovo
    SELECT * FROM del
)
SELECT * FROM del;

To mi přišlo jako vcelku robustní konstrukce, na který není co posrat, že je to jeden dotaz, tudíž atomický hotovo dvacet, postgre se o vše postará. Odbaví toho, kdo první dojde k lizu a ostatní si chvilku počkaj. Nicméně, když pustím něco jako

./get_batch.sh 1000 | wc -l & ./get_batch.sh 1000 | wc -l & ./get_batch.sh 1000 | wc -l &

přičemž get_batch je pouze nějaký to psql -qAtf...

tak první, co se spojí vrátí správně řádky, ostatní žádné řádky nevrátí. (samozřejmě jsem se ujistitl, že to není proto, že by zdrojová tabulka již byla prázdná, zas tak blbej nejsem). Osobně se domnívám, že nastane to, že si všechny vlákna naberou stejných n prvků, akorát první je zvládne smazat - vložit - vrátit.

juju

 

Problém vyřešilo, když jsem to napral mezi

BEGIN WORK;
LOCK TABLE zpracovat IN EXCLUSIVE MODE;
LOCK TABLE hotovo IN EXCLUSIVE MODE;
...
COMMIT WORK;

Předpokládám, že správnější řešení by bylo použít 'for update ... skip locked'.

tim explicitnim lockem jsi si vynutil serializaci. FOR UPDATE a SKIP LOCKED by mohl fungovat

Tak mě napadlo, že se podělím o tuhle zkušenost a případně si nechám vyvrátit mylné doměnky od někoho, kdo ví víc.

Je;

--

Jelen

Starší čeledín datovýho chlíva

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny „PostgreSQL-cz“ ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.
Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAAYBy8YQKPsQQ%3DT5EPocaz3P8Xk80f5ZBmXhrp3WQZcczs7UPg%40mail.gmail.com.

[Jan Marek]

Dobrý den,

prosím, jak by správně mělo tedy vypadat to zamykání dle schématu
FOR UPDATE a SKIP LOCKED?

Když vezmu ten dotaz:

WITH del AS (
DELETE FROM zpracovat
WHERE kod IN (
SELECT kod FROM zpracovat
ORDER BY kod
LIMIT :batch_size)
RETURNING kod
), hotovo AS (
INSERT hotovo
SELECT * FROM del
)
SELECT * FROM del;

Tak by to vypadalo třeba:

BEGIN work;

WITH del AS (
DELETE FROM zpracovat
WHERE kod IN (
SELECT kod FROM zpracovat
ORDER BY kod
LIMIT :batch_size

FOR UPDATE SKIP LOCKED)

RETURNING kod
), hotovo AS (
INSERT hotovo
SELECT * FROM del
)
SELECT * FROM del;

COMMIT work;

Je to tak?

Omlouvám se, zamykání jsem nikdy moc neřešil a tak ani nevím, jak
se to zapisuje...

Díky moc.

Zdraví
Honza Marek

Dne Čt, čec 14, 2022 at 09:02:39 CEST napsal Pavel Stehule:

> https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRBTtVf9iahwoLUxTa-MhHNga_P
> qMPUQOBDvVnp6B70J3w%40mail.gmail.com.

--
Ing. Jan Marek
University of South Bohemia
Academic Computer Centre
Phone: +420389032080
http://www.gnu.org/philosophy/no-word-attachments.cs.html

[Pavel Stehule]

čt 14. 7. 2022 v 9:44 odesílatel Jan Marek <jma...@jcu.cz> napsal:

ja si myslim, ze to je ok

stejne se ti serializuje v tom bashi

 

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/20220714074427.woheec2v3jrjdswx%40hazard.jcu.cz.

[Jan Michálek]

Akorát musí přijít to for update a skip locked před limit.

WITH del AS (
    DELETE FROM zpracovat

    WHERE katuze_kod IN (
        SELECT katuze_kod FROM zpracovat
        ORDER BY katuze_kod
        FOR UPDATE
        SKIP LOCKED
        LIMIT :batch_size)
    RETURNING katuze_kod
), hotovo AS (
    INSERT into hotovo (katuze_kod, worker)
    SELECT katuze_kod, :worker FROM del

)
SELECT * FROM del;

Udělal jsem to nakonec tak, to explicitní zamykání mi přišlo moc humpolácký a běží to dle očekávání.

Každopádně jsem předpokládal, že si to postgresql přečte nějak, než ten dotaz začne provádět a bude samo vědět, že se v rámci WITH maže a tudíž ty další vlákna pozdrží. Což ale objektivně nedává asi smysl, protože to defaultní chování by se zase mohlo hodit jinde.

Každopádně je to celkem užitečná finta a počítám, že to ještě nekde použiju.

V bashi mám

while [ "$(psql -U postgres -h localhost -d ${METADATADB} -qAtc 'SELECT count(*) FROM zpracovat;')" -gt 0 ];
do
     .....


done;

Chvíli jsem se s tím mazlil, než jsem byl spokojenej.

Našel jsem článek (kterej jsem si byl ale, blbec, včera línej přečíst)

https://www.crunchydata.com/blog/message-queuing-using-native-postgresql

a zjevně to jde udělat ještě elegantnějc s klauzulí USING (kterou taky neumím používat).

Je;

čt 14. 7. 2022 v 10:00 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

Chcete-li tuto diskusi zobrazit na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/CAFj8pRDUYsdUTrjAuW7cbiA%3DbAt79DFrU3%2BF7HaiG8-BrB%3DTTA%40mail.gmail.com.

[Jan Marek]

Dd,

Dne Čt, čec 14, 2022 at 10:36:07 CEST napsal Jan Michálek:

> Akorát musí přijít to for update a skip locked před limit.

tohle je zajímavý, protože v tom článku se objevuje tento
příklad:

-- backend 1
BEGIN;
DELETE FROM
queue_table
USING (
SELECT * FROM queue_table LIMIT 10 FOR UPDATE SKIP LOCKED
) q
WHERE q.id = queue_table.id RETURNING queue_table.*;

A tedy ten LIMIT je před FOR UPDATE SKIP LOCKED...

Zdraví
Honza Marek

[Jan Michálek]

Tak asi funguje obojí.

čt 14. 7. 2022 v 11:17 odesílatel Jan Marek <jma...@jcu.cz> napsal:

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny PostgreSQL-cz ve Skupinách Google.
Chcete-li zrušit odběr skupiny a přestat dostávat e‑maily ze skupiny, zašlete e-mail na adresu postgresql-c...@googlegroups.com.

Chcete-li zobrazit tuto diskusi na webu, navštivte https://groups.google.com/d/msgid/postgresql-cz/20220714091722.7f2ggneeofehsdye%40hazard.jcu.cz.