Izvo zvinotaridzika zvemukati memaitiro ePostgreSQL zvinobvumira kuti imhanye zvakanyanya mune mamwe mamiriro uye "kwete zvakanyanya kukurumidza" mune mamwe. Nhasi tichatarisa pamuenzaniso wekare wekupokana pakati pekuti DBMS inoshanda sei uye izvo mugadziri anoita nazvo - UPDATE vs MVCC nheyo.
Nyaya pfupi kubva :
Kana mutsara uchinge wagadziridzwa ne UPDATE command, maviri ma operation anonyatso itwa: DELETE uye INSERT. IN shanduro yezvino yetambo xmax yakaiswa yakaenzana nenhamba yekutengeserana yakaitwa UPDATE. Zvadaro zvinogadzirwa shanduro itsva the same line; kukosha kwayo kwexmin kunopindirana nehukoshi hwexmax hweshanduro yapfuura.
Imwe nguva mushure mekutengeserana uku kwapera, shanduro yekare kana itsva, zvichienderana ne COMMIT/ROOLBACK, achazivikanwa "vakafa" (vakafa vana) pakupfuura VACUUM maererano netafura uye yakanatswa.
Asi izvi hazvizoitiki pakarepo, asi matambudziko ne "vakafa" anogona kuwanikwa nokukurumidza - nekudzokorora kana mutafura huru, uye zvishoma gare gare iwe uchasangana nemamiriro akafanana .
#1: Ndinoda Kuifambisa
Ngatiti nzira yako iri kushanda pane bhizinesi logic, uye kamwe kamwe inoziva kuti zvingave zvakakodzera kugadzirisa iyo X munda mune imwe rekodhi:
UPDATE tbl SET X = <newX> WHERE pk = $1;Zvino, sezvo kuuraya kuchienderera mberi, zvinoitika kuti iyo Y munda inofanirawo kuvandudzwa:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... uyezve Z - sei uchitambisa nguva pazvinhu zvisingakoshi?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Mangani mavhezheni erekodhi aya atinawo mudhatabhesi? Hongu, 4 zvidimbu! Pane izvi, chimwe chine basa, uye 3 chichafanira kucheneswa mushure mako ne [auto]VACUUM.
Usazviita nenzira iyi! Shandisa kugadzirisa minda yese muchikumbiro chimwe - inenge nguva dzose pfungwa yenzira inogona kuchinjwa seizvi:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Kushandisa KWAKASIYANA NA, Ruka!
Saka, wanga uchiri kuda (panguva yekushandiswa kwescript kana shanduko, semuenzaniso). Uye chimwe chinhu chakadai chinobhururuka mune script:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Chikumbiro chingangoita fomu iri chinoitika kazhinji uye kanenge nguva dzose kwete kuzadza munda mutsva usina chinhu, asi kugadzirisa zvimwe zvikanganiso mu data. Panguva imwe cheteyo, iye pachake iko kurongeka kwe data iripo haina kuverengerwa zvachose - asi pasina! Kureva kuti, rekodhi inonyorwazve, kunyangwe iine chaizvo zvaidiwa - asi nei? Ngatigadzirise:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Vanhu vazhinji havazive nezve kuvepo kweanoshamisa opareta, saka heino pepa rekubiridzira IS DISTINCT FROM uye mamwe mashandisi ane musoro kuti abatsire:
... uye zvishoma nezve maoperation pane yakaoma ROW()-mashoko:
#3: Ndinoziva mudiwa wangu ne... kuvhara
dziri kutangwa maitiro maviri akafanana akafanana, imwe neimwe inoedza kumaka kuti "iri kuitika":
UPDATE tbl SET processing = TRUE WHERE pk = $1;Kunyangwe izvi zvikaita zvinhu zvakazvimirira kubva kune mumwe nemumwe, asi mukati meiyo ID imwe chete, mutengi wechipiri "akavharwa" pachikumbiro ichi kusvika kutengeserana kwekutanga kwapera.
Mhinduro # 1: basa racho rakaderedzwa kusvika kune yapfuura
Ngatingozviwedzera zvakare IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Mune chimiro ichi, chikumbiro chechipiri hachizochinji chero chinhu mudhatabhesi, zvese zvatove sezvazvinofanira kunge zviri - saka, kuvharira hakuzoitika. Tevere, isu tinogadzirisa chokwadi che "kusawana" rekodhi mune yakashandiswa algorithm.
Mhinduro # 2: mazano ekukiya
Musoro mukuru wechinyorwa chakasiyana, umo iwe unogona kuverenga nezvazvo .
Mhinduro # 3: mapenzi anofona
Asi izvi ndizvo chaizvo zvinofanira kuitika kwauri kushanda panguva imwe chete nerekodhi imwechete? Kana kuti wakakanganisa nealgorithms yekufonera bhizinesi logic padivi revatengi, semuenzaniso? Uye kana iwe uchifunga nezvazvo? ..
Source: www.habr.com