Qhov peculiarities ntawm cov txheej txheem sab hauv ntawm PostgreSQL tso cai rau nws nrawm heev hauv qee qhov xwm txheej thiab "tsis nrawm" hauv lwm tus. Niaj hnub no peb yuav tsom mus rau qhov piv txwv classic ntawm qhov tsis sib haum xeeb ntawm qhov DBMS ua haujlwm li cas thiab tus tsim tawm ua li cas nrog nws - UPDATE vs MVCC cov ntsiab lus.
Zaj dab neeg luv luv los ntawm :
Thaum ib kab hloov kho los ntawm UPDATE cov lus txib, ob txoj haujlwm tau ua tiav: DELETE thiab INSERT. IN tam sim no version ntawm txoj hlua xmax tau teeb tsa sib npaug rau tus lej ntawm kev hloov pauv uas tau ua UPDATE. Ces nws yog tsim ib tug tshiab version tib kab; nws tus nqi xmin coincides nrog rau xmax tus nqi ntawm lub dhau los version.
Qee lub sij hawm tom qab qhov kev hloov pauv no ua tiav, qhov qub lossis tshiab version, nyob ntawm COMMIT/ROOLBACK, yuav raug lees paub "tuag" (tuag tuples) thaum hla VACUUM raws li lub rooj thiab tshem tawm.
Tab sis qhov no yuav tsis tshwm sim tam sim ntawd, tab sis teeb meem nrog "tuag" tuaj yeem tau sai heev - nrog rov ua dua lossis nyob rau hauv ib lub rooj loj, thiab me ntsis tom qab ntawd koj yuav ntsib qhov xwm txheej zoo ib yam .
#1: Kuv Nyiam Tsiv
Cia peb hais tias koj txoj kev ua haujlwm ntawm kev lag luam logic, thiab mam li nco dheev nws paub tias nws yuav tsim nyog los hloov kho X teb hauv qee cov ntaub ntawv:
UPDATE tbl SET X = <newX> WHERE pk = $1;Tom qab ntawd, raws li kev ua tiav zuj zus, nws hloov tawm tias Y teb yuav tsum tau hloov kho:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... Thiab tom qab ntawd kuj Z - yog vim li cas nkim sij hawm ntawm trifles?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Tam sim no peb muaj pes tsawg versions ntawm cov ntaub ntawv no nyob rau hauv lub database? yog, 4 pc! Ntawm cov no, ib qho tseem ceeb, thiab 3 yuav tsum tau ntxuav tom qab koj los ntawm [auto] VACUUM.
Tsis txhob ua li no! Siv hloov kho tag nrho cov teb hauv ib qhov kev thov - yuav luag txhua lub logic ntawm txoj kev tuaj yeem hloov tau zoo li no:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Siv IS DISTINCT LOS NTAWM, Lukas!
Yog li, koj tseem xav tau (piv txwv li thaum siv ib tsab ntawv lossis converter, piv txwv). Thiab ib yam dab tsi zoo li no ya mus rau hauv tsab ntawv:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Ib qho kev thov nyob rau hauv kwv yees li daim foos no tshwm sim ntau zaus thiab yuav luag tsis tas yuav sau ib daim teb tshiab khoob, tab sis los kho qee qhov yuam kev hauv cov ntaub ntawv. Nyob rau tib lub sijhawm, nws tus kheej qhov tseeb ntawm cov ntaub ntawv uas twb muaj lawm tsis tau muab rau hauv tus account txhua - tab sis nyob rau hauv vain! Ntawd yog, cov ntaub ntawv tau sau dua tshiab, txawm tias nws muaj raws nraim qhov xav tau - tab sis vim li cas? Cia peb kho nws:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Ntau tus neeg tsis paub txog lub neej ntawm tus neeg teb xov tooj zoo li no, yog li ntawm no yog daim ntawv dag rau IS DISTINCT FROM thiab lwm cov tswv yim tsim nyog los pab:
... thiab me ntsis txog kev ua haujlwm ntawm complex ROW()- lus hais:
#3: Kuv paub kuv tus hlub los ntawm... thaiv
Yog launched ob txheej txheem sib npaug sib npaug, txhua tus uas sim kos qhov nkag tias nws yog "kev ua tiav":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Txawm hais tias cov txheej txheem no ua tau yam tsis muaj kev ywj pheej ntawm ib leeg, tab sis nyob rau hauv tib tus ID, tus neeg siv khoom thib ob yuav raug "xauv" ntawm qhov kev thov no kom txog thaum thawj qhov kev sib pauv tiav.
Kev daws #1: txoj hauj lwm raug txo mus rau yav dhau los
Cia peb rov ntxiv dua IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Hauv daim ntawv no, qhov kev thov thib ob tsuas yog yuav tsis hloov dab tsi hauv cov ntaub ntawv, txhua yam yog twb raws li nws yuav tsum tau ua - yog li ntawd, kev thaiv yuav tsis tshwm sim. Tom ntej no, peb ua qhov tseeb ntawm "tsis nrhiav" cov ntaub ntawv nyob rau hauv qhov kev siv algorithm.
Kev daws #2: advisory locks
Ib lub ntsiab lus loj rau ib tsab xov xwm cais, uas koj tuaj yeem nyeem txog .
Kev daws #3: ruam hu
Tab sis qhov no yog qhov yuav tsum tshwm sim rau koj ib txhij ua hauj lwm nrog tib cov ntaub ntawv? Los yog koj puas mess nrog cov algorithms hu rau kev lag luam logic ntawm tus neeg siv khoom, piv txwv li? Thiab yog tias koj xav txog nws? ..
Tau qhov twg los: www.hab.com