ʻO nā hiʻohiʻona o nā mīkini kūloko o PostgreSQL e ʻae iā ia e wikiwiki loa i kekahi mau kūlana a "ʻaʻole wikiwiki loa" i nā mea ʻē aʻe. I kēia lā, e nānā mākou i kahi hiʻohiʻona maʻamau o kahi paio ma waena o ka hana ʻana o kahi DBMS a me ka hana a ka mea hoʻomohala - UPDATE vs nā loina MVCC.
Moʻolelo pōkole mai :
Ke hoʻololi ʻia kahi lālani e kahi kauoha UPDATE, hana ʻia ʻelua mau hana: DELETE a INSERT. IN mana o kēia manawa o ke kaula Ua hoʻonohonoho ʻia ʻo xmax e like me ka helu o ka hana i hana i ka UPDATE. A laila hana ʻia he mana hou ka laina like; ua like kona waiwai xmin me ka waiwai xmax o ka mana mua.
I kekahi manawa ma hope o ka pau ʻana o kēia hana, ka mana kahiko a i ʻole ka mana hou, e pili ana i COMMIT/ROOLBACK, e ʻike ʻia "make" (dead tuples) ke hala VACUUM e like me ka papaʻaina a hoʻomaʻemaʻe.
Akā ʻaʻole hiki koke kēia, akā hiki ke loaʻa koke nā pilikia me ka "make" - me ka hana hou a i ʻole i loko o kahi papaʻaina nui, a ma hope iki e hālāwai ʻoe i ke kūlana like .
#1: Makemake au e hoʻoneʻe iā ia
E ʻōlelo mākou e hana ana kāu ʻano hana ma ka loiloi ʻoihana, a ʻike koke ʻo ia he mea pono e hoʻonui i ke kahua X i kekahi moʻolelo:
UPDATE tbl SET X = <newX> WHERE pk = $1;A laila, i ka holomua ʻana o ka hoʻokō, ʻike ʻia e hoʻonui hou ʻia ka kahua Y:
UPDATE tbl SET Y = <newY> WHERE pk = $1;... a laila ʻo Z - no ke aha e hoʻopau manawa i nā mea liʻiliʻi?
UPDATE tbl SET Z = <newZ> WHERE pk = $1;ʻEhia mau mana o kēia moʻolelo i loaʻa iā mākou i kēia manawa i ka waihona? ʻAe, 4 ʻāpana! ʻO kēia mau mea, pili kekahi, a ʻo 3 e hoʻomaʻemaʻe ʻia ma hope o ʻoe e [auto] VACUUM.
Mai hana pela! Hoʻohana hoʻohou i nā kahua āpau i hoʻokahi noi - aneane hiki ke hoʻololi i ka loiloi o ke ʻano e like me kēia:
UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;#2: E hoʻohana iā IS DISTINCT FROM, Luke!
No laila, makemake mau ʻoe (i ka wā o ka hoʻohana ʻana i kahi palapala a mea hoʻololi paha, no ka laʻana). A lele kekahi mea e like me kēia i ka palapala:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;Loaʻa pinepine ʻia kahi noi e pili ana i kēia ʻano a ʻaneʻane ʻaʻole e hoʻopiha i kahi kahua hou ʻole, akā e hoʻoponopono i kekahi mau hewa i ka ʻikepili. I ka manawa like, ʻo ia iho ʻaʻole i manaʻo ʻia ka pololei o ka ʻikepili i loaʻa - akā, makehewa! ʻO ia hoʻi, ua kākau hou ʻia ka moʻolelo, ʻoiai ʻo ia ka mea i makemake ʻia - akā no ke aha? E hoʻoponopono kākou:
UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
ʻAʻole ʻike ka nui o ka poʻe i ke ola ʻana o kahi mea hoʻohana maikaʻi loa, no laila eia kahi palapala hoʻopunipuni IS DISTINCT FROM a me nā mea hoʻohana loea e kōkua:
... a he wahi liʻiliʻi e pili ana i nā hana ma ka complex ROW()-nā ʻōlelo:
#3: Hoʻomaopopo au i kaʻu ipo ma... ke kāohi ʻana
ke hoʻolana ʻia nei ʻelua kaʻina hana like like, e ho'āʻo ana kēlā me kēia e hōʻailona i ke komo ʻana "e holomua nei":
UPDATE tbl SET processing = TRUE WHERE pk = $1;ʻOiai inā e hana maoli kēia mau kaʻina hana i nā mea kūʻokoʻa mai kekahi i kekahi, akā i loko o ka ID hoʻokahi, e "paʻa" ka mea kūʻai ʻelua ma kēia noi a hiki i ka pau ʻana o ka hana mua.
Pāʻoihana # 1: ua hoemi ia ka hana i ka hana mua
E hoʻohui hou kākou IS DISTINCT FROM:
UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;Ma kēia palapala, ʻaʻole e hoʻololi ka lua o ka noi i kekahi mea i loko o ka waihona, ua pau nā mea āpau e like me ka mea e pono ai - no laila, ʻaʻole e hiki ke hoʻopaʻa ʻia. A laila, hana mākou i ka ʻoiaʻiʻo o ka "ʻike ʻole" i ka moʻolelo ma ka algorithm i noi ʻia.
Pāʻoihana # 2: nā laka aʻoaʻo
He kumuhana nui no kahi ʻatikala kaʻawale, kahi āu e heluhelu ai .
Pāʻoihana # 3: kahea lapuwale
Akā ʻo kēia ka mea pono e loaʻa iā ʻoe hana like me ka mooolelo hookahi? A i ʻole ʻoe i hana hewa me nā algorithms no ke kāhea ʻana i ka loiloi ʻoihana ma ka ʻaoʻao o ka mea kūʻai aku, no ka laʻana? A inā ʻoe e noʻonoʻo ai?..
Source: www.habr.com