Lintho tse ikhethang tsa mekhoa ea ka hare ea PostgreSQL e lumella hore e potlake haholo maemong a mang le "eseng ka potlako" ho tse ling. Kajeno re tla shebana le mohlala oa khale oa khohlano lipakeng tsa hore na DBMS e sebetsa joang le seo mohlahlami a se etsang ka eona - UPDATE vs MVCC melaoana.
Pale e khuts'oane e tsoang ho :
Ha mola o fetotsoe ka taelo ea UPDATE, ho hlile ho etsoa opereishene tse peli: DELETE le INSERT. IN mofuta oa hona joale oa khoele xmax e behiloe ho lekana le palo ea khoebo e entseng UPDATE. Joale hoa bōptjoa phetolelo e ncha mola o tšoanang; boleng ba eona ba xmin bo tsamaisana le boleng ba xmax ba mofuta o fetileng.
Nako e itseng ka mor'a hore thekiso ena e phethoe, mofuta oa khale kapa o mocha, ho itšetlehile ka COMMIT/ROOLBACK, e tla tsejoa "ba shoeleng" (li-tuples tse shoeleng) ha o feta VACUUM ho ea ka tafole le ho hloekisoa.
Empa sena se ke ke sa etsahala hang-hang, empa mathata a "bafu" a ka fumanoa ka potlako haholo - ka makhetlo a mangata kapa tafoleng e kholo, 'me hamorao u tla kopana le boemo bo tšoanang .
#1: Ke Rata Ho E Tsamaisa
Ha re re mokhoa oa hau o sebetsa molemong oa khoebo, mme ka tšohanyetso oa hlokomela hore ho tla hlokahala ho nchafatsa tšimo ea X rekotong e itseng:
UPDATE tbl SET X = <newX> WHERE pk = $1;Joale, ha ts'ebetso e ntse e tsoela pele, hoa fumaneha hore lebala la Y le lona le lokela ho ntlafatsoa:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... mme hape le Z - hobaneng o senya nako ka dintho tse nyane?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Hona joale re na le liphetolelo tse kae tsa rekoto ee polokelong ea litaba? Ee, likotoana tse 4! Ho tsena, e le 'ngoe ea bohlokoa, 'me tse 3 li tla tlameha ho hloekisoa ka mor'a hau ka [auto]VACUUM.
U se ke ua e etsa ka tsela ena! Sebelisa ho nchafatsa likarolo tsohle ka kopo e le 'ngoe - hoo e batlang e le kamehla mohopolo oa mokhoa ona o ka fetoloa ka tsela ena:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Tšebeliso E FAPANENG LE, Luka!
Kahoo, u ne u ntse u batla (nakong ea tšebeliso ea script kapa converter, mohlala). 'Me ntho e kang ena e fofa ka har'a script:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Kopo hoo e batlang e le foromong ena e etsahala hangata mme hoo e batlang e le hore e se ke ea tlatsa tšimo e ncha e se nang letho, empa ho lokisa liphoso tse ling ho data. Ka nako e tšoanang, le eena ka boeena ho nepahala ha data e teng ha ho nkoe ho hang - empa ke lefeela! Ke hore tlaleho e ngoloa bocha, leha e ne e fupere hantle se neng se batloa - empa hobaneng? Ha re e lokise:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Batho ba bangata ha ba tsebe ka boteng ba opareitara e ntle joalo, ka hona, leqephe la cheat ke lena IS DISTINCT FROM le basebelisi ba bang ba bohlale ho thusa:
... le hanyane mabapi le ts'ebetso e rarahaneng ROW()-lipolelo:
#3: Ke lemoha moratuoa oa ka ka ... thibela
li ntse li qalisoa mekhoa e 'meli e tšoanang e tšoanang, e 'ngoe le e 'ngoe ea eona e lekang ho tšoaea se kentsoeng hore se "tsoela pele":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Le haeba lits'ebetso tsena li hlile li etsa lintho tse ikemetseng, empa ka har'a ID e le 'ngoe, moreki oa bobeli o tla " notleloa" kopong ena ho fihlela transaction ea pele e phethiloe.
Tharollo #1: mosebetsi o fokotsehile ho ea pele
Ha re e kenye hape IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Ka foromo ena, kopo ea bobeli e ke ke ea fetola letho ho database, ntho e 'ngoe le e' ngoe e se e ntse e le kamoo e lokelang ho ba kateng - ka hona, ho thibela ho ke ke ha etsahala. Ka mor'a moo, re sebetsana le 'nete ea "ho se fumane" rekoto ho algorithm e sebelisitsoeng.
Tharollo #2: liloko tsa likeletso
Sehlooho se seholo bakeng sa sengoloa se arohaneng, seo u ka balang ka sona .
Tharollo #3: mehala e hlokang kelello
Empa sena ke sona hantle se lokelang ho etsahala ho uena mosebetsi ka nako e le nngwe ka rekoto e tshwanang? Kapa na u ile ua senya le li-algorithms tsa ho bitsa logic ea khoebo ka lehlakoreng la bareki, mohlala? 'Me haeba u nahana ka eona? ..
Source: www.habr.com