Izici ezihlukile zezinqubo zangaphakathi ze-PostgreSQL zivumela ukuthi isheshe kakhulu kwezinye izimo futhi "hhayi ngokushesha kakhulu" kwezinye. Namuhla sizogxila esibonelweni sakudala sokungqubuzana phakathi kokuthi i-DBMS isebenza kanjani nokuthi umthuthukisi wenzani ngayo - UKUBUYEKEZA ngokumelene nezimiso ze-MVCC.
Indaba emfushane evela :
Uma umugqa ulungiswa ngomyalo othi UPDATE, imisebenzi emibili iyenziwa ngempela: SUSA futhi FAKA. IN inguqulo yamanje yochungechunge I-xmax isethwe ilingana nenani lomsebenzi owenze UKUBUYISA. Bese kudalwa inguqulo entsha umugqa ofanayo; inani layo le-xmin lihambisana nevelu engu-xmax yenguqulo yangaphambilini.
Esikhathini esithile ngemva kokuqedwa kwalokhu okwenziwayo, inguqulo endala noma entsha, kuye ngokuthi COMMIT/ROOLBACK, izobonwa "dead" (dead tuples) uma edlula VACUUM ngokwetafula futhi kwasuswa.
Kodwa lokhu ngeke kwenzeke ngokushesha, kodwa izinkinga "nabafileyo" zingatholakala ngokushesha kakhulu - ngokuphindaphindiwe noma etafuleni elikhulu, futhi ngemva kwesikhashana uzohlangana nesimo esifanayo .
#1: Ngithanda Ukuyihambisa
Ake sithi indlela yakho isebenza kumqondo webhizinisi, futhi kungazelelwe ibona ukuthi kungadingeka ukubuyekeza inkambu ye-X kwelinye irekhodi:
UPDATE tbl SET X = <newX> WHERE pk = $1;Bese, njengoba ukubulawa kuqhubeka, kuvela ukuthi inkambu ye-Y kufanele futhi ibuyekezwe:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... bese kuthi futhi Z - kungani uchitha isikhathi ezintweni ezincane?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;Zingaki izinguqulo zaleli rekhodi manje esinazo kusizindalwazi? Yebo, izingcezu ezi-4! Kulokhu, okukodwa kuyasebenza, futhi oku-3 kuyodingeka kuhlanzwe ngemva kwakho [okuzenzakalelayo]VACUUM.
Ungakwenzi ngale ndlela! Sebenzisa ibuyekeza zonke izinkambu ngesicelo esisodwa - cishe njalo i-logic yendlela ingashintshwa kanje:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: Ukusebenzisa KUHLUKE KUSUKA, Luka!
Ngakho, ubusafuna (ngesikhathi kusetshenziswa umbhalo noma isiguquli, isibonelo). Futhi into enjengale indiza embhalweni:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Isicelo cishe kuleli fomu senzeka kaningi futhi cishe ngaso sonke isikhathi sokuba singagcwalisi inkambu entsha engenalutho, kodwa ukulungisa amaphutha athile kudatha. Ngesikhathi esifanayo, yena ngokwakhe ukunemba kwedatha ekhona akunakwa nhlobo - kodwa ngeze! Okungukuthi, irekhodi libhalwa kabusha, noma ngabe liqukethe lokho kanye obekufuna - kodwa kungani? Masiyilungise:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Abantu abaningi abazi ukuthi kukhona opharetha omuhle kangaka, ngakho-ke nali ishidi lokukopela IS DISTINCT FROM kanye nabanye opharetha abanengqondo ukusiza:
... nokuncane mayelana nokusebenza ku-complex ROW()-izinkulumo:
#3: Ngibona isithandwa sami ngoku... ukuvimba
ziyethulwa izinqubo ezimbili ezifanayo ezifanayo, ngayinye ezama ukumaka okufakiwe ukuthi “kuyaqhubeka”:
UPDATE tbl SET processing = TRUE WHERE pk = $1;Ngisho noma lezi zinqubo empeleni zenza izinto ngaphandle komunye nomunye, kodwa ngaphakathi kwe-ID efanayo, iklayenti lesibili "lizokhiywa" kulesi sicelo kuze kuqedwe umsebenzi wokuqala.
Isixazululo # 1: umsebenzi wehliselwe kowangaphambilini
Asivele sengeze futhi IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Kuleli fomu, isicelo sesibili ngeke sishintshe lutho ku-database, konke sekuvele njengoba kufanele kube - ngakho-ke, ukuvinjelwa ngeke kwenzeke. Okulandelayo, sicubungula iqiniso "lokungatholi" irekhodi ku-algorithm esetshenzisiwe.
Isixazululo # 2: izingidi zokweluleka
Isihloko esikhulu sendatshana ehlukile, ongafunda kuyo .
Isixazululo # 3: izingcingo ezingenangqondo
Kodwa yilokhu kanye okufanele kwenzeke kuwena umsebenzi kanyekanye onerekhodi elifanayo? Noma ingabe uphambane ngama-algorithms wokushayela ingqondo yebhizinisi ohlangothini lweklayenti, isibonelo? Futhi uma ucabanga ngakho? ..
Source: www.habr.com