Transcript of Bruce Momjian's 2020 talk "Wehe i ka Postgres Lock Manager".
(E hoʻomaopopo: Hiki ke loaʻa nā nīnau SQL a pau mai kēia loulou: )
Aloha! He mea maikaʻi ke noho hou ma Rusia. E kala mai ʻaʻole hiki iaʻu ke hele mai i ka makahiki i hala, akā i kēia makahiki he mau hoʻolālā nui ko māua ʻo Ivan. Manaʻolana wau e hele pinepine ma ʻaneʻi. Aloha au e hele mai i Rusia. E kipa au iā Tyumen, Tver. Hauʻoli nui wau i ka hiki iaʻu ke kipa aku i kēia mau kūlanakauhale.
ʻO Bruce Momjian koʻu inoa. Hana wau ma EnterpriseDB a ua hana pū me Postgres no nā makahiki he 23. Noho au ma Philadelphia, USA. Hele au ma kahi o 90 mau lā o ka makahiki. A hele au ma kahi o 40 mau ʻaha kūkā. ʻO koʻu , aia nā kiʻi paheʻe aʻu e hōʻike aku nei iā ʻoe. No laila, ma hope o ka hālāwai kūkā hiki iā ʻoe ke hoʻoiho iā lākou mai kaʻu pūnaewele pilikino. Aia ma kahi o 30 mau hōʻike. Aia kekahi mau wikiō a me ka helu nui o nā moʻomanaʻo moʻomanaʻo, ʻoi aku ma mua o 500. He kumu ʻike kūpono kēia. A inā makemake ʻoe i kēia mea, a laila kono wau iā ʻoe e hoʻohana.
He kumu au, he polopeka ma mua o koʻu hoʻomaka ʻana e hana me Postgres. A hauʻoli nui wau i ka hiki iaʻu ke haʻi aku iā ʻoe i kaʻu mea e haʻi aku ai iā ʻoe. ʻO kēia kekahi o kaʻu mau hōʻikeʻike hoihoi loa. A he 110 kiʻi paheʻe kēia hōʻike. E hoʻomaka mākou e kamaʻilio me nā mea maʻalahi, a ma ka hopena e lilo ka hōʻike i mea paʻakikī, a lilo i mea paʻakikī.
He kamaʻilio maikaʻi ʻole kēia. ʻAʻole ʻo ka pale ʻana ke kumuhana kaulana loa. Makemake mākou e nalowale kēia ma kahi. Ua like ia me ka hele ʻana i ke kauka niho.
- He pilikia ka laka no ka nui o ka poʻe e hana ana i ka ʻikepili a he nui nā kaʻina hana e holo ana i ka manawa like. Pono lākou e ālai. ʻO ia hoʻi, i kēia lā e hāʻawi wau iā ʻoe i ka ʻike kumu no ka pale ʻana.
- Nā ID Kūʻai. He ʻāpana ʻoluʻolu kēia o ka hōʻike, akā pono lākou e hoʻomaopopo.
- A laila e kamaʻilio mākou e pili ana i nā ʻano o ka pale ʻana. He ʻāpana mechanical kēia.
- A ma lalo nei e hāʻawi mākou i kekahi mau hiʻohiʻona o ka pale ʻana. A paʻakikī loa ka hoʻomaopopo ʻana.
E kamaʻilio kākou e pili ana i ka pāpā ʻana.
Paʻakikī loa kā mākou ʻōlelo. ʻEhia o ʻoukou i ʻike no hea mai kēia paukū? ʻElua kanaka. No ka pāʻani i kapa ʻia ʻo Colossal Cave Adventure. He pāʻani kamepiula pili kikokikona i nā makahiki 80, manaʻo wau. Ma laila ʻoe e hele ai i loko o ke ana, i loko o kahi labyrinth, a ua loli ke kikokikona, akā ua like ka ʻike i kēlā me kēia manawa. ʻO ia koʻu hoʻomanaʻo ʻana i kēia pāʻani.
A ma ʻaneʻi mākou e ʻike ai i ka inoa o nā laka i hiki mai iā mākou mai Oracle. Hoʻohana mākou iā lākou.
Eia mākou e ʻike ai i nā huaʻōlelo e huikau iaʻu. No ka laʻana, SHARE UPDATE ECXLUSIVE. AʻE SHARE RAW ECXLUSIVE. ʻO ka ʻoiaʻiʻo, ʻaʻole maopopo loa kēia mau inoa. E ho'āʻo mākou e noʻonoʻo iā lākou i nā kikoʻī. Aia kekahi i ka huaʻōlelo "kaʻana", ʻo ia hoʻi ka hoʻokaʻawale. Aia kekahi i ka hua'ōlelo "exclusive". Aia kekahi o kēia mau huaʻōlelo ʻelua. Makemake au e hoʻomaka me ka hana ʻana o kēia mau laka.
A he mea nui hoʻi ka huaʻōlelo "access". A ʻo nā huaʻōlelo "laina" he kaula. ʻO ia hoʻi, ka hāʻawi ʻana, ka māhele lālani.
ʻO kekahi kumuhana e pono e hoʻomaopopo ʻia ma Postgres, ʻaʻole hiki iaʻu ke uhi i kaʻu kamaʻilio ʻana, ʻo MVCC. He hōʻike ʻokoʻa koʻu ma kēia kumuhana ma kaʻu pūnaewele. A inā manaʻo ʻoe he paʻakikī kēia hōʻike, ʻo MVCC paha kaʻu paʻakikī. A inā makemake ʻoe, hiki iā ʻoe ke nānā iā ia ma ka pūnaewele. Hiki iā ʻoe ke nānā i ke wikiō.
ʻO kekahi mea e pono ai mākou e hoʻomaopopo i nā ID kālepa. ʻAʻole hiki ke hana nā hana he nui me ka ʻole o nā mea hōʻike kūʻokoʻa. A eia kā mākou wehewehe ʻana i ke ʻano o ke kālepa. Loaʻa iā Postgres nā ʻōnaehana helu ʻelua. ʻIke wau ʻaʻole kēia he hopena nani loa.
E hoʻomanaʻo hoʻi e paʻakikī loa ka hoʻomaopopo ʻana i nā paheʻe, no laila ʻo ka mea i hōʻike ʻia i ka ʻulaʻula ka mea e pono ai ʻoe e hoʻolohe.
E nana kakou. Hōʻike ʻia ka helu kālepa i ka ʻulaʻula. Hōʻike ʻia ka hana SELECT pg_back maanei. Hoʻihoʻi ia i kaʻu kālepa a me ka ID kālepa.
ʻO kekahi mea hou aʻe, inā makemake ʻoe i kēia hōʻike a makemake ʻoe e holo ma kāu waihona, a laila hiki iā ʻoe ke hele i kēia loulou me ka ʻulaʻula a hoʻoiho i ka SQL no kēia hōʻike. A hiki iā ʻoe ke holo ma kāu PSQL a ʻo ka hōʻike holoʻokoʻa ma kāu pale koke. ʻAʻole loaʻa nā pua, akā ʻike mākou.
I kēia hihia, ʻike mākou i ka ID kālepa. ʻO kēia ka helu a mākou i hāʻawi ai iā ia. A aia kekahi ʻano ʻano ID kālepa ma Postgres, i kapa ʻia ʻo virtual transaction ID
A pono kākou e hoʻomaopopo i kēia. He mea nui loa kēia, inā ʻaʻole hiki iā mākou ke hoʻomaopopo i ka laka ʻana ma Postgres.
ʻO ka ID kālepa virtual he ID kālepa i loaʻa ʻole nā waiwai hoʻomau. No ka laʻana, inā holo wau i kahi kauoha SELECT, a laila ʻaʻole wau e hoʻololi i ka waihona, ʻaʻole wau e laka i kekahi mea. No laila ke holo mākou i kahi SELECT maʻalahi, ʻaʻole mākou e hāʻawi i kēlā kālepa i kahi ID hoʻomau. Hāʻawi wale mākou iā ia i kahi ID virtual ma laila.
A hoʻomaikaʻi kēia i ka hana ʻo Postgres, hoʻomaikaʻi i ka hiki ke hoʻomaʻemaʻe, no laila ʻelua mau helu ka ID transaction virtual. ʻO ka helu mua ma mua o ka slash ka ID hope. A ma ka ʻaoʻao ʻākau, ʻike mākou i kahi counter.
No laila, inā holo wau i kahi noi, ʻōlelo ʻia ʻo ka backend ID ʻo 2.
A inā e holo au i kahi ʻano o ia mau kālepa, a laila ʻike mākou e piʻi aʻe ka counter i kēlā me kēia manawa aʻu e holo ai i kahi nīnau. No ka laʻana, ke holo wau i ka nīnau 2/10, 2/11, 2/12, etc.
E hoʻomanaʻo he ʻelua kolamu ma ʻaneʻi. Ma ka hema ʻike mākou i ka ID kālepa virtual - 2/12. A ma ka ʻākau loaʻa iā mākou kahi ID kālepa mau. A kaawale keia kahua. A ʻaʻole hoʻololi kēia kālepa i ka waihona. No laila ʻaʻole wau e hāʻawi iā ia i kahi ID kālepa mau.
I koʻu holo ʻana i ke kauoha analyze ((ANALYZE)), hāʻawi ka nīnau like iaʻu i kahi ID kālepa mau. E nānā pehea i loli ai kēia iā mākou. ʻAʻole wau i kēia ID ma mua, akā i kēia manawa ua loaʻa iaʻu.
No laila, eia kekahi noi, kekahi hana. ʻO 2/13 ka helu kālepa virtual. A inā e noi au i kahi ID kālepa hoʻomau, a laila ke holo wau i ka nīnau, e loaʻa iaʻu.
No laila, hoʻokahi manawa hou. Loaʻa iā mākou kahi ID kālepa virtual a me kahi ID kālepa hoʻomau. E hoʻomaopopo wale i kēia wahi e hoʻomaopopo i ke ʻano o Postgres.
Hele mākou i ka ʻāpana ʻekolu. Maanei mākou e hele wale ai i nā ʻano laka like ʻole ma Postgres. ʻAʻole hoihoi loa. E ʻoi aku ka hoihoi o ka pauku hope. Akā, pono mākou e noʻonoʻo i nā mea kumu, no ka mea, inā ʻaʻole mākou e maopopo i ka mea e hiki mai ana.
E hele mākou ma kēia ʻāpana, e nānā mākou i kēlā me kēia ʻano laka. A e hōʻike wau iā ʻoe i nā hiʻohiʻona o ke ʻano o ka hoʻokomo ʻia ʻana, pehea lākou e hana ai, e hōʻike wau iā ʻoe i kekahi mau nīnau āu e hoʻohana ai e ʻike i ka hana ʻana o ka laka ma Postgres.
No ka hana ʻana i kahi nīnau a ʻike i ka mea e hana nei ma Postgres, pono mākou e hoʻopuka i ka nīnau ma ka ʻike pūnaewele. I kēia hihia, ua hōʻike ʻia ka pg_lock i ka ʻulaʻula. ʻO Pg_lock kahi papa ʻōnaehana e haʻi iā mākou i nā laka e hoʻohana ʻia nei ma Postgres.
Eia naʻe, paʻakikī loa iaʻu e hōʻike iā ʻoe i ka pg_lock iā ia iho no ka mea paʻakikī loa. No laila ua hana wau i kahi ʻike e hōʻike ana i nā pg_locks. A ke hana nei kekahi hana iaʻu e hiki ai iaʻu ke hoʻomaopopo maikaʻi. ʻO ia hoʻi, kāpae ia i kaʻu mau loka, kaʻu kau ponoʻī, a me nā mea ʻē aʻe.
ʻO kahi pilikia ʻē aʻe he ākea loa kēia ʻike, no laila pono wau e hana i kahi lua - lockview2.
A hōʻike mai iaʻu i nā kolamu hou aʻe mai ka papaʻaina. A ʻo kekahi e hōʻike mai iaʻu i ke koena o nā kolamu. He paʻakikī kēia, no laila ua hoʻāʻo wau e hōʻike i ka mea maʻalahi.
No laila ua hana mākou i kahi papa i kapa ʻia ʻo Lockdemo. A hana mākou i hoʻokahi laina ma laila. ʻO kēia kā mākou papa ʻaina. A e hana mākou i nā ʻāpana e hōʻike iā ʻoe i nā laʻana o nā laka.
No laila, hoʻokahi lālani, hoʻokahi kolamu. ʻO ka ʻano laka mua i kapa ʻia ʻo ACCESS SHARE. ʻO kēia ka pale palena iki. ʻO ia hoʻi, ʻaʻole ia e hakakā me nā laka ʻē aʻe.
A inā makemake mākou e wehewehe pono i kahi laka, holo mākou i ke kauoha "pākaukau laka". A e poloka ia, ʻo ia hoʻi ma ke ʻano ACCESS SHARE e hoʻomaka mākou i ka papa laka. A inā holo wau i ka PSQL ma hope, a laila hoʻomaka wau i ka lua o ka hālāwai mai kaʻu kau mua ma kēia ala. ʻO ia hoʻi, he aha kaʻu e hana ai ma ʻaneʻi? Hele au i kahi hālāwai ʻē aʻe a haʻi iā ia "e hōʻike mai iaʻu i ka lockview no kēia noi." A eia kaʻu AccessShareLock ma kēia pākaukau. ʻO kēia kaʻu i noi ai. A ua ʻōlelo ʻo ia ua hāʻawi ʻia ka poloka. Maʻalahi loa.
Eia hou, inā mākou e nānā i ke kolamu ʻelua, ʻaʻohe mea ma laila. Ua nele lākou.
A inā e holo wau i ke kauoha "SELECT", a laila ʻo ia ke ala implicit (explicit) e noi ai iā AccessShareLock. No laila hoʻokuʻu wau i kaʻu papaʻaina a holo i ka nīnau a hoʻihoʻi ka nīnau i nā lālani he nui. A ma kekahi o nā laina ʻike mākou iā AccessShareLock. No laila, kāhea ʻo SELECT iā AccessShareLock ma ka papa. A ʻaʻole ia e kūʻē me kekahi mea no ka mea he laka haʻahaʻa.
He aha inā e holo au i kahi SELECT a loaʻa iaʻu nā papa ʻokoʻa ʻekolu? Ma mua, e holo ana au i hoʻokahi papaʻaina, i kēia manawa ke holo nei au i ʻekolu: pg_class, pg_namespace a me pg_attribute.
A i kēia manawa ke nānā au i ka nīnau, ʻike wau i 9 AccessShareLocks i ʻekolu papa. No ke aha mai? Hōʻike ʻia nā papa ʻekolu i ka uliuli: pg_attribute, pg_class, pg_namespace. Akā hiki iā ʻoe ke ʻike i nā ʻōlelo kuhikuhi āpau i wehewehe ʻia ma o kēia mau papa i loaʻa iā AccessShareLock.
A he laka kēia i kū ʻole me nā poʻe ʻē aʻe. A ʻo nā mea a pau e pale wale iā mākou mai ka hoʻonohonoho hou ʻana i ka papaʻaina ke koho mākou. He kūpono. ʻO ia hoʻi, inā koho mākou i kahi papaʻaina, nalowale ia i kēlā manawa, a laila hewa kēia, no laila ʻO AccessShare kahi laka haʻahaʻa haʻahaʻa e haʻi mai iā mākou "mai hāʻule i kēia papaʻaina i kaʻu e hana nei". ʻO ka mea nui, ʻo ia wale nō kāna hana.
ROW SHARE - He ʻokoʻa iki kēia laka.
E lawe kākou i kekahi laʻana. SELECT ROW SHARE ala no ka laka ʻana i kēlā me kēia lālani. Ma kēia ala ʻaʻole hiki i kekahi ke hoʻopau iā lākou a hoʻololi paha iā mākou ke nānā nei iā lākou.
No laila he aha ka hana SHARE LOCK? ʻIke mākou he 681 ka ID kālepa no SELECT. A hoihoi kēia. He aha ka hana maanei? ʻO ka manawa mua a mākou e ʻike ai i ka helu aia ma ke kahua "Lock". Lawe mākou i ka ID kālepa a ʻōlelo ʻia ke kāohi nei ia i ke ʻano kūʻokoʻa. ʻO nā mea a pau e ʻōlelo nei he lālani wau i hoʻopaʻa ʻia ma kahi o ka papaʻaina. ʻAʻole naʻe ʻo ia i ʻōlelo i hea pono. E nānā mākou i kēia me nā kikoʻī hou aku ma hope iki.
Eia mākou e ʻōlelo nei ua hoʻohana ʻia ka laka e mākou.
No laila, ʻōlelo maopopo ka laka kūʻokoʻa he kūʻokoʻa. A inā hoʻi ʻoe e holoi i kahi lālani ma kēia pākaukau, a laila ʻo ia ka mea e hiki mai ana, e like me kāu e ʻike ai.
ʻO SHARE EXCLUSIVE kahi laka lōʻihi.
ʻO kēia ke kauoha analyze (ANALYZE) e hoʻohana ʻia.
SHARE LOCK - hiki iā ʻoe ke laka paʻa i ke ʻano kaʻana like.
Hiki iā ʻoe ke hana i kahi kuhikuhi kūikawā. A ma laila ʻoe e ʻike ai iā SHARE LOCK, ʻo ia kekahi o lākou. A hoʻopaʻa ʻo ia i ka pākaukau a kau i kahi LAKA SHARE ma luna.
ʻO ka mea paʻamau, SHARE LOCK ma ka papaʻaina ʻo ia ka mea hiki i nā poʻe ʻē aʻe ke heluhelu i ka papaʻaina, akā ʻaʻole hiki i kekahi ke hoʻololi. A ʻo kēia ka mea e hana ai ke hana ʻoe i kahi kuhikuhi kūʻokoʻa.
Inā wau e hana i kahi papa kuhikuhi like ʻole, a laila e loaʻa iaʻu kahi ʻano laka ʻokoʻa no ka mea, e like me kāu e hoʻomanaʻo ai, ʻo ka hoʻohana ʻana i nā index concurrently e hōʻemi i ka pono laka. A inā wau e hoʻohana i ka laka maʻamau, he kuhikuhi maʻamau, a laila e pale au i ke kākau ʻana i ka papa kuhikuhi papa i ka wā e hana ʻia ana. Inā hoʻohana au i ka papa kuhikuhi hoʻokahi, pono wau e hoʻohana i kahi ʻano laka ʻokoʻa.
SHARE ROW EXCLUSIVE - hiki ke hoʻonohonoho ʻia (me ka maopopo).
A i ʻole hiki iā mākou ke hana i kahi lula, ʻo ia hoʻi, e lawe i kahi hihia kikoʻī e hoʻohana ʻia ai.
ʻO ka laka EXCLUSIVE ʻaʻole hiki i kekahi ke hoʻololi i ka papaʻaina.
Maanei mākou e ʻike ai i nā ʻano laka like ʻole.
ʻO ACCESS EXCLUSIVE, no ka laʻana, he kauoha pale. No ka laʻana, inā hana ʻoe CLUSTER table, a laila, ʻaʻole hiki i kekahi ke kākau ma laila. A ʻaʻole ia e hoʻopaʻa i ka papaʻaina wale nō, akā, ʻo nā kuhikuhi pū kekahi.
ʻO kēia ka ʻaoʻao ʻelua o ka ACCESS EXCLUSIVE blocking, kahi mākou e ʻike pono ai i kāna mea i poloka ai i ka papaʻaina. Hoʻopaʻa ia i nā lālani pākaukau pākahi, he mea hoihoi loa ia.
ʻO ia ka ʻike kumu aʻu i makemake ai e hāʻawi. Ua kamaʻilio mākou e pili ana i nā loka, e pili ana i nā ID kālepa, ua kamaʻilio mākou e pili ana i nā ID kālepa virtual, e pili ana i nā ID kālepa mau.
A i kēia manawa e hele mākou i kekahi mau hiʻohiʻona pale. ʻO kēia ka mea hoihoi loa. E nānā mākou i nā hihia hoihoi loa. A ʻo kaʻu pahuhopu i kēia hōʻike ʻana, ʻo ia ka hāʻawi ʻana iā ʻoe i kahi ʻike maikaʻi aʻe i ka hana a Postgres i ka wā e hoʻāʻo ai e pale i kekahi mau mea. Manaʻo wau he maikaʻi loa ia i ka pale ʻana i nā ʻāpana.
E nānā kākou i kekahi mau laʻana kikoʻī.
E hoʻomaka mākou me nā papaʻaina a me hoʻokahi lālani i ka papaʻaina. Ke hoʻokomo wau i kahi mea i loaʻa iaʻu ExclusiveLock, Transaction ID a me ExclusiveLock i hōʻike ʻia ma ka papaʻaina.
He aha ka hopena inā hoʻokomo au i ʻelua lālani hou? A i kēia manawa he ʻekolu lālani kā mākou papaʻaina. A hoʻokomo wau i hoʻokahi lālani a loaʻa kēia ma ke ʻano he puka. A inā e hoʻokomo au i ʻelua lālani hou, he aha ka mea ʻē i kēlā? Aia kekahi mea ʻē ma ʻaneʻi no ka mea ua hoʻohui au i ʻekolu lālani i kēia pākaukau, akā ʻelua mau lālani i loko o ka pākaukau laka. A ʻo kēia ke ʻano kumu nui o Postgres.
Manaʻo ka poʻe he nui inā i loko o kahi waihona e laka ʻoe i nā lālani 100, a laila pono ʻoe e hana i 100 mau mea laka. Inā ālai au i nā lālani 1 i ka manawa hoʻokahi, a laila pono au i 000 mau nīnau. A inā makemake au i miliona a i ʻole piliona e ālai. Akā inā mākou e hana i kēia, ʻaʻole maikaʻi loa ia. Inā ʻoe i hoʻohana i kahi ʻōnaehana e hana ana i nā mea hoʻopaʻa inoa no kēlā me kēia lālani, a laila hiki iā ʻoe ke ʻike he paʻakikī kēia. No ka mea pono ʻoe e wehewehe koke i kahi papa laka e hiki ke kahe, akā ʻaʻole hana ʻo Postgres.
A ʻo ka mea koʻikoʻi e pili ana i kēia paheʻe, ʻo ia ka hōʻike maopopo ʻana aia kekahi ʻōnaehana e holo ana i loko o MVCC e laka ana i nā lālani pākahi. No laila ke laka ʻoe i nā piliona o nā lālani, ʻaʻole hana ʻo Postgres i hoʻokahi piliona mau kauoha laka kaʻawale. A he hopena maikaʻi loa kēia i ka huahana.
Pehea e pili ana i kahi mea hou? Ke hoʻonui nei au i ka lālani i kēia manawa, a ʻike ʻoe ua hana ia i ʻelua mau hana like ʻole i ka manawa hoʻokahi. Ua hoʻopaʻa ʻo ia i ka papaʻaina i ka manawa like, akā ua hoʻopaʻa pū ʻia ka index. A pono ʻo ia e hoʻopaʻa i ka index no ka mea aia nā ʻokoʻa kūʻokoʻa ma kēia pākaukau. A makemake mākou e hōʻoia ʻaʻole e hoʻololi kekahi, no laila ke kāohi nei mākou.
He aha ka hana inā makemake au e hoʻonui i ʻelua lālani? A ke ike nei kakou ua like kona ano. Hana mākou i ʻelua mau mea hou, akā ʻo ka helu like o nā laina laka.
Inā ʻoe e noʻonoʻo pehea e hana ai ʻo Postgres i kēia, pono ʻoe e hoʻolohe i kaʻu mau kamaʻilio ʻana ma MVCC e aʻo ai pehea e hōʻailona ai ʻo Postgres i kēia mau laina e hoʻololi ai. A loaʻa iā Postgres kahi ala e hana ai i kēia, akā ʻaʻole ia e hana ma ka pae paʻa paʻa, hana ia ma kahi haʻahaʻa a ʻoi aku ka maikaʻi.
Pehea inā makemake au e holoi i kekahi mea? Inā hoʻopau wau, no ka laʻana, hoʻokahi lālani a loaʻa iaʻu kaʻu mau mea hoʻokomo pale ʻelua, a inā makemake wau e holoi iā lākou āpau, aia nō lākou.
A, no ka laʻana, makemake wau e hoʻokomo i nā laina 1, a laila holoi a hoʻohui paha i nā laina 000, a laila kēlā mau laina pākahi aʻu i hoʻohui ai a hoʻololi paha, ʻaʻole i kākau ʻia ma aneʻi. Ua kākau ʻia lākou ma kahi pae haʻahaʻa i loko o ka moʻo ponoʻī. A i ka wā o ka haʻiʻōlelo MVCC ua kamaʻilio wau e pili ana i kēia me ka kikoʻī. Akā, he mea koʻikoʻi ke nānā ʻoe i nā laka e ʻike pono ʻoe e laka ana i ka pae papaʻaina a ʻaʻole ʻoe e ʻike i ke ʻano o ka hoʻopaʻa ʻia ʻana o nā lālani pākahi ma aneʻi.
Pehea e pili ana i ka pale ʻana?
Inā kaomi au i ka refresh, loaʻa iaʻu nā lālani ʻelua i laka. A inā koho wau iā lākou āpau a kaomi i ka "update everywhere," a laila loaʻa iaʻu ʻelua mau moʻolelo pale.
ʻAʻole mākou e hana i nā moʻolelo kaʻawale no kēlā me kēia lālani. No ka mea, hāʻule ka huahana, nui paha ia. A hiki iā mākou ke loaʻa iā mākou iho i kahi kūlana maikaʻi ʻole.
A ʻo ka mea like, inā mākou e kaʻana like, hiki iā mākou ke hana i nā manawa he 30.
Hoʻihoʻi mākou i kā mākou papaʻaina, holoi i nā mea āpau, a laila hoʻokomo hou i hoʻokahi lālani.
ʻO kahi ʻano ʻē aʻe āu e ʻike ai ma Postgres i ʻike nui ʻia a makemake ʻia e hiki iā ʻoe ke hana i kahi mea hou a i ʻole kahi koho. A hiki iā ʻoe ke hana i kēia i ka manawa like. A koho ʻaʻole e ālai i ka hōʻano hou a me ka mea like ma ka ʻaoʻao ʻē aʻe. Ke ʻōlelo nei mākou i ka mea heluhelu mai pāpā i ka mea kākau, a ʻaʻole i pāpā ka mea kākau i ka mea heluhelu.
E hōʻike wau iā ʻoe i kahi laʻana o kēia. E koho wau i kēia manawa. A laila e hana mākou i ka INSERT. A laila hiki iā ʻoe ke ʻike - 694. Hiki iā ʻoe ke ʻike i ka ID o ke kālepa i hana i kēia hoʻokomo. A pēlā ka hana.
A inā e nānā au i kaʻu ID hope i kēia manawa, ʻo 695 kēia.
A hiki iaʻu ke ʻike i ka 695 e puka mai ana ma kaʻu papaʻaina.
A inā e hoʻonui au ma ʻaneʻi e like me kēia, a laila loaʻa iaʻu kahi hihia ʻokoʻa. I kēia hihia, ʻo 695 kahi laka kūʻokoʻa, a he ʻano like ka hoʻonui, akā ʻaʻohe paio ma waena o lākou, he mea maʻamau.
A hiki iā ʻoe ke ʻike ma luna ʻo ShareLock, a ma lalo ʻo ExclusiveLock. A ua holo pono nā hana ʻelua.
A pono ʻoe e hoʻolohe i kaʻu kamaʻilio ma MVCC e hoʻomaopopo i ke ʻano o kēia. Akā he kiʻi kēia e hiki ai iā ʻoe ke hana i ka manawa like, ʻo ia hoʻi e hana i kahi SELECT a me kahi UPDATE i ka manawa like.
E hoʻonohonoho hou a hana hou i hoʻokahi hana.
Inā ʻoe e hoʻāʻo e holo i ʻelua mau mea hou i ka manawa like ma ka lālani hoʻokahi, e ālai ʻia. A e hoʻomanaʻo, ʻōlelo wau ʻaʻole pāpā ka mea heluhelu i ka mea kākau, a ʻaʻole hoʻopaʻa ka mea kākau i ka mea heluhelu, akā pale kekahi mea kākau i kekahi mea kākau. ʻO ia hoʻi, ʻaʻole hiki iā mākou ke hoʻololi i ʻelua mau kānaka i ka lālani hoʻokahi i ka manawa like. Pono ʻoe e kali a pau kekahi o lākou.
A i mea e hōʻike ai i kēia, e nānā au i ka papa ʻaina Lockdemo. A e nānā kākou i hoʻokahi lālani. No ka hana 698.
Ua hōʻano hou mākou i kēia i 2. ʻO 699 ka mea hou hou. A ua kūleʻa a i ʻole aia i kahi kālepa e kali nei a ke kali nei mākou e hōʻoia a hoʻopau paha.
Akā e nānā i kahi mea ʻē aʻe - ʻo 2/51 kā mākou hana mua, kā mākou hālāwai mua. ʻO 3/112 ka lua o ka noi i hele mai mai luna mai i hoʻololi i kēlā waiwai i 3. A inā ʻoe e ʻike, ua paʻa ka luna iā ia iho, ʻo ia ka 699. Akā ʻaʻole ʻo 3/112 i hāʻawi i ka laka. 'Ōlelo ke kolamu Lock_mode i kāna mea e kali nei. Manaʻo ia he 699. A inā ʻoe e nānā i kahi o 699, ʻoi aku ka kiʻekiʻe. A he aha ka hana a ka hālāwai mua? Ua hana ʻo ia i kahi laka kūʻokoʻa ma kāna ID kālepa ponoʻī. ʻO kēia ka hana a Postgres. Hoʻopaʻa ia i kāna ID kālepa ponoʻī. A inā makemake ʻoe e kali i kekahi e hōʻoia a hoʻopau paha, a laila pono ʻoe e kali ʻoiai aia kahi kālepa e kali nei. A ʻo ia ke kumu e ʻike ai mākou i kahi laina ʻano ʻē.
E nana hou kakou. Ma ka ʻaoʻao hema, ʻike mākou i kā mākou ID hana. Ma ke kolamu ʻelua, ʻike mākou i kā mākou ID kālepa virtual, a ma ke kolu ʻike mākou i lock_type. He aha ke ʻano o kēia? ʻO ka mea nui i ʻōlelo ʻia ʻo ia ke kāohi nei i ka ID kālepa. Akā, e nānā i nā lālani a pau ma lalo e ʻōlelo i ka pilina. A no laila, loaʻa iā ʻoe ʻelua ʻano laka ma ka papaʻaina. Aia kahi laka pili. A laila aia ka transactionid blocking, kahi āu e poloka ai iā ʻoe iho, ʻo ia ka mea e hana ai ma ka lālani mua a i ʻole ma lalo loa, kahi o ka transactionid, kahi mākou e kali ai no 699 e hoʻopau i kāna hana.
E ʻike au i ka hana ma ʻaneʻi. A eia nā mea ʻelua e hana like i ka manawa like. Ke nānā nei ʻoe i kahi laka ID kālepa ma ka lālani mua e laka iā ia iho. A pale ʻo ia iā ia iho i mea e kali ai nā kānaka.
Inā ʻoe e nānā i ka lālani 6, ua like ia me ka helu mua. A no laila ua pāpā ʻia ke kālepa 699. ʻO 700 ka mea paʻa ponoʻī. A laila ma ka lālani lalo e ʻike ʻoe ke kali nei mākou no 699 e hoʻopau i kāna hana.
A ma lock_type, ʻike ʻoe i nā helu.
Hiki iā ʻoe ke ʻike iā 0/10. A ʻo kēia ka helu ʻaoʻao, a me ka offset o kēia lālani.
A ʻike ʻoe ua lilo ia i 0/11 ke hoʻonui mākou.
Akā ʻo ka ʻoiaʻiʻo ʻo 0/10, no ka mea aia kahi kali no kēia hana. Loaʻa iā mākou ka manawa e ʻike ai ʻo kēia ka moʻolelo aʻu e kali nei e hōʻoia.
I ka manawa a mākou i hōʻoia ai a paʻi i ka commit, a i ka pau ʻana o ka mea hou, ʻo ia ka mea a mākou e loaʻa hou ai. ʻO Transaction 700 wale nō ka laka, ʻaʻole ia e kali i kekahi no ka mea ua paʻa. Ke kali wale nei a pau ka hana. Ke pau ka 699, ʻaʻole mākou e kali hou i kekahi mea. A i kēia manawa, ʻōlelo ʻo 700 ua maikaʻi nā mea āpau, aia nā laka āpau e pono ai ma nā papa ʻaina āpau.
A i mea e paʻakikī loa ai kēia mea holoʻokoʻa, hana mākou i kahi ʻike ʻē aʻe, i kēia manawa e hāʻawi iā mākou i kahi hierarchy. ʻAʻole wau manaʻo e hoʻomaopopo ʻoe i kēia noi. Akā e hāʻawi kēia iā mākou i kahi ʻike maʻalahi i nā mea e hana nei.
He manaʻo recursive kēia a loaʻa kekahi ʻāpana ʻē aʻe. A laila hoʻihoʻi hou i nā mea a pau. E hoʻohana kākou i kēia.
He aha inā inā mākou e hana i ʻekolu mau mea hou i ka manawa like a ʻōlelo mākou he ʻekolu ka lālani. A e hoʻololi mākou i ka 3 i ka 4.
A maʻaneʻi mākou e ʻike ai i ka 4. A me ka helu kālepa 702.
A laila e hoʻololi au i ka 4 i ka 5. A ʻo ka 5 i ka 6, a me ka 6 i ka 7. A e laina wau i kekahi mau kānaka e kali ana i ka pau ʻana o kēia hana hoʻokahi.
A maopopo nā mea a pau. He aha ka lālani mua? ʻO 702 kēia. ʻO kēia ka ID kālepa i hoʻonohonoho mua i kēia waiwai. He aha ka mea i kākau ʻia ma kaʻu kolamu Granted? He mau kaha ko'u f. ʻO kēia kaʻu mau mea hou i hiki ʻole ke ʻae ʻia (5, 6, 7) no ka mea ke kali nei mākou i ka pau ʻana o ka ID 702. Ma laila mākou i ka pale ID kālepa. A ʻo kēia ka hopena i 5 mau paʻa ID transactional.
A inā ʻoe e nānā iā 704, ma 705, ʻaʻohe mea i kākau ʻia ma laila, no ka mea ʻaʻole lākou i ʻike i ka mea e hana nei. Ua kākau wale lākou ʻaʻole lākou i ʻike i ka mea e hana nei. A e hiamoe wale nō lākou no ka mea ke kali nei lākou i kahi e hoʻopau ai a hoʻāla ʻia i ka wā e loaʻa ai ka manawa e hoʻololi i nā lālani.
Penei kona ano. Ua maopopo ke kali nei lakou a pau i ka laina 12.
ʻO kēia kā mākou i ʻike ai ma ʻaneʻi. Eia 0/12.
No laila ke ʻae ʻia ka hana mua, hiki iā ʻoe ke ʻike ma aneʻi pehea e hana ai ka hierarchy. A i kēia manawa ua maopopo nā mea a pau. Maʻemaʻe lākou a pau. A ke kali nei nō lākou.
Eia ka mea e hana nei. 702 hana. A loaʻa iā 703 kēia laka lālani, a laila hoʻomaka ka 704 e kali no 703 e hana. A ke kali nei ka 705 i kēia. A pau kēia mau mea, hoʻomaʻemaʻe lākou iā lākou iho. A makemake wau e kuhikuhi i ka laina laina o nā mea a pau. A ua like loa kēia me kahi kūlana i loko o kahi kaʻa kaʻa ke kali nei nā mea a pau i ke kaʻa mua. Kū ka kaʻa mua a laina nā mea a pau ma kahi laina lōʻihi. A laila neʻe ia, a laila hiki i ke kaʻa aʻe ke holo i mua a loaʻa kāna poloka, etc.
A inā ʻaʻole i paʻakikī kēia iā ʻoe, a laila e kamaʻilio mākou iā ʻoe e pili ana i nā deadlocks. ʻAʻole maopopo iaʻu ʻo wai o ʻoukou i hālāwai me lākou. He pilikia maʻamau kēia i nā ʻōnaehana waihona. Akā ʻo ka deadlocks ke kali nei kekahi kau i kahi kau hou e hana i kekahi mea. A i kēia manawa ke kali nei kahi kau hou no ka hālāwai mua e hana i kekahi mea.
A, no ka laʻana, inā ʻōlelo ʻo Ivan: "E hāʻawi mai iaʻu i kekahi mea," a ʻōlelo wau: "ʻAʻole, e hāʻawi wale wau iā ʻoe inā hāʻawi ʻoe iaʻu i kahi mea ʻē aʻe." A ʻōlelo ʻo ia, "ʻAʻole, ʻaʻole wau e hāʻawi iā ʻoe inā ʻaʻole ʻoe e hāʻawi mai iaʻu." A hoʻopau mākou i kahi kūlana deadlock. Manaʻo wau ʻaʻole e hana ʻo Ivan i kēia, akā hoʻomaopopo ʻoe i ke ʻano he ʻelua mau kānaka e makemake ana e loaʻa kekahi mea a ʻaʻole mākaukau lākou e hāʻawi a hiki i ka hāʻawi ʻana o kekahi i kā lāua makemake. A ʻaʻohe hopena.
A ʻo ka mea nui, pono kāu waihona e ʻike i kēia. A laila pono ʻoe e holoi a pani paha i kekahi o nā kau, no ka mea inā ʻaʻole e noho mau lākou ma laila. A ʻike mākou i loko o nā waihona, ʻike mākou ma nā ʻōnaehana hana. A ma nā wahi āpau i loaʻa iā mākou nā kaʻina hana like, hiki ke hana kēia.
A i kēia manawa e hoʻokomo mākou i ʻelua deadlocks. E kau mākou i ka 50 a me 80. Ma ka lālani mua, e hoʻonui au mai ka 50 a i ka 50. E loaʻa iaʻu ka helu kālepa 710.
A laila e hoʻololi au i ka 80 i 81, a me 50 i 51.
A penei ke ano o ia mea. A no laila ua pāpā ʻia ka lālani 710, a ke kali nei ʻo 711 i ka hōʻoia. Ua ʻike mākou i kēia i ko mākou hōʻano hou ʻana. ʻO 710 ka mea nona kā mākou moʻolelo. A ke kali nei ʻo 711 no 710 e hoʻopau i ke kālepa.
A ke ʻōlelo nei nō hoʻi ma ka lālani hea nā deadlocks. A eia kahi e hoʻomaka ai e lilo i mea ʻē.
I kēia manawa ke hoʻonui nei mākou i ka 80 a 80.
A ʻo kēia kahi e hoʻomaka ai ka make. Ke kali nei ʻo 710 i ka pane mai 711, a ke kali nei ʻo 711 iā 710. ʻAʻole e pau maikaʻi kēia. A ʻaʻohe ala i waho o kēia. A e manaʻo lākou i kahi pane mai kekahi i kekahi.
A e hoʻomaka wale ana e lohi i nā mea a pau. A ʻaʻole mākou makemake i kēlā.
A loaʻa iā Postgres nā ala e ʻike ai i ka wā e hana ai kēia. A ke hana ʻia kēia, loaʻa iā ʻoe kēia hewa. A mai keia mea i maopopo ke kali ana ia mea a me ia ano no ka SHARE LOCK mai kekahi kaʻina hana ʻē aʻe, ʻo ia hoʻi, ua ālai ʻia e ka hana 711. A ke kali nei kēlā kaʻina hana no kahi LAKA SHARE e hāʻawi ʻia ma kēlā a me kēlā ʻano ID kālepa a ua kāohi ʻia e kēlā a me kēia kaʻina hana. No laila, aia kahi pilikia ma ʻaneʻi.
Loaʻa nā ʻaoʻao ʻekolu? Hiki paha? ʻAe.
Hoʻokomo mākou i kēia mau helu i loko o kahi pākaukau. Hoʻololi mākou i ka 40 a 40, hana mākou i ka pale.
Hoʻololi mākou i 60 i 61, 80 i 81.
A laila hoʻololi mākou i 80 a laila boom!
A ke kali nei ka 714 i ka 715. Ke kali nei ka 716 i ka 715. ʻAʻohe mea hiki ke hana no ia mea.
ʻAʻole ʻelua mau kānaka ma ʻaneʻi, ʻekolu mau kānaka ma ʻaneʻi. Makemake au i kekahi mea mai iā ʻoe, makemake kēia mea i ke kolu o ke kanaka, a ʻo ke kolu ke makemake i kekahi mea mai iaʻu. A hoʻopau mākou i kahi kali ʻekolu no ka mea ke kali nei mākou a pau i ka mea ʻē aʻe e hoʻopau i nā mea e pono ai lākou e hana.
A ʻike ʻo Postgres i ka lālani e hana ai kēia. A no laila e hāʻawi iā ʻoe i kēia memo, e hōʻike ana he pilikia kāu i kahi ʻekolu mau mea hoʻokomo e pāpā ana kekahi i kekahi. A ʻaʻohe kapu ma ʻaneʻi. ʻO kēia paha ka hihia kahi 20 mau mea e pale ai kekahi i kekahi.
ʻO ka pilikia aʻe he serializable.
Inā laka serializable kūikawā.
A hoʻi mākou i 719. He mea maʻamau kona puka.
A hiki iā ʻoe ke kaomi e hana i ke kālepa mai serializable.
A ʻike ʻoe i kēia manawa he ʻano ʻokoʻa ka laka SA - ʻo ia ke ʻano serializable.
A no laila, loaʻa iā mākou kahi ʻano laka hou i kapa ʻia ʻo SARieadLock, kahi laka serial a hiki iā ʻoe ke komo i nā serials.
A hiki nō hoʻi iā ʻoe ke hoʻokomo i nā kuhikuhi kikoʻī.
Ma kēia papaʻaina, loaʻa iā mākou nā kuhikuhi kikoʻī.
No laila, inā wau e hoʻokomo i ka helu 2 ma ʻaneʻi, no laila, loaʻa iaʻu kahi 2. Akā ma luna loa, hoʻokomo wau i kahi 2 hou aʻe. Akā i kēia manawa ke kali nei ʻo 721 iā 722 e hoʻopau i kāna hana no ka mea ʻaʻole hiki iā ia ke hoʻokomo i ka 721 a ʻike ʻo ia i ka mea e hiki mai ana i ka 2.
A inā mākou e hana subtransaction.
Eia mākou he 723.
A inā mākou e mālama i ka helu a laila e hoʻonui iā ia, a laila loaʻa iā mākou kahi ID kālepa hou. He ʻano hana ʻē aʻe kēia e pono ai ʻoe e ʻike. Inā hoʻihoʻi mākou i kēia, a laila hele ka ID kālepa. 724 e haʻalele ana. Akā i kēia manawa he 725 mākou.
No laila he aha kaʻu e hoʻāʻo nei e hana ma ʻaneʻi? Ke ho'āʻo nei au e hōʻike iā ʻoe i nā laʻana o nā laka ʻokoʻa āu e ʻike ai: inā paha he mau laka serializable a i ʻole SAVEPOINT, he mau ʻano laka like ʻole kēia e ʻike ʻia ma ka papa laka.
ʻO kēia ka hana ʻana i nā laka paʻa (explicit), nona ka pg_advisory_lock.
A ʻike ʻoe ua helu ʻia ke ʻano blocking ma ke ʻano he aʻoaʻo. A ma ʻaneʻi e ʻōlelo ʻia "aʻoaʻo" i ka ʻulaʻula. A hiki iā ʻoe ke pāpā like me kēia me pg_advisory_unlock.
A i ka hopena, makemake wau e hōʻike iā ʻoe i kahi mea hoʻohihi. E hana au i kahi ʻike ʻē aʻe. Akā, e hui au i ka papa pg_locks me ka papa pg_stat_activity. A no ke aha wau e makemake ai e hana i kēia? No ka mea e ʻae kēia iaʻu e nānā a ʻike i nā kau āpau o kēia manawa a ʻike pono i ke ʻano o nā laka a lākou e kali nei. A he mea hoihoi loa kēia ke hoʻohui mākou i ka pākaukau laka a me ka papa nīnau.
A eia mākou e hana i ka pg_stat_view.
A hōʻano hou mākou i ka lālani i hoʻokahi. A eia mākou e ʻike ai i ka 724. A laila hoʻonui mākou i kā mākou lālani i ʻekolu. A he aha kāu e ʻike nei i kēia manawa? He mau noi kēia, ʻo ia hoʻi, ʻike ʻoe i ka papa inoa holoʻokoʻa o nā noi i helu ʻia ma ka kolamu hema. A laila ma ka ʻaoʻao ʻākau hiki iā ʻoe ke ʻike i nā poloka a me nā mea a lākou e hana ai. A hiki ke maopopo iā ʻoe i ʻole ʻoe e hoʻi i kēlā me kēia kau i kēlā me kēia manawa a ʻike inā pono ʻoe e hui pū me ia a ʻaʻole paha. Hana lākou no mākou.
ʻO kekahi hiʻohiʻona maikaʻi loa pg_blocking_pids. ʻAʻole paha ʻoe i lohe iā ia. He aha kona hana? Hāʻawi ia iā mākou e haʻi no kēia kau 11740 i nā ID kaʻina kikoʻī e kali nei. A ʻike ʻoe e kali ana ʻo 11740 iā 724. A ʻo 724 ma luna loa. A ʻo 11306 kāu ID kaʻina. ʻO ka mea nui, hele kēia hana ma kāu papa laka. A ʻike wau he paʻakikī iki, akā hiki iā ʻoe ke hoʻomaopopo. ʻO ka mea nui ke hele nei kēia hana ma kēia papa laka a hoʻāʻo e ʻimi i kahi i hāʻawi ʻia ai kēia kaʻina ID i nā laka e kali nei. A ke ho'āʻo nei hoʻi e noʻonoʻo i ke kaʻina hana ID i ke kaʻina e kali nei no ka laka. No laila hiki iā ʻoe ke holo i kēia hana pg_blocking_pids.
A he mea pono loa kēia. Hoʻohui wale mākou i kēia ma ka mana 9.6, no laila he 5 mau makahiki wale nō kēia hiʻohiʻona, akā he mea pono loa. A pela no ka lua o ka noi. Hōʻike pololei ia i nā mea e pono ai mākou e ʻike.
ʻO kēia kaʻu mea i makemake ai e kamaʻilio pū me ʻoe. A e like me kaʻu i manaʻo ai, hoʻohana mākou i ko mākou manawa āpau no ka nui o nā paheʻe. A loaʻa nā kiʻi paheʻe no ka hoʻoiho ʻana. Mahalo wau iā ʻoe no kou noho ʻana ma ʻaneʻi. Manaʻo wau e hauʻoli ʻoe i ke koena o ka ʻaha kūkā, mahalo nui iā ʻoe!
Nīnau:
No ka laʻana, inā e ho'āʻo nei au e hoʻololi i nā lālani, a ke hoʻāʻo nei ka lua o ke kau e holoi i ka papaʻaina holoʻokoʻa. I koʻu hoʻomaopopo ʻana, pono e loaʻa kahi mea e like me ka laka manaʻo. Aia kekahi mea ma Postgres?
E hoʻi kāua i ka hoʻomaka. E hoʻomanaʻo paha ʻoe i ka wā e hana ai ʻoe i kekahi mea, no ka laʻana ke hana ʻoe i kahi SELECT, hoʻopuka mākou i kahi AccessShareLock. A ke pale nei kēia i ka waiho ʻana o ka papaʻaina. No laila, inā makemake ʻoe e hoʻohou i kahi lālani ma kahi papaʻaina a i ʻole e holoi i kahi lālani, a laila ʻaʻole hiki i kekahi ke holoi i ka papaʻaina holoʻokoʻa i ka manawa like no ka mea ke paʻa nei ʻoe i kēia AccessShareLock ma luna o ka papaʻaina holoʻokoʻa a ma luna o ka lālani. A pau kāu hana, hiki iā lākou ke holoi. Akā ʻoiai ʻoe e hoʻololi pololei i kahi mea ma laila, ʻaʻole hiki iā lākou ke hana.
E hana hou kāua. E neʻe kākou i ka laʻana holoi. A ʻike ʻoe i ka loaʻa ʻana o kahi laka kūʻokoʻa ma ka lālani ma luna o ka papaʻaina holoʻokoʻa.
E like paha kēia me ka laka kūʻokoʻa, ʻeā?
ʻAe, me he mea lā. Maopopo iaʻu kāu mea e kamaʻilio nei. Ke ʻōlelo nei ʻoe inā e hana wau i kahi SELECT a laila loaʻa iaʻu kahi ShareExclusive a laila hana wau iā Row Exclusive, lilo ia i pilikia? Akā naʻe, ʻaʻole pilikia kēia. Ua like kēia me ka hoʻonui ʻana i ka degere laka, akā ʻo kaʻu mea pono he laka e pale ai i ka holoi ʻana. A i kēia manawa, ke hana au i kēia laka i ʻoi aku ka ikaika, ke pale mau nei ia i ka holoi ʻana. No laila, ʻaʻole wau e piʻi i luna. ʻO ia hoʻi, ʻaʻole ia e hana i ka wā ma kahi haʻahaʻa loa, no laila ke hoʻokiʻekiʻe au i kona pae e pale mau ana i ka holoi ʻana i ka papaʻaina.
Maopopo iaʻu kāu mea e kamaʻilio nei. ʻAʻohe hihia escalation laka ma ʻaneʻi, kahi āu e hoʻāʻo nei e haʻalele i hoʻokahi laka e hoʻokomo i kahi laka ʻoi aku ka ikaika. Ma ʻaneʻi e hoʻonui wale i kēia pale ʻana ma ka papa, no laila ʻaʻole ia e hoʻoulu i ka hakakā. Akā, he nīnau maikaʻi. Mahalo nui iā ʻoe no ka nīnau ʻana i kēia!
He aha kā mākou e hana ai i mea e pale aku ai i kahi kūlana deadlock ke loaʻa iā mākou nā kau he nui, ka nui o nā mea hoʻohana?
Hoʻomaopopo koke ʻo Postgres i nā kūlana make. A e hoʻopau aunoa ia i kekahi o nā kau. ʻO ke ala wale nō e pale aku ai i ka pale ʻana i ka make, ʻo ia ka pale ʻana i nā poʻe ma ke ʻano like. No laila ke nānā nei ʻoe i kāu noi, pinepine ke kumu o nā deadlocks ... E noʻonoʻo kākou makemake wau e pāpā i ʻelua mau mea like ʻole. Hoʻokahi palapala hoʻopaʻa paʻa i ka papa 1, a ʻo kekahi palapala hoʻopaʻa paʻa i ka 2, a laila ka papa 1. A ʻo ke ala maʻalahi loa e pale aku i nā deadlocks, ʻo ia ke nānā i kāu noi a hoʻāʻo e hōʻoia i ka hana ʻana o ka laka ma ka hoʻonohonoho like ma nā noi āpau. A hoʻopau pinepine kēia i ka 80% o nā pilikia, no ka mea, kākau nā ʻano kanaka āpau i kēia mau noi. A inā hoʻopaʻa ʻoe iā lākou ma ke ʻano like, a laila ʻaʻole ʻoe e hālāwai me kahi kūlana deadlock.
Mahalo nui iā ʻoe no kāu hana! Ua kamaʻilio ʻoe e pili ana i ka ʻūhā piha a, inā maopopo iaʻu, hoʻokaʻawale ka ʻūhā piha i ka hoʻonohonoho o nā moʻolelo ma kahi waiho ʻokoʻa, no laila mālama lākou i nā moʻolelo o kēia manawa me ka loli ʻole. No ke aha e lawe ai ʻo vacuum piha i ka laka kūʻokoʻa a no ke aha e hakakā ai me nā hana kākau?
He nīnau maikaʻi kēlā. ʻO ke kumu, ʻo ka piha ʻana o ka vacuum e lawe i ka papaʻaina. A ke hana nei mākou i kahi mana hou o ka papaʻaina. A e hou ka papaʻaina. ʻIke ʻia he mana hou kēia o ka papaʻaina. A ʻo ka pilikia, ke hana mākou i kēia, ʻaʻole mākou makemake e heluhelu ka poʻe no ka mea pono mākou e ʻike i ka papaʻaina hou. A no laila pili kēia i ka nīnau mua. Inā hiki iā mākou ke heluhelu i ka manawa like, ʻaʻole hiki iā mākou ke hoʻoneʻe a kuhikuhi i nā poʻe i kahi papaʻaina hou. Pono mākou e kali no ka pau ʻana o ka heluhelu ʻana i kēia papa ʻaina, a no laila, he kūlana paʻa wale nō ia.
'Ōlelo wale mākou ua laka mākou mai ka hoʻomaka ʻana no ka mea ʻike mākou i ka hope loa e pono ai mākou i kahi laka kūʻokoʻa i mea e neʻe ai i nā mea a pau i ke kope hou. No laila hiki iā mākou ke hoʻoholo i kēia. A ke hana nei mākou i kēia ʻano me ka indexing like. Akā ʻoi aku ka paʻakikī o kēia hana. A pili loa kēia i kāu nīnau mua e pili ana i ka laka wale nō.
Hiki paha ke hoʻohui i ka manawa paʻa i Postgres? Ma Oracle, hiki iaʻu, no ka laʻana, ke kākau "koho e hoʻohou" a kali i 50 kekona ma mua o ka hoʻonui ʻana. Ua maikaʻi no ka noi. Akā ma Postgres, pono wau e hana koke a ʻaʻole e kali iki, a kali paha a hiki i kekahi manawa.
ʻAe, hiki iā ʻoe ke koho i kahi manawa pau ma kāu mau laka, ma kāu mau laka. Hiki iā ʻoe ke hoʻopuka i kahi kauoha ʻaʻohe ala, ʻo ia ... inā ʻaʻole hiki iā ʻoe ke kiʻi koke i ka laka. No laila, ʻo ka manawa paʻa a i ʻole kekahi mea ʻē aʻe e hiki ai iā ʻoe ke hana i kēia. ʻAʻole hana ʻia kēia ma ka pae syntactic. Hana ʻia kēia ma ke ʻano he loli ma ke kikowaena. I kekahi manawa ʻaʻole hiki ke hoʻohana ʻia kēia.
Hiki iā ʻoe ke wehe i ka slide 75?
ʻAe.
A penei kaʻu nīnau. No ke aha e manaʻo nei nā kaʻina hana hou ʻelua i ka 703?
A he nīnau nui kēia. ʻAʻole maopopo iaʻu, ma ke ala, no ke aha e hana ai ʻo Postgres i kēia. Akā, i ka wā i hana ʻia ai ʻo 703, ua manaʻo ʻia ʻo 702. A i ka wā i hōʻike ʻia ai ka 704 a me 705, me he mea lā ʻaʻole lākou i ʻike i kā lākou mea e manaʻo nei no ka mea ʻaʻohe mea i laila. A hana ʻo Postgres i kēia ʻano: inā ʻaʻole hiki iā ʻoe ke kiʻi i kahi laka, kākau ʻo ia "He aha ke kumu o ka hana ʻana iā ʻoe?", No ka mea, ke kali nei ʻoe i kekahi. No laila e ʻae wale mākou iā ia e kau i ka lewa, ʻaʻole ia e hōʻano hou. Akā he aha ka mea i hana ʻia ma ʻaneʻi? I ka hoʻopau ʻana o 702 i ke kaʻina hana a loaʻa iā 703 kona laka, hoʻi hou ka ʻōnaehana. A ʻōlelo ʻo ia i kēia manawa ʻelua mau kānaka e kali nei. A laila e hoʻohou pū kākou iā lākou. A e hōʻike mākou e kali ana nā mea ʻelua.
ʻAʻole maopopo iaʻu ke kumu e hana ai ʻo Postgres i kēia. Akā aia kekahi pilikia i kapa ʻia ʻo f…. Me he mea lā ʻaʻole kēia he huaʻōlelo ma ka ʻōlelo Lūkini. ʻO kēia ka manawa e kali ai nā mea a pau i hoʻokahi hale kākela, ʻoiai inā he 20 mau mana e kali nei i ka hale kākela. A ala koke lākou a pau i ka manawa like. A hoʻomaka nā mea a pau e hoʻāʻo e pane. Akā, hana ka ʻōnaehana i mea e kali ai nā mea a pau no 703. No ka mea ke kali nei lākou a pau, a e laina koke mākou iā lākou āpau. A inā e ʻike ʻia kekahi noi hou i hana ʻia ma hope o kēia, no ka laʻana, 707, a laila e nele hou.
A i koʻu manaʻo ua hana ʻia kēia i hiki iā mākou ke ʻōlelo i kēia manawa ke kali nei ʻo 702 no 703, a ʻo ka poʻe a pau e hele mai ana ma hope o kēlā, ʻaʻohe komo i kēia kahua. Akā i ka haʻalele ʻana o ka waiter mua, loaʻa ka poʻe a pau e kali ana i kēlā manawa ma mua o ka hoʻonui ʻana i ka hōʻailona like. A no laila, manaʻo wau ua hana ʻia kēia i hiki iā mākou ke hoʻoponopono i ka hoʻonohonoho pono ʻana.
Ua nānā mau au i kēia he mea ʻano ʻano ʻē. No ka mea ma ʻaneʻi, no ka laʻana, ʻaʻole mākou i papa inoa iā lākou. Akā i koʻu manaʻo, i kēlā me kēia manawa a mākou e hāʻawi ai i kahi laka hou, ke nānā nei mākou i ka poʻe a pau e kali nei. A laila laina mākou iā lākou a pau. A laila, komo wale ka mea hou i loko o ka pila ke pau ka hana ʻana o ka mea aʻe. He nīnau maikaʻi loa. Mahalo nui iā ʻoe no kāu nīnau!
Me he mea lā iaʻu ua ʻoi aku ka maikaʻi i ka wā e manaʻo ai ʻo 705 i ka 704.
Akā, ʻo ka pilikia ma ʻaneʻi, ʻo ia kēia. ʻO ka ʻenehana, hiki iā ʻoe ke ala i kekahi a i ʻole kekahi. A pēlā mākou e ala ai i kekahi a i ʻole kekahi. Akā he aha ka mea i loko o ka ʻōnaehana? Hiki iā ʻoe ke ʻike pehea i kāohi ai ʻo 703 ma luna loa i kāna ID kālepa ponoʻī. ʻO kēia ka hana a Postgres. A ua ālai ʻia ʻo 703 e kāna ID kālepa ponoʻī, no laila inā makemake kekahi e kali, a laila e kali lākou no 703. A, ma ke ʻano, hoʻopau ʻo 703. A ma hope o kona hoʻopau ʻana e ala mai ai kekahi o nā kaʻina hana. A ʻaʻole maopopo iā mākou he aha ke ʻano o kēia kaʻina hana. A laila, hana mākou i nā mea a pau me ka mālie. Akā ʻaʻole maopopo ke kaʻina hana i hoʻāla mua ʻia, no ka mea, ʻo ia paha kekahi o kēia mau hana. ʻO ka mea nui, loaʻa iā mākou kahi mea hoʻonohonoho i ʻōlelo hiki iā mākou ke ala i kēia mau kaʻina hana. E koho wale mākou i hoʻokahi. No laila pono e ʻike ʻia lāua ʻelua no ka mea hiki iā mākou ke hoʻāla i kekahi o lākou.
A ʻo ka pilikia, loaʻa iā mākou ka CP-infinity. A no laila, hiki iā mākou ke ala i ka hope. A inā e hoʻāla mākou i ka mea hope, e kali mākou i ka mea i loaʻa i ka poloka, no laila ʻaʻole mākou e hoʻoholo i ka mea e hoʻāla mua ʻia. Hoʻokumu wale mākou i kahi kūlana, a na ka ʻōnaehana e hoʻāla iā lākou i kahi ʻano maʻamau.
he nui na . E nānā, he mea hoihoi a pono hoʻi. ʻO ke kumuhana, ʻoiaʻiʻo, paʻakikī loa. Mahalo nui iā ʻoe, Bruce!
Source: www.habr.com