Abubuwan da ke cikin hanyoyin ciki na PostgreSQL sun ba shi damar yin sauri a wasu yanayi kuma "ba da sauri sosai" a wasu ba. A yau za mu mai da hankali kan misali na yau da kullun na rikici tsakanin yadda DBMS ke aiki da abin da mai haɓaka ke yi da shi - UPDATE vs MVCC ka'idodin.
Takaitaccen labari daga :
Lokacin da aka gyaggyara layi ta hanyar UPDATE, ana aiwatar da ayyuka guda biyu: GAME da SA. IN halin yanzu sigar kirtani xmax an saita daidai da adadin ciniki wanda yayi UPDATE. Sannan aka halicce shi sabon salo layi daya; ƙimar xmin sa yayi daidai da ƙimar xmax na sigar da ta gabata.
Wani lokaci bayan an gama wannan ciniki, tsohon ko sabon sigar, ya danganta da COMMIT/ROOLBACK, za a gane "matattu" (matattu tuples) lokacin wucewa VACUUM bisa ga tebur da kuma share.
Amma wannan ba zai faru nan da nan, amma matsaloli tare da "matattu" za a iya samu da sauri - tare da maimaita ko a cikin babban tebur, kuma kadan daga baya za ku ci karo da irin wannan yanayin .
#1: Ina son Motsa shi
Bari mu ce hanyar ku tana aiki akan dabarun kasuwanci, kuma ba zato ba tsammani ya gane cewa zai zama dole don sabunta filin X a cikin wani rikodin:
UPDATE tbl SET X = <newX> WHERE pk = $1;Sa'an nan, yayin da aiwatar da aiwatarwa, ya zama cewa ya kamata a sabunta filin Y:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... sannan kuma Z - me yasa bata lokaci akan kananan yara?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Yanzu nau'ikan wannan rikodin nawa muke da su a cikin ma'ajin bayanai? da, 4 guda! Daga cikin waɗannan, ɗayan ya dace, kuma 3 dole ne a tsabtace ku ta [auto] VACUUM.
Kada ku yi haka! Amfani sabunta duk filayen cikin buƙatu ɗaya - Kusan koyaushe ana iya canza mahangar hanyar kamar haka:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: AMFANI DA BANBANTA DAGA, Luka!
Don haka, har yanzu kuna so (a lokacin amfani da rubutun ko mai canzawa, misali). Kuma wani abu kamar wannan yana tashi cikin rubutun:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Buƙatar kusan wannan fom yana faruwa sau da yawa kuma kusan koyaushe ba don cika sabon filin fanko ba, amma don gyara wasu kurakurai a cikin bayanan. A lokaci guda ita da kanta ba a la'akari da daidaitattun bayanan da ke akwai kwata-kwata - amma a banza! Wato, an sake rubuta rikodin, ko da ya ƙunshi ainihin abin da ake so - amma me ya sa? Mu gyara:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Mutane da yawa ba su san da wanzuwar irin wannan ma'aikaci mai ban mamaki ba, don haka ga takardar yaudara IS DISTINCT FROM da sauran ma'aikata masu ma'ana don taimakawa:
... kuma kadan game da ayyuka akan hadaddun ROW()-bayani:
#3: Na gane masoyita ta... tarewa
ana kaddamar da su matakai guda biyu iri ɗaya, kowanne daga cikinsu yana ƙoƙarin sanya alamar shigarwar cewa yana "ci gaba":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Ko da waɗannan hanyoyin suna yin abubuwa masu zaman kansu ba tare da juna ba, amma a cikin ID guda ɗaya, abokin ciniki na biyu za a "kulle" akan wannan buƙatar har sai an kammala ma'amala ta farko.
Magani # 1: an rage aikin zuwa na baya
Bari mu sake ƙara shi IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;A cikin wannan nau'i, buƙatun na biyu kawai ba zai canza komai a cikin bayanan ba, duk abin da ya riga ya kasance kamar yadda ya kamata - saboda haka, toshewa ba zai faru ba. Na gaba, muna aiwatar da gaskiyar "ba a gano" rikodin a cikin algorithm da aka yi amfani da shi ba.
Magani # 2: makullai nasiha
Babban jigo don labarin dabam, wanda zaku iya karantawa game da shi .
Magani # 3: wawan kira
Amma wannan shi ne ainihin abin da ya kamata ya faru da ku aiki na lokaci guda tare da rikodin iri ɗaya? Ko kun yi rikici da algorithms don kiran dabaru na kasuwanci a gefen abokin ciniki, misali? Kuma idan kun yi tunani game da shi?..
source: www.habr.com