PostgreSQL Antipatterns: boj s hordami "mŕtvych"

Zvláštnosti vnútorných mechanizmov PostgreSQL umožňujú, aby bol v niektorých situáciách veľmi rýchly a v iných „nie veľmi rýchly“. Dnes sa zameriame na klasický príklad konfliktu medzi tým, ako funguje DBMS a čo s ním robí vývojár – Princípy UPDATE vs MVCC.

Krátky príbeh z skvelý článok:

Keď je riadok upravený príkazom UPDATE, v skutočnosti sa vykonajú dve operácie: DELETE a INSERT. IN aktuálna verzia reťazca xmax sa nastaví na rovnakú hodnotu ako číslo transakcie, ktorá vykonala UPDATE. Potom sa vytvorí nová verzia rovnaký riadok; jeho hodnota xmin sa zhoduje s hodnotou xmax predchádzajúcej verzie.

Nejaký čas po dokončení tejto transakcie stará alebo nová verzia, v závislosti od COMMIT/ROOLBACK, budú uznané "mŕtvy" (mŕtve n-tice) pri prejazde VACUUM podľa tabuľky a vyčistené.

To sa však nestane hneď, ale problémy s „mŕtvymi“ sa dajú získať veľmi rýchlo - opakovaným alebo hromadná aktualizácia záznamov vo veľkom stole a o niečo neskôr narazíte na rovnakú situáciu VAKUUM vám nepomôže.

#1: Rád to hýbem

Povedzme, že vaša metóda pracuje na obchodnej logike a zrazu si uvedomí, že by bolo potrebné aktualizovať pole X v nejakom zázname:

UPDATE tbl SET X = <newX> WHERE pk = $1;

Potom, ako bude vykonávanie pokračovať, sa ukáže, že by sa malo aktualizovať aj pole Y:

UPDATE tbl SET Y = <newY> WHERE pk = $1;

... a potom aj Z - prečo strácať čas maličkosťami?

UPDATE tbl SET Z = <newZ> WHERE pk = $1;

Koľko verzií tohto záznamu máme teraz v databáze? Áno, 4 kusy! Jeden z nich je relevantný a 3 budete musieť po vás vyčistiť pomocou [auto]VACUUM.

Nerobte to týmto spôsobom! Použite aktualizovať všetky polia v jednej žiadosti — takmer vždy je možné logiku metódy zmeniť takto:

UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;

#2: Použitie JE DISTINCT FROM, Luke!

Takže si ešte chcel aktualizovať veľa, veľa záznamov v tabuľke (napríklad pri použití skriptu alebo konvertora). A do scenára letí niečo takéto:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;

Požiadavka v približne tejto forme sa vyskytuje pomerne často a takmer vždy nie na vyplnenie prázdneho nového poľa, ale na opravu niektorých chýb v údajoch. Zároveň aj ona sama na správnosť existujúcich údajov sa vôbec neprihliada - ale márne! To znamená, že záznam je prepísaný, aj keď obsahoval presne to, čo sa chcelo – ale prečo? Poďme to napraviť:

UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;

Mnoho ľudí si neuvedomuje existenciu takého úžasného operátora, takže tu je cheat IS DISTINCT FROM a ďalšie logické operátory, ktoré vám pomôžu:

... a trochu o operáciách na komplexe ROW()- výrazy:

#3: Svojho miláčika spoznám podľa... blokovania

sa spúšťajú dva rovnaké paralelné procesy, z ktorých každý sa pokúša označiť záznam, že prebieha:

UPDATE tbl SET processing = TRUE WHERE pk = $1;

Aj keď tieto procesy skutočne robia veci nezávisle od seba, ale v rámci rovnakého ID, druhý klient bude na túto požiadavku „uzamknutý“, kým sa nedokončí prvá transakcia.

Riešenie #1: úloha sa zredukuje na predchádzajúcu

Ešte raz to pridáme IS DISTINCT FROM:

UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;

V tejto forme druhá požiadavka jednoducho v databáze nič nezmení, všetko je už tak, ako má byť – teda k blokovaniu nedôjde. Ďalej spracujeme skutočnosť „nenájdenia“ záznamu v použitom algoritme.

Riešenie #2: poradné zámky

Veľká téma na samostatný článok, v ktorom si môžete prečítať o metódy aplikácie a „hrabanie“ odporúčacieho blokovania.

Riešenie #3: hlúpe hovory

Ale presne toto by sa vám malo stať simultánna práca s rovnakým záznamom? Alebo ste sa napríklad pokazili s algoritmami volania obchodnej logiky na strane klienta? A ak sa nad tým zamyslíte?...

Zdroj: hab.com