Det særegne ved de interne mekanismene til PostgreSQL gjør at den er veldig rask i noen situasjoner og "ikke veldig rask" i andre. I dag skal vi fokusere på et klassisk eksempel på en konflikt mellom hvordan en DBMS fungerer og hva utvikleren gjør med den - OPPDATERING vs MVCC-prinsipper.
Kort historie fra :
Når en rad modifiseres av en UPDATE-kommando, utføres faktisk to operasjoner: DELETE og INSERT. I gjeldende versjon av strengen xmax settes lik nummeret på transaksjonen som utførte OPPDATERING. Så er det skapt en ny versjon samme linje; xmin-verdien sammenfaller med xmax-verdien til forrige versjon.
En tid etter at denne transaksjonen er fullført, vil den gamle eller nye versjonen, avhengig av COMMIT/ROOLBACK, vil bli gjenkjent "død" (døde tupler) når du passerer VACUUM i henhold til tabellen og ryddet.
Men dette vil ikke skje med en gang, men problemer med "døde" kan erverves veldig raskt - med gjentatte eller i et stort bord, og litt senere vil du møte samme situasjon .
#1: Jeg liker å flytte den
La oss si at metoden din jobber med forretningslogikk, og plutselig innser den at det ville være nødvendig å oppdatere X-feltet i en eller annen post:
UPDATE tbl SET X = <newX> WHERE pk = $1;Så, etter hvert som utførelsen skrider frem, viser det seg at Y-feltet også bør oppdateres:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... og så også Z - hvorfor kaste bort tid på bagateller?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Hvor mange versjoner av denne posten har vi nå i databasen? Jepp, 4 stk! Av disse er en aktuell, og 3 må ryddes opp etter deg med [auto]VACUUM.
Ikke gjør det på denne måten! Bruk oppdatere alle feltene i én forespørsel — nesten alltid logikken til metoden kan endres slik:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Bruk ER forskjellig fra, Luke!
Så du ville fortsatt (under bruk av et skript eller konverter, for eksempel). Og noe sånt som dette flyr inn i manuset:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;En forespørsel i omtrent dette skjemaet forekommer ganske ofte og nesten alltid om ikke å fylle ut et tomt nytt felt, men å rette opp noen feil i dataene. Samtidig er hun selv riktigheten av eksisterende data er ikke tatt i betraktning i det hele tatt - men til ingen nytte! Det vil si at plata skrives om, selv om den inneholdt akkurat det som var ønsket – men hvorfor? La oss fikse det:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Mange mennesker er ikke klar over eksistensen av en så fantastisk operatør, så her er et jukseark på IS DISTINCT FROM og andre logiske operatorer for å hjelpe:
... og litt om operasjoner på kompleks ROW()-uttrykkene:
#3: Jeg kjenner igjen kjæresten min ved å... blokkere
blir lansert to identiske parallelle prosesser, som hver prøver å merke oppføringen at den er "pågår":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Selv om disse prosessene faktisk gjør ting uavhengig av hverandre, men innenfor samme ID, vil den andre klienten bli "låst" på denne forespørselen til den første transaksjonen er fullført.
Løsning #1: oppgaven er redusert til den forrige
La oss bare legge det til igjen IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;I dette skjemaet vil den andre forespørselen ganske enkelt ikke endre noe i databasen, alt er allerede som det skal være - derfor vil blokkering ikke forekomme. Deretter behandler vi det faktum å "ikke finne" posten i den anvendte algoritmen.
Løsning #2: rådgivende låser
Et stort tema for en egen artikkel, som du kan lese om .
Løsning #3: dumme samtaler
Men det er akkurat dette som bør skje med deg samtidig arbeid med samme rekord? Eller rotet du med algoritmene for å kalle forretningslogikk på klientsiden, for eksempel? Og hvis du tenker deg om?..
Kilde: www.habr.com