PostgreSQL vastased mustrid: võitleme 'surnukehade' hordiga.

PostgreSQL-i sisemiste mehhanismide tööomadused võimaldavad tal olla ühes olukorras väga kiire ning teises „vähem kiire“. Täna peatume klassikalisele näitele konfliktist selle vahel, kuidas andmebaasi haldustarkvara töötab ja mida selle kallal arendaja teeb — UPDATE vs MVCC põhimõtted.

Lühidalt lugu suurepärasest artiklist:

Kui rida muudetakse UPDATE käsuga, viiakse tegelikult läbi kaks operatsiooni: DELETE ja INSERT. Praeguses rea versioonis seatakse xmax väärtuseks tehingu number, mis viis läbi UPDATE. Seejärel luuakse uus versioon sama rea uue versioon; xmin väärtus on sama, mis eelneva versiooni xmax.

Mõne aja pärast pärast selle tehingu lõpetamist tunnustatakse vana või uut versiooni, sõltuvalt COMMIT/ROLLBACK, „surnud“ (dead tuples) tabelist läbi minnes ja eemaldatakse. VACUUM Kuid see ei toimu kohe ning probleeme „surnud” ridadega võib kiiresti tekkida — korduvate või

Kuid see ei juhtu sugugi kohe, kuid probleemid "surnutega" võivad kiiresti tekkida — korduva või massiliste kirjeuuenduste korral suures tabelis, ja hiljem võidakse sattuda olukorda, kus ka VACUUM ei saa aidata..

#1: I Like To Move It

Kujutage ette, et teie äriloogika meetod töötab ja äkki mõistate, et on vaja uuendada mingis kirjes välja X:

UPDATE tbl SET X =  WHERE pk = $1;

Siis, käivitamise käigus selgub, et ka väli Y tuleks uuendada:

UPDATE tbl SET Y =  WHERE pk = $1;

… ja siis veel Z — milleks jännata?

UPDATE tbl SET Z =  WHERE pk = $1;

Kui palju versioone sellest kirjest on meil andmebaasis? Aha, 4 tükki! Nendest üks on ajakohane ning 3 tuleb [auto]VACUUM teie eest kustutada.

Ärge tehke nii! Kasutage kõigi väljade uuendamist ühe päringuga — peaaegu alati saab meetodi tööloogikat nii muuta:

UPDATE tbl SET X = , Y = , Z =  WHERE pk = $1;

#2: Use IS DISTINCT FROM, Luke!

Nii et te siiski soovisite uuendada palju-palju kirjeid tabelis (näiteks skripti või konverteri rakendamise käigus). Ja skripti lendab midagi sellist:

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

Umbes sellises vormis kohtab päringut üsna sageli ja peaaegu alati mitte uue tühja välja täitmiseks, vaid andmete väidete parandamiseks. Selle käigus olemasolevate andmete korrektus ei arvestata üldse — ja asjata! See tähendab, et kirje kirjutatakse üle, isegi kui seal oli täpselt see, mida soovisite — aga miks? Parandame:

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

Paljuski ei tea sellise suurepärase operaatori olemasolust, seega siin on kiirlähendus IS DISTINCT FROM ja teiste loogiliste operaatorite jaoks:

… ja natuke keeruliste ROW()-avaldiste kohta:

#3: А я милого узнаю по… блокировке

Käivituvad kaks identset paralleelset protsessi, millest igaüks püüab märkida, et kirje on "töös":

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

I isegi kui need protsessid teevad iseseisvaid asju, kuid ühe ID raames, selle päringuga lukustub teine klient, kuni esimene tehing on lõpule viidud.

Lahendus №1: ülesanne on vähendatud eelnevale

Lihtsalt lisame uuesti IS DISTINCT FROM:

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

Sellisel kujul teine päring ei muuda andmebaasis midagi, seal on juba "kõik korras" — seega lukustust ei toimu. Edasi töötleme kirje "mitteleidmist" rakendusalgoritmis.

Lahendus №2: soovituslikud lukud

Suur teema eraldi artiklile, kus saab lugeda rakendusmeetoditest ja „komplikatsioonidest” soovitatavate lukustuste puhul.

Lahendus №3: mõttetud kutsed

See peab kindlasti teie korraldama ühe ja sama kirje samal ajal töötamine? Или вы все-таки накосячили с алгоритмами вызовов бизнес-логики со стороны клиента, например? А если подумать?..

Allikas: habr.com