MySQL: read once

[hknmtt]

Mam MySQL/MariaDB tabulku, ktora ma povedzme tri stlpce:
- prvy stlpec je integer, ktory je auto-increment
- druhy stlpec je unikatne a raditelne(sortovatelne) id odvodene od nejakej casovej znacky, ktore je vytvorene v aplikacii, takze je mozne chronologicky podla nej zoradit zaznamy
- treti stlpec su nejake data vo forme blobu

Potrebujem zabezpecit, ze kazdy riadok sa precita nejakym konzumentom iba jeden krat.

Problem: konzument ma read-only transakciu a precita si vsetky zaznamy, ked dobehne na koniec, ulozi si posledne id, nasledne opakuje proces s novou read-only transakciou, s tym ze pouzije posledne spracovane id ako podmienku pre vratenie novsich vysledkov. Ak nie su nove zaznamy, tak spi nejaky cas a proces znovu opakuje.

Teraz mam dve write transakcie, jedna zacne a vytvori nejake zaznamy. Druha write transakcia zacne po nej a tiez vytvori nejake zaznamy. Avsak druha transakcia skonci skor a commitne zmeny. Nasledne skonci prva transakcia a commitne zmeny.

Problem, ktory tu vznika je ten, ze prva transakcia bude mat mensi auto-increment integer(prvy stlpec), pripadne aj id ktore sa generuje v aplikacii(druhy stlpec), nez druha transakcia, takze chronologicky je to v poriadku. Lenze tym ze druha transakcia commitla skor, tak sa do DB zapisu riadky v "nespravnom" poradi. Co je bezne v poriadku, nic sa nedeje, mame stale unikatne idcka pre prvy aj druhy stlpec a indexy na radenie.

Lenze nas konzument ma teraz problem, ze preskocil zaznamy prvej transakcie, pretoze cital zaznamy, dobehol na koniec, spal, druha transakcia commitla zmeny, konzument spracoval nove zaznamy a ulozil si posledne id(ci uz auto-increment prveho stlpca, alebo aplikacne id z druheho stlpca), nasledne commitla prva transakcia, lenze mala mensie auto-increment id ako aj id z aplikacie, takze nas konzument tieto zaznamy nevidi.

Takze by ma zaujimalo, ako je mozne takyto problem riesit bez toho, aby sa kazdy riadok oznacil ako uz precitany a teda konzument tak nepreskocil zaznamy kvoli poradiu v ktorom su transakcie commitnute?

[Reklama]

[Kit]

Mam MySQL/MariaDB tabulku, ktora ma povedzme tri stlpce:
Potrebujem zabezpecit, ze kazdy riadok sa precita nejakym konzumentom iba jeden krat.

V podstatě potřebuješ implementovat frontu. MySQL na to není zrovna vhodná. Pokud bys použil třeba Redis, tak ten to umí už v základu.

V MySQL budeš potřebovat ještě čtvrtý sloupec se stavem, např.

Kód: [Vybrat]

ENUM('čeká', 'zpracovává se', 'hotovo') DEFAULT 'čeká', který budeš průběžně modifikovat a výběr budeš dělat podle něho.

[hknmtt]

Mam MySQL/MariaDB tabulku, ktora ma povedzme tri stlpce:
Potrebujem zabezpecit, ze kazdy riadok sa precita nejakym konzumentom iba jeden krat.

V podstatě potřebuješ implementovat frontu. MySQL na to není zrovna vhodná. Pokud bys použil třeba Redis, tak ten to umí už v základu.

V MySQL budeš potřebovat ještě čtvrtý sloupec se stavem, např.

Kód: [Vybrat]

ENUM('čeká', 'zpracovává se', 'hotovo') DEFAULT 'čeká', který budeš průběžně modifikovat a výběr budeš dělat podle něho.

Ono pouzit stlpec na oznacenie precitania, ako som pisal hore, je ako-tak v poriadku...ak mam len jedneho konzumenta. Chcel by som to poriesit tak, aby to mohlo fungovat pre viacerych.

Nemozem v podstate ani urobit nejaku dodatocnu tabulku, do ktorej by som kopiroval idcka novych zaznamov, pretoze mi vznika uplne totozny problem kvoli tomu, ze zaznamy nie su v absolutnom poradi. Drzat zoznam spracovanych zaznamov v pameti pre kazdeho konzumenta je dost nerealne. Cize ani to nie je cesta. Mozem pripadne urobit opak a teda pre kazdeho konzumenta kopirovat do vedlajsej tabulky id spracovanych zaznamov a proste filtrovat podla toho.

[hknmtt]

Vo svojej podstate, jedine co realne potrebujem je, aby databaza mala nejaky auto-increment, ktory sa zapise az pocas commitu, takze nemoze vzniknut out-of-order insert zaznamov a to by vyriesilo cely moj problem.

[Filip Jirsák]

Když budete mít víc konzumentů, stejně nebudete chtít, aby se o záznamy prali. Takže ty záznamy rozdělíte do oddílů a každý konzument bude zpracovávat svůj oddíl. Třeba na základě ID záznamu modulo počet konzumentů.

Vo svojej podstate, jedine co realne potrebujem je, aby databaza mala nejaky auto-increment, ktory sa zapise az pocas commitu, takze nemoze vzniknut out-of-order insert zaznamov a to by vyriesilo cely moj problem.

To je přesně něco, co nejde, pokud nepoužijete úroveň izolace transakcí SERIALIZABLE, tedy že se bude provádět jedna transakce za druhou, nepoběží paralelně vedle sebe.

[Reklama]

[Michal Šmucr]

Asi bych se snažil vylepšit tu logiku tak, aby z toho byla normální fronta s joby, kdy budu z consumera aktualizovat stav a nebudu spoléhat na nějaké sekvenční číslování. Tzn. měl bych tam sloupec se stavem, nevím teď např. smallint (0 - pending, 1 - processing, 2 - done, 3 - failed), co bych aktualizoval podle potřeby. Plus případně nějaká další metadata (který worker/consumer to udělal atp.).
MySQL 8, případně teď jsem se díval, už že i MariaDB 10.6 by měla umět SELECT s použitím FOR UPDATE SKIP LOCKED.
FOR UPDATE zařídí, že když nějaký consumer přečte tabulku, a vrátí mu to jeden řádek (LIMIT 1) co je pending, tak ten řádek také zároveň zamkne než skončí jeho transakce (změní sloupec stav).
To SKIP LOCKED zas zajistí, že pokud už bude řádek zamčený transakcí z jiného consumeru, tak se přeskočí.

[hknmtt]

Ja neriesim ziadnu frontu ani vzajomne exkluzivnych konzumentov. Zase to tu ide to offtopicu, ako vzdy, takze tomu chcem zabranit co najskor. Jedine, co potrebujem, je zabezpecit citanie zaznamov v konzistentnom poradi bez preskocenia v pripade, ze transakcie comitnu zmeny v odlisnom poradi nez v ktorom boli zacate.

[hknmtt]

Uz som to vyriesil. Ak by niekto riesil nieco podobne, tak je nutne pouzit id generovane v aplikacii, ktore su unikatne ale zoraditelne podla casu, respektive monotonicky. Vzdy ked sa vytvori nova write transakcia, treba pre nu vygenerovat nove id a ulozit si zoznam aktivnych transakcii. Akekolvek data budu v ramci tejto transakcie vyprodukovane, budu mat vzdy vecsie id nez je id samotnej transakcie, kedze cas tyka kupredu a id sa budu posuvat dopredu taktiez. Konzument jedine co musi urobit je robit range select kde pouzije svoje posledne spracovane id ako spodnu hranicu a id najmensej aktualne aktivnej transakcie ako horny limit. Ak transakcia comitne, pouzije sa id nasledovnej najnizsej transakcie ako horny limit. Ak sa transakcia revertne, rovnaky princip - na dalsiu najmensiu alebo az po koniec zaznamov. Transakcie mozu byt comitnute v akomkolvek poradi, hlavne je vzdy pouzit horny limit najnizsej transakcie, bez ohladu na jej ne/uspech, a takto rad za radom postupne citat viac a viac novych zaznamov.

[Filip Jirsák]

Zase to tu ide to offtopicu, ako vzdy

Je dobře, že jste si toho konečně všimnul. Teď se ještě zamyslete nad tím, zda to náhodou není tím, jak kladete dotazy. Když se to jiným neděje, skoro to vypadá, jako by to bylo vámi a vašimi nesrozumitelnými dotazy.

To vaše řešení je divné (tím, že generování ID přesunete z databáze do aplikace se těžko může něco změnit – pokud ta aplikace nesplňuje nějaké podmínky, které pro databázi neplatí), vychází z předpokladů, které jste v zadání nenapsal (takže těžko může někdo takové řešení navrhnout – ale to je stále dokola váš problém, že neumíte popsat, jaký problém řešíte). A věty jako „Transakcie mozu byt comitnute v akomkolvek poradi, hlavne je vzdy pouzit horny limit najnizsej transakcie, bez ohladu na jej ne/uspech“ ve čtenářích vzbuzují otázky, zda víte, co je to commit transakce, zda víte zejména co je to rollback transakce a kde přijde čtenář k ID neúspěšné, tedy rollbacknuté transakce.

[Sox37]

Kromě transakcí si nastudujte také zámky. InnoDB podporuje i tzv. gap locks, kterými jedna transakce může zabránit druhé vkládat řádky s určitými hodnotami indexu. Ve vašem případě by tak transakce s menšími hodnotami mohla zamknout rozsah za jejím posledním timestampem a transakce s vyšsími timestampy by tak musela čekat.

https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

[MalyTomi]

a co takto to urobit, ako sa bezne robi synchro? pridas stlpec s datumom a casom vlozenia zaznamu a namiesto pamatania si idcka, si len zapamatas najvyssi datum. A pojdes od neho vyssie. Takto mozu byt viaceri consumeri a kazdy si moze ist od ineho zaznamu, a neriesis kto kedy ten zaznam vlozil. Nemusis ani riesit letny/zimny cas, ak ten datum bude GMT, a ani rozdielne casy na roznych strojoch, kedze sa kontrolovat bude stale voci tomu, kde je db.

[Sox37]

MalyTomi: To je to, co má OP aktuálně. Problém je v tom, že má více producentů a do DB mohou být vloženy starší záznamy až po tom, co tam jsou už nějaké novější.

Podle mě by ani to moje řešení se zámky nefungovalo, musel by se spíš použít nějaký timeout, aby byl dostatek času pro vložení starších záznamů. Nebo případně aplikaci upravit tak, aby nebylo potřeba záznamy zpracovávat chronologicky.

[Tomas-T]

Nevím, zda by to nekolidovalo s nějakými jinými neuvedenými požadavky, ale jednoduché by bylo nenačítat záznamy až do aktuálního času, ale třeba minimálně minutu staré - pokud lze učinit předpoklad, že mezi takto starými záznamy žádná ještě nedokončená transakce už neběží, takže tam nic nepřibyde.

[Filip Jirsák]

Nevím, zda by to nekolidovalo s nějakými jinými neuvedenými požadavky, ale jednoduché by bylo nenačítat záznamy až do aktuálního času, ale třeba minimálně minutu staré - pokud lze učinit předpoklad, že mezi takto starými záznamy žádná ještě nedokončená transakce už neběží, takže tam nic nepřibyde.

Přesně tak. Vzhledem k tomu, že podle zadání hknmtt nevadí, když se nějaké záznamy zpracují dvakrát, a hknmtt zjevně moc netuší, jak to v té aplikaci vlastně vypadá s transakcemi, je nejspolehlivější metoda udělat při zpracování dostatečně velký překryv – větší, než je maximální možná délka transakce.

Druhá věc je, že hknmtt psal, že už si teď označuje záznamy jako zpracované, a to, co řeší, je jen optimalizace. Přičemž vůbec nevíme, proč to chce optimalizovat, zda je tam nějaký výkonnostní problém, a pokud ano, tak jaký. Takže vzhledem k tomu, že hknmtt nedokáže popsat, jaké jsou tam předpoklady, ani jak to v aplikaci funguje s transakcemi, ani proč to che optimalizovat, je nejlepší doporučení, které můžeme dát – nesahat na to a nechat to tak, jak to je.

[NCC1701E]

Mam MySQL/MariaDB tabulku, ktora ma povedzme tri stlpce:
Potrebujem zabezpecit, ze kazdy riadok sa precita nejakym konzumentom iba jeden krat.

V podstatě potřebuješ implementovat frontu. MySQL na to není zrovna vhodná. Pokud bys použil třeba Redis, tak ten to umí už v základu.

V MySQL budeš potřebovat ještě čtvrtý sloupec se stavem, např.

Kód: [Vybrat]

ENUM('čeká', 'zpracovává se', 'hotovo') DEFAULT 'čeká', který budeš průběžně modifikovat a výběr budeš dělat podle něho.

Ono pouzit stlpec na oznacenie precitania, ako som pisal hore, je ako-tak v poriadku...ak mam len jedneho konzumenta. Chcel by som to poriesit tak, aby to mohlo fungovat pre viacerych.

Pro víc konzumentů to může fungovat tak, že každý konzument bude mít svoje číslo a vždy po zpracování zvedne příznak (zvláštní sloupec) o jedničku. Pak je nutné si pohlídat aby v ID konzumentů nebyla díra. Samozřejmě potom zpracování konzumenty bude sériové, ale dotaz bude naprosto jednoznačný. Paralelizace by se dala udělat nahrazením příznaku bitmapou (tam bych se bál paralelních updatů) nebo pomocí pole (podobný problém s updaty a režie navíc). Na druhou stranu, u pole které bude mít na začátku všechny konzumenty a po zpracování se bude ubírat by režie nebyla tak strašná a update je taky řešitelný.