http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex><\/p>\n<\/p>\n
Skat\u0101mies. Sarkan\u0101 kr\u0101s\u0101 izcelts transakcijas numurs. \u0160eit par\u0101d\u012bta funkcija SELECT pg_back. T\u0101 atgrie\u017e manu transakciju un \u0161\u012bs transakcijas ID. <\/p>\n
<\/p>\n
V\u0113l viens moments: ja jums pat\u012bk \u0161\u012b prezent\u0101cija un v\u0113laties to palaist sav\u0101 datu b\u0101z\u0113, varat sekot \u0161ai roz\u0101 kr\u0101s\u0101 izceltai saitei un lejupiel\u0101d\u0113t SQL \u0161ai prezent\u0101cijai. Varat to vienk\u0101r\u0161i palaist sav\u0101 PSQL un visa prezent\u0101cija par\u0101d\u012bsies j\u016bsu ekr\u0101n\u0101 nekav\u0113joties. Taj\u0101 neb\u016bs kr\u0101su, bet vismaz m\u0113s to var\u0113sim redz\u0113t.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/aeaeecc16029e5fa54583223069578d1.png\")
<\/p>\n
<\/p>\n
\u0160aj\u0101 gad\u012bjum\u0101 redzam transakcijas ID. Tas ir numurs, kuru m\u0113s tam pie\u0161\u0137\u012br\u0101m. Ir ar\u012b cits transakcijas ID veids Postgres, ko sauc par virtu\u0101lo transakcijas ID.<\/p>\n
<\/p>\n
Mums tas ir j\u0101izprot. Tas ir \u013coti svar\u012bgi, cit\u0101di m\u0113s nevar\u0113sim saprast blo\u0137\u0113\u0161anu Postgres.<\/p>\n
<\/p>\n
Virtu\u0101lais transakcijas ID ir transakcijas ID, kas nesatur past\u0101v\u012bgas v\u0113rt\u012bbas. Piem\u0113ram, ja es palaistu SELECT komandu, visdr\u012bz\u0101k es nemain\u012b\u0161u datu b\u0101zi un neko neblo\u0137\u0113\u0161u. T\u0101p\u0113c, kad m\u0113s palaistam vienk\u0101r\u0161u SELECT, m\u0113s nedodam \u0161ai transakcijai past\u0101v\u012bgu ID. M\u0113s pie\u0161\u0137iram tikai virtu\u0101lo ID.<\/p>\n
<\/p>\n
Tas uzlabo Postgres veiktsp\u0113ju un att\u012br\u012b\u0161anas iesp\u0113jas, t\u0101p\u0113c virtu\u0101lais transakcijas ID sast\u0101v no diviem skait\u013ciem. Pirmais skaitlis pirms sl\u012bpsv\u012btras ir backend ID. Bet pa labi m\u0113s redzam vienk\u0101r\u0161i skait\u012bt\u0101ju.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/8813ab1f15f71ce4fbb5448bde2cfba6.png\")
<\/p>\n
<\/p>\n
T\u0101d\u0113\u013c, ja es palaistu vaic\u0101jumu, tas nor\u0101da, ka backend ID ir 2. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/b6f4e3a2ad8d6801af2716b0bd3bf75a.png\")
<\/p>\n
<\/p>\n
Un, ja es palaistu virkni \u0161\u0101du transakciju, m\u0113s redzam, ka skait\u012bt\u0101js katru reizi palielin\u0101s, kad es palaistu vaic\u0101jumu. Piem\u0113ram, kad es palaistu vaic\u0101jumu 2\/10, 2\/11, 2\/12 utt. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/0b5fbb897233ff3dd07b807af4d4e0d7.png\")
<\/p>\n
<\/p>\n
\u0145emiet v\u0113r\u0101, ka \u0161eit ir divas kolonnas. Pa kreisi m\u0113s redzam virtu\u0101lo transakcijas ID \u2013 2\/12. Bet pa labi mums ir past\u0101v\u012bgais transakcijas ID. Un \u0161aj\u0101 lauk\u0101 nav nek\u0101. Un \u0161\u012b transakcija nemodific\u0113 datu b\u0101zi. T\u0101p\u0113c es tai nepie\u0161\u0137iru past\u0101v\u012bgu transakcijas ID.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/e8ab3dc34ce6036eedfb87e56f41d01a.png\")
<\/p>\n
<\/p>\n
Tikl\u012bdz es palaistu komandu analiz\u0113t (ANALYZE), tas pats vaic\u0101jums man sniedz past\u0101v\u012bgu transakcijas ID. Apskatiet, k\u0101 tas ir main\u012bjies. Agr\u0101k man nebija \u0161\u012b ID, tagad tas ir par\u0101d\u012bjies.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ca546a3006bfc0881ef1c7e7fe878c7a.png\")
<\/p>\n
<\/p>\n
T\u0101tad, \u0161eit ir v\u0113l viens vaic\u0101jums, v\u0113l viena transakcija. Virtu\u0101lais transakcijas numurs \u2013 2\/13. Un, ja es piepras\u012b\u0161u past\u0101v\u012bgu transakcijas ID, tad, kad es palaistu vaic\u0101jumu, es to ieg\u016b\u0161u. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/69bf4082be1832504a823f6420d62609.png\")
<\/p>\n
<\/p>\n
T\u0101tad, v\u0113lreiz. Mums ir virtu\u0101lais transakcijas ID un past\u0101v\u012bgais transakcijas ID. Vienk\u0101r\u0161i saprotiet \u0161o momentu, lai izprastu Postgres uzved\u012bbu.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ecc70c44ceee36dab9c98bcfcebf4c8f.png\")
<\/p>\n
<\/p>\n
P\u0101rejam pie tre\u0161\u0101 sada\u013cas. \u0160eit m\u0113s vienk\u0101r\u0161i apskat\u012bsim da\u017e\u0101dus blo\u0137\u0113\u0161anas veidus Postgres. Tas nav p\u0101r\u0101k aizraujo\u0161i. P\u0113d\u0113j\u0101 sada\u013ca b\u016bs daudz interesant\u0101ka. Bet mums ir j\u0101apsver pamata lietas, cit\u0101di m\u0113s nesaprat\u012bsim to, kas notiks t\u0101l\u0101k. <\/p>\n
<\/p>\n
M\u0113s iziesim cauri \u0161ai sada\u013cai, m\u0113s apskat\u012bsim katru blo\u0137\u0113\u0161anas veidu. Un es jums par\u0101d\u012b\u0161u piem\u0113rus, k\u0101 tie tiek iestat\u012bti, k\u0101 tie darbojas, par\u0101d\u012b\u0161u jums da\u017eus vaic\u0101jumus, kurus var izmantot, lai redz\u0113tu, k\u0101 darbojas blo\u0137\u0113\u0161anas meh\u0101nisms Postgres.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/2074b6c82ff77525090c868d0c9f09f5.png\")
<\/p>\n
<\/p>\n
Lai izveidotu vaic\u0101jumu un redz\u0113tu, kas notiek Postgres, mums ir j\u0101izdod vaic\u0101jums system view. \u0160aj\u0101 gad\u012bjum\u0101 sarkan\u0101 kr\u0101s\u0101 ir izcelta pg_lock. Pg_lock ir sist\u0113mas tabula, kas mums nor\u0101da, kuras blo\u0137\u0113\u0161anas \u0161obr\u012bd tiek izmantotas Postgres.<\/p>\n
<\/p>\n
Tom\u0113r man ir \u013coti gr\u016bti par\u0101d\u012bt jums pg_lock pa\u0161u, jo tas ir diezgan sare\u017e\u0123\u012bti. T\u0101p\u0113c es izveidoju view, kas par\u0101da pg_locks. Tas ar\u012b veic man da\u017eus uzdevumus, kas \u013cauj man lab\u0101k saprast. T. i., tas izsl\u0113dz manas blo\u0137\u0113\u0161anas, manu pa\u0161a sesiju utt. Tas ir vienk\u0101r\u0161i standarta SQL, un tas \u013cauj lab\u0101k jums par\u0101d\u012bt, kas notiek. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/8372fff8ded76ace47591850b74db860.png\")
<\/p>\n
<\/p>\n
V\u0113l viena probl\u0113ma ir t\u0101, ka \u0161is view ir \u013coti pla\u0161s, t\u0101p\u0113c man ir j\u0101izveido otrais \u2013 lockview2.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/559745d390f99d7d28199be76d3abc18.png\")
Tas man r\u0101da v\u0113l vair\u0101k kolonnas no tabulas. Un v\u0113l viena, kas man r\u0101da p\u0101r\u0113j\u0101s kolonnas. Tas ir diezgan sare\u017e\u0123\u012bti, t\u0101p\u0113c es centos to par\u0101d\u012bt p\u0113c iesp\u0113jas vienk\u0101r\u0161\u0101k. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/48d69895241e1f58ac5ef0e385a67644.png\")
<\/p>\n
<\/p>\n
T\u0101tad, m\u0113s izveidoj\u0101m tabulu, kas saucas Lockdemo. Un m\u0113s taj\u0101 izveidoj\u0101m vienu rindu. T\u0101 ir m\u016bsu parauga tabula. Un m\u0113s izveidosim sada\u013cas, lai vienk\u0101r\u0161i par\u0101d\u012btu jums blo\u0137\u0113\u0161anas piem\u0113rus. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/2c5a64d5488c1eef7def91a5e0657519.png\")
<\/p>\n
<\/p>\n
T\u0101tad, viena rinda, viena kolonna. Pirmais blo\u0137\u0113\u0161anas veids saucas ACCESS SHARE. Tas ir vismaz\u0101k ierobe\u017eojo\u0161s blo\u0137\u0113jums. Tas noz\u012bm\u0113, ka tas praktiski nesaskaras ar p\u0101r\u0113j\u0101m blo\u0137\u0113\u0161an\u0101m.<\/p>\n
<\/p>\n
Ja, ja, ja m\u0113s v\u0113lamies skaidri defin\u0113t blo\u0137\u0113\u0161anu, m\u0113s palaid\u012bsim komandu \u00ablock table\u00bb. Tas skaidri blo\u0137\u0113s, t. i., piek\u013cuves koplieto\u0161anas re\u017e\u012bm\u0101 m\u0113s palaid\u012bsim lock table. Un, ja es palaid\u012b\u0161u PSQL fona re\u017e\u012bm\u0101, es t\u0101d\u0113j\u0101di palaid\u012b\u0161u otro sesiju no savas pirm\u0101s sesijas. T. i., ko es \u0161eit dar\u012b\u0161u? Es p\u0101reju uz citu sesiju un saku tai \u00abpar\u0101di man lockview \u0161im vaic\u0101jumam\u00bb. Un \u0161eit man ir AccessShareLock \u0161aj\u0101 tabul\u0101. Tas ir tie\u0161i tas, ko es pras\u012bju. Un vi\u0146\u0161 saka, ka blo\u0137\u0113\u0161ana ir pie\u0161\u0137irta. \u013boti vienk\u0101r\u0161i. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/458adfcb7fb0de3f4b23bc703a78534e.png\")
<\/p>\n
<\/p>\n
T\u0101l\u0101k, ja m\u0113s skat\u0101mies uz otro kolonnu, tur nekas nav. T\u0101s ir tuk\u0161as. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/f3b848f3c9bfaf1a41273effeb0965e1.png\")
<\/p>\n
<\/p>\n
Un, ja es palaid\u012b\u0161u komandu \u00abSELECT\u00bb, tas ir implicitais (ac\u012bmredzamais) veids, k\u0101 piepras\u012bt AccessShareLock. T\u0101p\u0113c es izlaistu savu tabulu un palaistu vaic\u0101jumu, un vaic\u0101jums atgrie\u017e vair\u0101kas rindas. Un vien\u0101 no rind\u0101m m\u0113s redzam AccessShareLock. T\u0101d\u0113j\u0101di SELECT izraisa AccessShareLock tabul\u0101. Un tas praktiski nesaskar\u0101s ar neko, jo t\u0101 ir zema l\u012bme\u0146a blo\u0137\u0113\u0161ana. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/0d9526dbea91b5e5f4e0e05a20b16d38.png\")
<\/p>\n
<\/p>\n
Ko dar\u012bt, ja es palaid\u012b\u0161u SELECT un man b\u016bs tr\u012bs da\u017e\u0101das tabulas? Agr\u0101k es palaidu tikai vienu tabulu, tagad es palaidu tr\u012bs: pg_class, pg_namespace un pg_attribute.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/415be5bb7cb7cbbe6bbfd0ab1b9b2d3e.png\")
<\/p>\n
<\/p>\n
Un tagad, kad es skatos uz vaic\u0101jumu, es redzu 9 AccessShareLocks trij\u0101s tabul\u0101s. K\u0101p\u0113c? Zil\u0101 kr\u0101s\u0101 izcelti tr\u012bs tabulas: pg_attribute, pg_class, pg_namespace. Bet j\u016bs varat ar\u012b redz\u0113t, ka visiem indeksam, kas defin\u0113ts caur \u0161\u012bm tabul\u0101m, ir ar\u012b AccessShareLock.<\/p>\n
<\/p>\n
Un tas ir blo\u0137\u0113jums, kas praktiski nesaskaras ar citiem. Un viss, ko vi\u0146\u0161 dara, ir, ka vi\u0146\u0161 vienk\u0101r\u0161i ne\u013cauj mums izt\u012br\u012bt tabulu, kam\u0113r m\u0113s to izv\u0113lamies. Tas ir j\u0113gpilni. T. i., ja m\u0113s izv\u0113lamies tabulu, t\u0101 taj\u0101 br\u012bd\u012b izz\u016bd, tad tas nav pareizi, t\u0101p\u0113c. AccessShare \u2013 t\u0101 ir zemas l\u012bme\u0146a blo\u0137\u0113\u0161ana, kas nor\u0101da \"neizdz\u0113siet \u0161o tabulu, kam\u0113r es str\u0101d\u0101ju\".<\/strong>Pamat\u0101 tas ir viss, ko vi\u0146\u0161 dara. <\/p>\n<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/99c896a5a57f7d64077fad9368f9b41a.png\")
<\/p>\n
<\/p>\n
ROW SHARE \u2013 tas ir blo\u0137\u0113jums, kas nedaudz at\u0161\u0137iras. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/bbd833128a83fba3b7271f89e6115425.png\")
<\/p>\n
<\/p>\n
\u0145emsim piem\u0113ru. SELECT ROW SHARE ir veids, k\u0101 blo\u0137\u0113t katru rindu atsevi\u0161\u0137i.<\/strong>T\u0101d\u0113j\u0101di neviens nevar t\u0101s izdz\u0113st vai main\u012bt, kam\u0113r m\u0113s t\u0101s skat\u0101m. <\/p>\n<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7f7384d5c4c04ffc771192359d785c43.png\")
T\u0101tad, ko dara SHARE LOCK? M\u0113s redzam, ka transakcijas ID 681 attiec\u0101s uz SELECT. Un tas ir interesanti. Kas \u0161aj\u0101 gad\u012bjum\u0101 notika? Pirmo reizi m\u0113s redzam numuru lauk\u0101 \u201eLock\u201d. M\u0113s \u0146emam transakcijas ID, un tas saka, ka t\u0101 ekskluz\u012bvi blo\u0137\u0113. Viss, ko t\u0101 dara, vi\u0146a saka, ka man ir rinda, kas tehniski ir blo\u0137\u0113ta kaut kur tabul\u0101. Bet nepasaka, kur tie\u0161i. Nedaudz v\u0113l\u0101k m\u0113s to apskat\u012bsim detaliz\u0113t\u0101k.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/284a303fd34c167d453facb46e207067.png\")
<\/p>\n
<\/p>\n
\u0160eit m\u0113s sak\u0101m, ka blo\u0137\u0113\u0161ana tiek izmantota mums.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/12a2be61a2f406fc125dcab11983e9c6.png\")
<\/p>\n
<\/p>\n
T\u0101tad, ekskluz\u012bv\u0101 blo\u0137\u0113\u0161ana skaidri (p\u0101rskat\u0101mi) nor\u0101da, ka t\u0101 ir ekskluz\u012bva. Un, ja j\u016bs dz\u0113\u0161at rindu \u0161aj\u0101 tabul\u0101, tad tas tie\u0161\u0101m notiks, k\u0101 j\u016bs varat redz\u0113t. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/23ed127ff4a033a6f51c0b5566fd6a7c.png\")
<\/p>\n
<\/p>\n
SHARE EXCLUSIVE \u2013 t\u0101 ir ilg\u0101ka blo\u0137\u0113\u0161ana.<\/strong><\/p>\n<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/12e6b0217c528ef5034eb328553fefe9.png\")
<\/p>\n
<\/p>\n
Tas (ANALYZE) ir anal\u012bzes komanda, kas tiks izmantota.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/d11480b90b74c8165a60bd73cf24a683.png\")
<\/p>\n
<\/p>\n
SHARE LOCK \u2013 j\u016bs varat skaidri blo\u0137\u0113t share re\u017e\u012bm\u0101.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ee2a5ad8d0da5fb94865abe3cfa70fa7.png\")
<\/p>\n
<\/p>\n
J\u016bs varat ar\u012b izveidot unik\u0101lu indeksu. Un tur j\u016bs varat redz\u0113t SHARE LOCK, kas ir t\u0101 da\u013ca. Un tas blo\u0137\u0113 tabulu un uzliek tai SHARE LOCK blo\u0137\u0113\u0161anu.<\/p>\n
<\/p>\n
P\u0113c noklus\u0113juma SHARE LOCK tabulai noz\u012bm\u0113, ka citi var las\u012bt tabulu, bet neviens to nevar modifik\u0113t. Un tie\u0161i tas notiek, kad j\u016bs izveidojat unik\u0101lu indeksu.<\/strong><\/p>\n<\/p>\n
Ja es izveidoju unik\u0101lu concurrently indeksu, man b\u016bs cits blo\u0137\u0113\u0161anas veids, jo, k\u0101 j\u016bs atceraties, concurrently indeksu izmanto\u0161ana samazina blo\u0137\u0113\u0161anas pras\u012bbas. Un, ja es izmantoju parasto blo\u0137\u0113\u0161anu, parasto indeksu, tad es t\u0101d\u0113j\u0101di nov\u0113r\u0161u rakst\u012b\u0161anu indeks\u0101 tabulas izveides laik\u0101. Ja es izmantoju concurrently indeksu, man j\u0101izmanto cits blo\u0137\u0113\u0161anas veids. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5952ba48cda26f907d0d9265a933e880.png\")
<\/p>\n
<\/p>\n
SHARE ROW EXCLUSIVE \u2013 atkal to var noteikt skaidri (p\u0101rskat\u0101mi).<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5d780961d3a2e6aa0f82287fb4822782.png\")
<\/p>\n
<\/p>\n
Vai ar\u012b varam izveidot noteikumu, t.i., \u0146emt k\u0101du konkr\u0113tu gad\u012bjumu, kad tas tiks izmantots.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7b0f44c4b44426a8dd7999387b245783.png\")
<\/p>\n
<\/p>\n
EXCLUSIVE blo\u0137\u0113\u0161ana noz\u012bm\u0113, ka neviens cits nevar\u0113s main\u012bt tabulu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/0b59c38455eb973a10be32c2cae0d2aa.png\")
<\/p>\n
<\/p>\n
\u0160eit m\u0113s redzam da\u017e\u0101dus blo\u0137\u0113\u0161anas veidus. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5ec2fc9ae765dfa28285b9f025212056.png\")
<\/p>\n
<\/p>\n
ACCESS EXCLUSIVE, piem\u0113ram, ir blo\u0137\u0113\u0161anas komanda. Piem\u0113ram, ja j\u016bs veicat CLUSTER table<\/code>, tas noz\u012bm\u0113s, ka neviens nevar\u0113s taj\u0101 rakst\u012bt. Un tas blo\u0137\u0113 ne tikai pa\u0161u tabulu, bet ar\u012b indeksus.<\/p>\n<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/9290594355c0b8cef5a07be3a94f6c24.png\")
<\/p>\n
<\/p>\n
Tas ir otrais ACCESS EXCLUSIVE blo\u0137\u0113\u0161anas lapa, kur\u0101 m\u0113s konkr\u0113ti redzam, ko t\u0101 blo\u0137\u0113 tabul\u0101. T\u0101 blo\u0137\u0113 atsevi\u0161\u0137as tabulas rindas, kas ir diezgan interesanti.<\/p>\n
<\/p>\n
\u0160\u012b ir visa pamatinform\u0101cija, ko es v\u0113l\u0113jos sniegt. M\u0113s run\u0101j\u0101m par blo\u0137\u0113\u0161an\u0101m, par dar\u012bjumu ID, run\u0101j\u0101m par virtu\u0101lajiem dar\u012bjumu ID, par past\u0101v\u012bgajiem dar\u012bjumu ID. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/b9bc44aa88e6760c11a7d0fe247b4b33.png\")
<\/p>\n
<\/p>\n
Tagad m\u0113s apl\u016bkosim blo\u0137\u0113\u0161anas piem\u0113rus. Tas ir visinteresant\u0101kais posms. M\u0113s izskat\u012bsim \u013coti interesantus gad\u012bjumus. Un mana uzdevums \u0161aj\u0101 prezent\u0101cij\u0101 ir sniegt jums lab\u0101ku izpratni par to, ko Postgres paties\u012bb\u0101 dara, kad tas m\u0113\u0123ina blo\u0137\u0113t da\u017e\u0101das lietas. Man \u0161\u0137iet, ka tas \u013coti labi prot blo\u0137\u0113t atsevi\u0161\u0137us elementus. <\/p>\n
<\/p>\n
Apskat\u012bsim konkr\u0113tus piem\u0113rus.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/566ecc5ef289e8fdfa5a96bd8b3daa80.png\")
<\/p>\n
<\/p>\n
S\u0101ksim ar tabul\u0101m un vienu rindu tabul\u0101. Kad es ko ievietoju, man par\u0101d\u0101s ExclusiveLock, dar\u012bjuma ID un ExclusiveLock uz tabulu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/840b23a8b942211c93e233006fd3fe4b.png\")
<\/p>\n
<\/p>\n
Bet kas notiks, ja es ievieto\u0161u v\u0113l divas rindas? Tagad m\u016bsu tabul\u0101 ir tr\u012bs rindas. Es ievietoju vienu rindu un sa\u0146emu \u0161\u0101du rezult\u0101tu. Un, ja es ievieto\u0161u v\u0113l divas rindas, kas \u0161eit ir d\u012bvaini? \u0160eit ir d\u012bvain\u012bba, jo es pievienoju tr\u012bs rindas \u0161ai tabulai, bet man joproj\u0101m ir divas rindas blo\u0137\u0113\u0161an\u0101. Un tas ir, b\u016bt\u012bb\u0101, Postgres pamata uzved\u012bba.<\/p>\n
<\/p>\n
Daudzi dom\u0101, ka, ja datos j\u016bs blo\u0137\u0113jat 100 rindas, jums b\u016bs nepiecie\u0161ams izveidot 100 skat\u012bjumu blo\u0137\u0113\u0161an\u0101m. Ja es blo\u0137\u0113\u0161u uzreiz 1 000 rindas, tad man b\u016bs vajadz\u012bgi 1 000 \u0161\u0101du piepras\u012bjumu. Un, ja man j\u0101noblo\u0137\u0113 miljons vai miljards. Bet, ja m\u0113s to dar\u012bsim, tad tas nedarbosies \u013coti labi. Ja esat izmantoju\u0161i sist\u0113mu, kas veido blo\u0137\u0113\u0161anas ierakstus katrai atsevi\u0161\u0137ai rindai, tad saprotiet, ka tas ir sare\u017e\u0123\u012bti. Jo jums nekav\u0113joties j\u0101nosaka blo\u0137\u0113\u0161anas tabula, kas var p\u0101rpild\u012bties, bet Postgres t\u0101 nedara. <\/p>\n
<\/p>\n
Un \u0161aj\u0101 sl\u012bd\u012b ir \u013coti svar\u012bgi, ka \u0161eit skaidri par\u0101d\u012bts, ka past\u0101v v\u0113l viena sist\u0113ma, kas darbojas iek\u0161 MVCC, kas blo\u0137\u0113 atsevi\u0161\u0137as rindas. T\u0101d\u0113\u013c, kad j\u016bs blo\u0137\u0113jat miljardiem rindu, Postgres neveido miljardus atsevi\u0161\u0137u blo\u0137\u0113\u0161anas komandu. Un tas \u013coti labi ietekm\u0113 veiktsp\u0113ju. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/09ee01fffbb9bd00c84f2ebce51458cc.png\")
<\/p>\n
<\/p>\n
K\u0101 ir ar atjaunin\u0101jumiem? Es pa\u0161laik atjauninu rindas, un j\u016bs varat paman\u012bt, ka tas uzreiz veica divas da\u017e\u0101das darb\u012bbas. Tas vienlaic\u012bgi blo\u0137\u0113ja tabulu, bet ar\u012b blo\u0137\u0113ja indeksu. Un tam bija nepiecie\u0161ams blo\u0137\u0113t indeksu, jo \u0161aj\u0101 tabul\u0101 ir unik\u0101lie ierobe\u017eojumi. M\u0113s v\u0113lamies p\u0101rliecin\u0101ties, ka neviens to nemaina, t\u0101p\u0113c m\u0113s to blo\u0137\u0113jam.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/3e72d68af2d5ae3cc062b6e8b6da343b.png\")
<\/p>\n
<\/p>\n
Ko dar\u012bt, ja es gribu atjaunin\u0101t divas rindas? M\u0113s redzam, ka tas uzvedas t\u0101pat. M\u0113s veicam divreiz vair\u0101k atjaunin\u0101jumu, bet blo\u0137\u0113to rindu skaits paliek t\u0101ds pats. <\/p>\n
<\/p>\n
Ja j\u016bs interes\u0113, k\u0101 Postgres to dara, jums j\u0101izdara uzman\u012bba uz man\u0101m lekcij\u0101m par MVCC, lai uzzin\u0101tu, k\u0101 Postgres iek\u0161\u0113ji apz\u012bm\u0113 \u0161\u012bs rindas, kuras tas maina. Un Postgres ir veids, k\u0101 to izdar\u012bt, bet tas to nedara tabulu blo\u0137\u0113\u0161anas l\u012bmen\u012b, bet gan efekt\u012bv\u0101k\u0101 un zem\u0101k\u0101 l\u012bmen\u012b.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7e119822b0fb7c5aa3c4a463841bc8e1.png\")
<\/p>\n
<\/p>\n
Bet, ja es gribu kaut ko izdz\u0113st? Ja es dz\u0113\u0161u, piem\u0113ram, vienu rindu un man joproj\u0101m ir manas divas blo\u0137\u0113\u0161anas, un pat ja es v\u0113l\u0113tos t\u0101s visas izdz\u0113st, t\u0101s joproj\u0101m b\u016btu kl\u0101t.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/c329f9fbc478a25396ea04102715973e.png\")
<\/p>\n
<\/p>\n
Piem\u0113ram, ja es gribu ievietot 1 000 rindu un tad vai nu izdz\u0113st, vai pievienot 1 000 rindu, tad t\u0101s individu\u0101l\u0101s rindas, kuras es pievienoju vai mainu, netiek ierakst\u012btas \u0161eit. T\u0101s tiek ierakst\u012btas uz zem\u0101ka l\u012bme\u0146a iek\u0161\u0113jos datos. Un lekcij\u0101 par MVCC es par to run\u0101ju deta\u013c\u0101s. Bet ir \u013coti svar\u012bgi, kad j\u016bs analiz\u0113jat blo\u0137\u0113jumus, p\u0101rliecin\u0101ties, ka jums ir blo\u0137\u0113jums tabulas l\u012bmen\u012b un ka j\u016bs neredzat, k\u0101 tiek ierakst\u012btas atsevi\u0161\u0137as rindas. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5683ca83f5c17da7928fae96c307c2d6.png\")
<\/p>\n
<\/p>\n
K\u0101 ir ar eksplic\u012bto blo\u0137\u0113\u0161anu?<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/a1746f121884c898c91e3b5d2ef517b8.png\")
<\/p>\n
<\/p>\n
Ja es nospie\u017eu \"atjaunin\u0101t\", tad man ir divas blo\u0137\u0113tas rindas. Un, ja es t\u0101s visas iez\u012bm\u0113ju un nospiedu \"atjaunin\u0101t visur\", man joproj\u0101m paliek divi blo\u0137\u0113jumi. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5d5388e5d0aff39042a9cd69edc91eea.png\")
<\/p>\n
<\/p>\n
M\u0113s neveidojam atsevi\u0161\u0137as ierakstus katrai atsevi\u0161\u0137ai rindai. Jo tad sniegums samazin\u0101s, var\u0113tu b\u016bt p\u0101r\u0101k daudz. Un m\u0113s varam non\u0101kt nepat\u012bkamaj\u0101 situ\u0101cij\u0101. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/54e88ed51e705701a8b3ddaa4a784d75.png\")
<\/p>\n
<\/p>\n
Un tas pats attiec\u012bb\u0101 uz shared; m\u0113s varam veikt to 30 reizes. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ab9c26e9be090d85b09070ec5ba0d64d.png\")
<\/p>\n
<\/p>\n
M\u0113s atjaunojam m\u016bsu tabulu, visu izdz\u0113\u0161am, p\u0113c tam atkal ievietojam vienu rindu.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/2d0a5b75f5cd08dca97b61bbae6a6cbd.png\")
<\/p>\n
<\/p>\n
V\u0113l viena uzved\u012bba, ko j\u016bs varat redz\u0113t Postgres, ir \u013coti labi zin\u0101ma un v\u0113lama uzved\u012bba \u2013 j\u016bs varat veikt gan update, gan select. Un j\u016bs varat to dar\u012bt vienlaikus. Select neblo\u0137\u0113 update, un ar\u012b otr\u0101di. M\u0113s sak\u0101m, ka las\u012bt\u0101js neblo\u0137\u0113 to, kur\u0161 raksta, bet tas, kur\u0161 raksta, neblo\u0137\u0113 las\u012bt\u0101ju. <\/p>\n
<\/p>\n
Es jums par\u0101d\u012b\u0161u \u0161\u012b piem\u0113ra. Es pa\u0161laik veicu izv\u0113li. M\u0113s v\u0113l\u0101k veiksim INSERT. Un j\u016bs tad var\u0113siet redz\u0113t \u2013 694. J\u016bs var\u0113siet redz\u0113t dar\u012bjuma ID, kas veica \u0161o ievieto\u0161anu. Un t\u0101 ir \u0161\u012b darb\u012bba. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/f80f3b68ef43a75a9590ff200741ce83.png\")
<\/p>\n
<\/p>\n
Ja tagad paskat\u012b\u0161os uz savu aizmugur\u0113jo ID, tad tas ir k\u013cuvis \u2013 695. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/65bc487bbfd7b325aa5cffa7373a40a4.png\")
<\/p>\n
<\/p>\n
Un es varu redz\u0113t, ka 695 par\u0101d\u0101s man\u0101 tabul\u0101. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/6ac586641f978615db4d021e85ef702a.png\")
<\/p>\n
<\/p>\n
Ja es \u0161eit veicu atjaunin\u0101\u0161anu t\u0101, tad es ieg\u016bstu citu gad\u012bjumu. \u0160aj\u0101 gad\u012bjum\u0101 695 ir ekskluz\u012bva blo\u0137\u0113\u0161ana, bet update ir t\u0101da pati uzved\u012bba, tikai starp t\u0101m nerodas konflikts, kas ir pietiekami neparasti. <\/p>\n
<\/p>\n
Un j\u016bs varat paman\u012bt, ka aug\u0161pus\u0113 \u2013 tas ir ShareLock, bet apak\u0161\u0101 \u2013 tas ir ExclusiveLock. Un abas dar\u012bjumi ir sasniegu\u0161i rezult\u0101tu.<\/p>\n
<\/p>\n
Un jums j\u0101klaus\u0101s man\u0101 prezent\u0101cij\u0101 par MVCC, lai saprastu, k\u0101 tas notiek. Bet tas ir ilustr\u0101cija tam, ka j\u016bs varat to dar\u012bt vienlaikus, proti, vienlaikus veikt SELECT un UPDATE.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/3bcf43cdb9ee72ea75e17c3bf71f3407.png\")
<\/p>\n
<\/p>\n
L\u016bdzu, m\u0113s izsl\u0113gsim un v\u0113lreiz veiksim vienu oper\u0101ciju. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/fdae265dbebb0fae66e47cd4eb4b1bd6.png\")
<\/p>\n
<\/p>\n
Ja j\u016bs m\u0113\u0123in\u0101siet vienlaikus uzs\u0101kt divus update uz vienu un to pa\u0161u rindu, tad tas tiks blo\u0137\u0113ts. Un atcerieties, es teicu, ka las\u012bt\u0101js neblo\u0137\u0113 rakst\u012bt\u0101ju, bet rakst\u012bt\u0101js blo\u0137\u0113 las\u012bt\u0101ju, bet viens rakst\u012bt\u0101js blo\u0137\u0113 otru rakst\u012bt\u0101ju. Proti, m\u0113s nevaram pie\u013caut, lai divi cilv\u0113ki vienlaikus atjaunin\u0101tu to pa\u0161u rindu. J\u0101m\u0101c\u0101s gaid\u012bt, kam\u0113r vienam no vi\u0146iem beidzas process. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ef9adfe81107468dfea1c5a837b28aaa.png\")
<\/p>\n
<\/p>\n
Un lai to ilustr\u0113tu, es paskat\u012b\u0161os uz Lockdemo tabulu. Un m\u0113s apl\u016bkosim vienu rindu. Par dar\u012bjumu 698.<\/p>\n
<\/p>\n
M\u0113s to atjaunin\u0101j\u0101m l\u012bdz 2. 699 \u2013 tas ir pirmais atjaunin\u0101jums. Un tas ir izg\u0101jis veiksm\u012bgi vai atrodas gaido\u0161\u0101 dar\u012bjuma st\u0101vokl\u012b un gaida, kad m\u0113s apstiprin\u0101sim vai atcelsim. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/9794811cdc204141a74f67ee98fca1f6.png\")
<\/p>\n
<\/p>\n
Bet skat\u012bt uz ko citu \u2013 2\/51 \u2013 t\u0101 ir m\u016bsu pirm\u0101 transakcija, m\u016bsu pirm\u0101 sesija. 3\/112 \u2013 tas ir otrais piepras\u012bjums, kas par\u0101d\u012bj\u0101s aug\u0161pus\u0113 un main\u012bja \u0161o v\u0113rt\u012bbu uz 3. Un, ja paman\u0101t, tad aug\u0161\u0113jais ir blo\u0137\u0113jis pats sevi, kas ir 699. Bet 3\/112 nesniedza blo\u0137\u0113\u0161anu. Kolonn\u0101 Lock_mode rakst\u012bts, ka tas gaida. Tas gaida 699. Un, ja paskat\u0101ties, kur ir 699, tas ir augst\u0101k. Un ko izdar\u012bja pirm\u0101 sesija? T\u0101 izveidoja ekskluz\u012bvu blo\u0137\u0113\u0161anu uz savu pa\u0161u transakcijas ID. T\u0101 tas darbojas Postgres. Tas blo\u0137\u0113 savu pa\u0161u transakcijas ID. Un, ja v\u0113laties gaid\u012bt, kam\u0113r k\u0101ds apstiprina vai atce\u013c, j\u0101gaida, kam\u0113r ir gaido\u0161a transakcija. T\u0101p\u0113c m\u0113s varam redz\u0113t d\u012bvainu rindi\u0146u. <\/p>\n
<\/p>\n
Apskat\u012bsim v\u0113lreiz. Kreisaj\u0101 pus\u0113 m\u0113s redzam m\u016bsu apstr\u0101des ID. Otraj\u0101 kolonn\u0101 m\u0113s redzam m\u016bsu virtu\u0101lo transakcijas ID, bet tre\u0161aj\u0101 redzam lock_type. Ko tas noz\u012bm\u0113? Paties\u012bb\u0101, tas saka, ka tas blo\u0137\u0113 transakcijas ID. Bet atz\u012bm\u0113jiet, ka vis\u0101s rind\u0101s apak\u0161\u0101 rakst\u012bts relation. T\u0101p\u0113c jums ir divu veidu blo\u0137\u0113\u0161ana tabul\u0101. Ir relation blo\u0137\u0113\u0161ana. Un ar\u012b ir transactionid blo\u0137\u0113\u0161ana, kur j\u016bs blo\u0137\u0113jat pa\u0161i, tas ir tie\u0161i tas, kas notiek pirmaj\u0101 rind\u0101 vai pa\u0161\u0101 apak\u0161\u0101, kur transactionid, kur m\u0113s gaid\u0101m, lai 699 pabeigtu savu darb\u012bbu. <\/p>\n
<\/p>\n
Es skatos, kas \u0161eit notiek. Un \u0161eit vienlaikus notiek divas lietas. J\u016bs skat\u0101ties uz blok\u0101di p\u0113c transakcijas ID pirmaj\u0101 rind\u0101, kas blo\u0137\u0113 pati sevi. Un tas blo\u0137\u0113 pati sevi, lai liktu cilv\u0113kiem gaid\u012bt. <\/p>\n
<\/p>\n
Ja j\u016bs paskat\u0101ties uz 6. rindu, tad t\u0101 pati ieraksts k\u0101 pirmaj\u0101. Un t\u0101p\u0113c transakcija 699 ir blo\u0137\u0113ta. 700 ar\u012b pa\u0161blo\u0137\u0113jas. Un p\u0113c tam apak\u0161\u0113j\u0101 rind\u0101 j\u016bs redz\u0113siet, ka m\u0113s gaid\u0101m, kad 699 pabeigs savu darb\u012bbu.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/e712a48cdb9ddc496e4e196eb3bb97c3.png\")
<\/p>\n
<\/p>\n
Un lock_type, tuple j\u016bs redzat skait\u013cus. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/575245898d8455f00583659264b96ce9.png\")
<\/p>\n
<\/p>\n
J\u016bs varat redz\u0113t, ka tas ir 0\/10. Un tas ir lapu numurs, un ar\u012b \u0161\u012b konkr\u0113t\u0101 rinda offset. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7059c7661fc81a7101b2d92d38b6f64c.png\")
<\/p>\n
<\/p>\n
Un j\u016bs redzat, ka tas k\u013c\u016bst 0\/11, kad m\u0113s atjaunojam. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/4572ef7afc0ba8c8bbf5706ce9415d65.png\")
<\/p>\n
<\/p>\n
Bet paties\u012bb\u0101 \u2013 tas ir 0\/10, jo notiek \u0161\u012bs darb\u012bbas gaid\u012b\u0161ana. Mums ir iesp\u0113ja paskat\u012bties, ka \u0161\u012b ir t\u0101 rinda, kuru es gaidu, lai apstiprin\u0101tu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/9dc6fb0e3dee8c0b1ccbb724d0bb6c75.png\")
<\/p>\n
<\/p>\n
Kad m\u0113s to apstiprin\u0101j\u0101m un nospied\u0101m commit, un kad atjaunin\u0101jums beidz\u0101s, m\u0113s atkal ieg\u016bsim \u0161o rezult\u0101tu. Transakcija 700 ir vien\u012bgais blo\u0137\u0113jums, t\u0101 vairs nevienu negaida, jo t\u0101 ir apstiprin\u0101ta. T\u0101 vienk\u0101r\u0161i gaida, l\u012bdz transakcija nosl\u0113gsies. Tikl\u012bdz 699 beidzas, m\u0113s vairs neko negaid\u0101m. Un tagad transakcija 700 saka, ka viss ir k\u0101rt\u012bb\u0101, ka visi nepiecie\u0161amie blo\u0137\u0113jumi ir pieejami vis\u0101s at\u013cautaj\u0101s tabul\u0101s. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/42bc10a0c5bbda9bcf3a40f0dd87bc6b.png\")
<\/p>\n
<\/p>\n
Lai v\u0113l vair\u0101k sare\u017e\u0123\u012btu to, m\u0113s izveidosim v\u0113l vienu skatu, kas \u0161oreiz nodro\u0161in\u0101s mums hierarhiju. Es negaidu, ka j\u016bs saprat\u012bsiet \u0161o piepras\u012bjumu. Ta\u010du tas dos mums skaidr\u0101ku priek\u0161statu par to, kas notiek. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/855f6dd22cba114b051e4eaac106c0ea.png\")
<\/p>\n
<\/p>\n
Tas ir rekurs\u012bvs skats, kuram ir ar\u012b v\u0113l viena sada\u013ca. Un tas v\u0113lreiz apvieno visu kop\u0101. \u013baujiet to izmantot. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/fdb54fae54fb87131c13d67a2f66fae2.png\")
<\/p>\n
<\/p>\n
Ko, ja m\u0113s veiksim tr\u012bs vienlaic\u012bgus atjaunin\u0101jumus un teiksim, ka rinda tagad ir vien\u0101da ar trim. Un m\u0113s nomain\u012bsim 3 uz 4. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/f28e0d58e223ef9ccca9ba0acab5b0ce.png\")
<\/p>\n
<\/p>\n
Un l\u016bk, m\u0113s redzam 4. Un transakcijas ID 702. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/49553b4dc4bb4c07db099fd26a6da22c.png\")
<\/p>\n
<\/p>\n
Un tad es nomain\u012b\u0161u 4 uz 5. Un 5 uz 6, un 6 uz 7. Un es rindoju cilv\u0113ku rind\u0101, kuri gaid\u012bs, lai \u0161\u012b viena transakcija nosl\u0113gtos.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7f4a4fef1745ad9cdcd602aa0474f6ec.png\")
<\/p>\n
<\/p>\n
Un viss k\u013c\u016bst skaidrs. K\u0101da ir pirm\u0101 rinda? Tas ir 702. Tas ir transakcijas ID, kas s\u0101kotn\u0113ji noteica \u0161o v\u0113rt\u012bbu. Un ko man raksta kolonn\u0101 Granted? Man ir atz\u012bmes f<\/code>. Tie ir mani atjaunin\u0101jumi (5, 6, 7), kas nevar tikt apstiprin\u0101ti, jo m\u0113s gaid\u0101m, lai transakcijas ID 702 pabeigtos. Tur ir transakcijas ID blo\u0137\u0113jums. Un t\u0101 ieg\u016bstam 5 transakciju blo\u0137\u0113jumus ID.<\/p>\n<\/p>\n
Un, ja j\u016bs paskat\u012bsieties uz 704, uz 705, tad tur v\u0113l nekas nav uzrakst\u012bts, jo vi\u0146i v\u0113l nezina, kas notiek. Vi\u0146i vienk\u0101r\u0161i raksta, ka nav ne jausmas, kas notiek. Un vi\u0146i vienk\u0101r\u0161i aizmigs, jo gaida, kad k\u0101ds pabeigs un pamodin\u0101s vi\u0146us, kad par\u0101d\u012bsies iesp\u0113ja nomain\u012bt rindu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/af97313e395a47e43d5ca9e6c78271af.png\")
<\/p>\n
<\/p>\n
T\u0101 tas izskat\u0101s. Ir skaidrs, ka vi\u0146i visi gaida 12. rindu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/611f2d09dfbf78abe6659f794d4ad4f6.png\")
<\/p>\n
<\/p>\n
Tas ir tas, ko m\u0113s redz\u0113j\u0101m \u0161eit. L\u016bk, 0\/12. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/738d1d9b272c6ef2f6e8dffd9cd9c061.png\")
<\/p>\n
<\/p>\n
T\u0101tad, kad pirm\u0101 transakcija ir apstiprin\u0101ta, j\u016bs varat redz\u0113t, k\u0101 darbojas hierarhija. Un tagad viss k\u013c\u016bst skaidrs. Vi\u0146i visi k\u013c\u016bst t\u012bri. Un vi\u0146i faktiski v\u0113l joproj\u0101m gaida. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/7f53df10dd5de8776a07094e68acd195.png\")
<\/p>\n
<\/p>\n
L\u016bk, kas notiek. 702 veic kommitu. Un tagad 703 sa\u0146em \u0161o rindu blo\u0137\u0113\u0161anu, un tad 704 s\u0101k gaid\u012bt, kad 703 veiks kommitu. Un 705 ar\u012b to gaida. Un kad viss tas beidzas, vi\u0146i pa\u0161i sevi atbr\u012bvo. Un es v\u0113l\u0113tos uzsv\u0113rt, ka visi izk\u0101rtojas rind\u0101. Un tas \u013coti atg\u0101dina situ\u0101ciju ar sastr\u0113gumu, kad visi gaida pirmo ma\u0161\u012bnu. Pirm\u0101 ma\u0161\u012bna apst\u0101j\u0101s, un visi izk\u0101rtojas gar\u0101 rind\u0101. P\u0113c tam t\u0101 p\u0101rvietojas, tad n\u0101kam\u0101 ma\u0161\u012bna var iziet uz priek\u0161u un sa\u0146emt savu blo\u0137\u0113\u0161anu utt. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/44d6e80e90fbaa3c726bec204ee10f1b.png\")
<\/p>\n
<\/p>\n
Un, ja jums \u0161\u0137ita, ka tas nav pietiekami sare\u017e\u0123\u012bti, tad tagad run\u0101sim par deadlocks. Es nezinu, kur\u0161 no jums ar to ir sask\u0101ries. Tas ir diezgan izplat\u012bts probl\u0113ma datub\u0101z\u0113s. Bet deadlocks ir t\u0101ds gad\u012bjums, kad viena sesija gaida, lai kaut ko izpild\u012btu otra sesija. Bet \u0161aj\u0101 br\u012bd\u012b otra sesija gaida, lai pirm\u0101 sesija kaut ko izpild\u012btu. <\/p>\n
<\/p>\n
Un, piem\u0113ram, ja J\u0101nis saka: \u201eDosi man kaut ko\u201d, bet es saku: \u201eN\u0113, es to do\u0161u tikai tad, ja tu man dosi kaut ko citu\u201d. Un vi\u0146\u0161 saka: \u201eN\u0113, es tev to nedo\u0161u, ja tu man nedosi\u201d. Un m\u0113s non\u0101kam mirst\u012bg\u0101 blo\u0137\u0113\u0161anas situ\u0101cij\u0101. Es esmu p\u0101rliecin\u0101ts, ka J\u0101nis t\u0101 nesaprastu, bet j\u016bs saprotat j\u0113gu, ka mums ir divi cilv\u0113ki, kas v\u0113las kaut ko ieg\u016bt, un vi\u0146i nav gatavi to nodot, kam\u0113r otrs cilv\u0113ks vi\u0146iem nedos to, ko vi\u0146i v\u0113las. Un te nav risin\u0101juma. <\/p>\n
<\/p>\n
Pamats, j\u016bsu datub\u0101zei tas j\u0101identific\u0113. Un tad ir j\u0101izdz\u0113\u0161 vai j\u0101sit vienu no sesij\u0101m, jo cit\u0101di t\u0101s paliks tur uz visiem laikiem. Un m\u0113s to redzam datub\u0101z\u0113s, m\u0113s to redzam oper\u0113t\u0101jsist\u0113m\u0101s. Un visur, kur mums ir paral\u0113li procesi, tas var notikt. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/6acee6bc304593cdd9e411882b5e3406.png\")
<\/p>\n
<\/p>\n
Un tagad m\u0113s izveidosim divus deadlocks. M\u0113s izveidosim 50 un 80. Pirmaj\u0101 rind\u0101 es veicu atjaunin\u0101\u0161anu no 50 uz 50. Man izn\u0101k dar\u012bjuma numurs 710. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/28a2a7fb4f91b8f8c6c78affb32d249c.png\")
<\/p>\n
<\/p>\n
Un p\u0113c tam es main\u012b\u0161u 80 uz 81, un 50 uz 51. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/f6653c36f5a09934035f4838ff0fa380.png\")
<\/p>\n
<\/p>\n
Un l\u016bk, k\u0101 tas izskat\u012bsies. Un t\u0101p\u0113c 710 ir rindu blo\u0137\u0113\u0161ana, bet 711 gaida apstiprin\u0101jumu. M\u0113s to redz\u0113j\u0101m, kad atjaunin\u0101j\u0101m. 710 ir m\u016bsu rindu \u012bpa\u0161nieks. Bet 711 gaida, lai 710 pabeigtu dar\u012bjumu.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/99718717ffb2024f4cb92509c2f2b878.png\")
<\/p>\n
<\/p>\n
Un tur pat ir uzrakst\u012bts, uz k\u0101d\u0101 tie\u0161i rind\u0101 mums notiek deadlocks. Un \u0161eit s\u0101k k\u013c\u016bt d\u012bvaini.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/423fc8973c29be1504d5bf8828beaa71.png\")
<\/p>\n
<\/p>\n
Tagad m\u0113s atjaunin\u0101m 80 uz 80. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/3a4b24e58f96205dbdfa8480fac2262a.png\")
<\/p>\n
<\/p>\n
Un t\u0101 ir vieta, kur s\u0101kas deadlocks. 710 gaida atbildi no 711, bet 711 gaida atbildi no 710. Un tam neb\u016bs labas beigas. Un nav izejas. Vi\u0146i gaid\u012bs atbildes viens no otra. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/19394fa0b4a19991a4a4cc1e181c47e8.png\")
<\/p>\n
<\/p>\n
Un tas viss s\u0101ks aizkav\u0113ties. Un m\u0113s to negribam. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/94c86723b8b42861d682c75f0c54b552.png\")
<\/p>\n
<\/p>\n
Un Postgres ir veidi, k\u0101 paman\u012bt, kad tas notiek. Un kad tas notiek, tad j\u016bs sa\u0146emat \u0161\u0101du k\u013c\u016bdu. Un no t\u0101 ir skaidrs, ka viens process gaida SHARE LOCK no otra procesa, t. i., tas, kuru blo\u0137\u0113 711. process. Un tas process gaid\u012bja, lai tiktu pie\u0161\u0137irts SHARE LOCK uz k\u0101du transakcijas ID un tika blo\u0137\u0113ts ar k\u0101du citu procesu. T\u0101p\u0113c \u0161eit ir mirst\u012bga blo\u0137\u0113\u0161ana.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/88a499d8f03be3c9f5bc6c93aa39c9a6.png\")
<\/p>\n
<\/p>\n
Bet vai ir iesp\u0113jama tr\u012bsbiedru deadlocks? Vai tas ir iesp\u0113jams? J\u0101. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/20fae4c504b794b678d265a0b1e9e33d.png\")
<\/p>\n
<\/p>\n
M\u0113s ievadam \u0161os skait\u013cus tabul\u0101. M\u0113s main\u0101m 40 uz 40, m\u0113s veicam blo\u0137\u0113\u0161anu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/67b7302545781681b182d4f6ddc93d9f.png\")
<\/p>\n
<\/p>\n
M\u0113s main\u0101m 60 uz 61, 80 uz 81. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/87aa9680b0160be0d798b2697a0f425d.png\")
<\/p>\n
<\/p>\n
Un tad m\u0113s main\u0101m 80, un tad - bums! <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/dc6179e048dfb42e1e11b75f956182c1.png\")
<\/p>\n
<\/p>\n
Un 714 tagad gaida 715. 716. gaida 715. Un ar to vairs neko nevar izdar\u012bt.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/e5e190437f420783f4fca26f4f04ecd3.png\")
<\/p>\n
<\/p>\n
\u0160eit jau nav divi cilv\u0113ki, \u0161eit jau ir tr\u012bs cilv\u0113ki. Es gribu kaut ko no tevis, \u0161is v\u0113las kaut ko no tre\u0161\u0101 cilv\u0113ka, bet tre\u0161ais cilv\u0113ks v\u0113las kaut ko no manis. Un m\u0113s non\u0101kam tr\u012bsbiedru gaid\u012b\u0161an\u0101, jo visi gaid\u0101m, kam\u0113r cits cilv\u0113ks pabeigs to, kas vi\u0146am j\u0101dara. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/fde8e3cb14dc05c520693ca2a99cf5f1.png\")
<\/p>\n
<\/p>\n
Un Postgres zina, kur\u0101 rind\u0101 tas notiek. T\u0101p\u0113c vi\u0146\u0161 izsniegs jums sekojo\u0161o zi\u0146ojumu, kas par\u0101da, ka jums ir probl\u0113ma, kur tr\u012bs ievades blo\u0137\u0113 viena otru. Un \u0161eit nav ierobe\u017eojumu. Tas var b\u016bt gad\u012bjum\u0101, kad 20 ieraksti blo\u0137\u0113 viens otru. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/a3dde7b6092b3d2e93c4927b6b42d795.png\")
<\/p>\n
<\/p>\n
N\u0101kam\u0101 probl\u0113ma ir serializable. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/9bff965bafaa03a160c0787bfa8606fb.png\")
<\/p>\n
<\/p>\n
Ja ir \u012bpa\u0161a serializable blo\u0137\u0113\u0161ana.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/db310da4461e647ed498e58b1e878fda.png\")
<\/p>\n
<\/p>\n
Un atgrie\u017eamies pie 719. Tam ir piln\u012bgi norm\u0101ls izn\u0101kums. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/e7eca0a2191a62014ad949583a45bf2f.png\")
<\/p>\n
<\/p>\n
Un j\u016bs varat nospiest, lai izveidotu transakciju no serializable.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/4aa78f3e4d07b8ca48d860e8df2dacda.png\")
<\/p>\n
<\/p>\n
Un j\u016bs saprotat, ka tagad jums ir cita veida blo\u0137\u0113\u0161ana SA - tas noz\u012bm\u0113 serializable. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/f9e79b9b6b1cc581b2178b0285bd3213.png\")
<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/603dc15cdda0a128c82e87275ca8ba85.png\")
<\/p>\n
<\/p>\n
T\u0101p\u0113c mums ir jauna blo\u0137\u0113\u0161anas veids, ko sauc par SARieadLock, kas ir s\u0113rijveida blo\u0137\u0113\u0161ana un \u013cauj ievad\u012bt s\u0113rijas numurus.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ce2644b0e8d8b71035044c56a94621f4.png\")
<\/p>\n
<\/p>\n
Un j\u016bs ar\u012b varat ievietot unik\u0101las indeksus. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/053a37128688fe22040a2c5a3d5ad477.png\")
<\/p>\n
<\/p>\n
\u0160aj\u0101 tabul\u0101 mums ir unik\u0101lie indeksi.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/284e50ea2b0b64a9efda8b9224f466ae.png\")
<\/p>\n
<\/p>\n
T\u0101p\u0113c, ja es \u0161eit ievadu skaitli 2, t\u0101p\u0113c man ir 2. Bet aug\u0161\u0101 es ievietoju v\u0113l vienu 2. Un j\u016bs varat redz\u0113t, ka 721. ir ekskluz\u012bvs blo\u0137\u0113jums. Bet tagad 722 gaida, lai 721 pabeigtu savu oper\u0101ciju, jo vi\u0146\u0161 nevar ievietot 2, pirms nezin, kas notiks ar 721. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ee01a45e1e16fe0ab10f5d172048f35f.png\")
<\/p>\n
<\/p>\n
Un ja m\u0113s veicam subtransaction.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/58a67f29a50c5e90d3770492eb7a8fc4.png\")
<\/p>\n
<\/p>\n
\u0160eit mums ir 723.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/6684b92639d48e62b6abf55bea86a951.png\")
<\/p>\n
<\/p>\n
Ja, ja saglab\u0101jam punktu un p\u0113c tam to atjaunin\u0101m, tad mums rodas jauns transakcijas ID. Tas ir v\u0113l viens uzved\u012bbas raksturs, par kuru jums j\u0101zina. Ja m\u0113s to atgrie\u017eam, tad transakcijas ID ir prom. 724 ir prom. Bet tagad mums par\u0101d\u0101s 725. <\/p>\n
<\/p>\n
Un ko es \u0161eit cen\u0161os dar\u012bt? Es cen\u0161os jums par\u0101d\u012bt piem\u0113rus neparast\u0101m blo\u0137\u0113\u0161an\u0101m, kuras varat sastapt: vai tas b\u016btu serializable blo\u0137\u0113jumi vai SAVEPOINT \u2013 tie ir at\u0161\u0137ir\u012bgi blo\u0137\u0113jumu veidi, kas par\u0101d\u012bsies blo\u0137\u0113jumu tabul\u0101. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/3e0b5d4b4e84ca18e0e33cb46013cd70.png\")
<\/p>\n
<\/p>\n
Tas ir eksplic\u012btu (skaidru) blo\u0137\u0113jumu izveide, kuriem ir pg_advisory_lock. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/2ce5ecd96cec5bcf0f757eb0093759c3.png\")
<\/p>\n
<\/p>\n
Un j\u016bs redzat, ka blo\u0137\u0113juma veids \u0161eit ir nor\u0101d\u012bts k\u0101 advisory. Un \u0161eit sarkan\u0101 kr\u0101s\u0101 ir uzrakst\u012bts \u00abadvisory\u00bb. Un j\u016bs varat vienlaikus t\u0101 blo\u0137\u0113t ar pg_advisory_unlock.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5b5ae66d50ce9aaa779bbf47de00b1d7.png\")
<\/p>\n
<\/p>\n
Un nosl\u0113dzot, es v\u0113l\u0113tos jums par\u0101d\u012bt v\u0113l vienu nogalino\u0161u lietu. Es izveido\u0161u v\u0113l vienu veidu. Bet es savieno\u0161anu tabulu pg_locks ar tabulu pg_stat_activity. Un k\u0101p\u0113c es to v\u0113los dar\u012bt? T\u0101p\u0113c, ka tas \u013caus man apskat\u012bt un redz\u0113t visas pa\u0161reiz\u0113j\u0101s sesijas un redz\u0113t, k\u0101das tie\u0161i blo\u0137\u0113\u0161anas t\u0101s gaida. Un tas ir diezgan interesanti, kad m\u0113s apvienojam blo\u0137\u0113jumu tabulu un piepras\u012bjumu tabulu. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/3949e48d9626a7a030b033dd05e7f042.png\")
<\/p>\n
<\/p>\n
Un \u0161eit m\u0113s izveidojam pg_stat_view. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/2b3b7b34bb35f5a67ac5d4ed1328a910.png\")
<\/p>\n
<\/p>\n
Un m\u0113s atjaunin\u0101m rindu par vienu. Un \u0161eit m\u0113s redzam 724. P\u0113c tam m\u0113s atjaunin\u0101m m\u016bsu rindu l\u012bdz trim. Un ko j\u016bs tagad redzat? Tie ir piepras\u012bjumi, tas ir, j\u016bs redzat visu piepras\u012bjumu sarakstu, kas ir uzskait\u012bts kreisaj\u0101 kolonn\u0101. Un p\u0113c tam labaj\u0101 pus\u0113 j\u016bs varat redz\u0113t blo\u0137\u0113jumus un to, ko tie rada. Un tas var b\u016bt saprotam\u0101k jums, lai jums neb\u016btu j\u0101atgrie\u017eas katru reizi pie katras sesijas un j\u0101skat\u0101s \u2013 vai vajag to pievienot vai ne. Mums to paveic. <\/p>\n
<\/p>\n
V\u0113l viena funkcija, kas ir \u013coti noder\u012bga \u2013 t\u0101 ir pg_blocking_pids<\/code>J\u016bs, iesp\u0113jams, to nekad neesat dzird\u0113ju\u0161i. Ko tas dara? Tas \u013cauj mums pateikt, ka \u0161ai sesijai 11740, k\u0101di prec\u012bzi ID procesi t\u0101 gaida. Un j\u016bs varat redz\u0113t, ka 11740 gaida 724. Un 724 ir pa\u0161\u0101 aug\u0161\u0101. Savuk\u0101rt 11306 ir j\u016bsu procesa ID. Pamat\u0101 \u0161\u012b funkcija p\u0101rl\u016bko j\u016bsu blo\u0137\u0113\u0161anas tabulu. Un es zinu, ka tas ir nedaudz sare\u017e\u0123\u012bti, ta\u010du jums tas izdodas saprast. Pamat\u0101 \u0161\u012b funkcija p\u0101rskata \u0161o blo\u0137\u0113\u0161anas tabulu un cen\u0161as atrast, kur atrodas \u0161is procesa ID, \u0146emot v\u0113r\u0101 t\u0101s blo\u0137\u0113\u0161anas, kuras t\u0101 gaida. T\u0101 ar\u012b m\u0113\u0123ina apr\u0113\u0137in\u0101t, k\u0101ds tie\u0161i procesa ID ir tam procesam, kur\u0161 gaida blo\u0137\u0113\u0161anas. T\u0101d\u0113j\u0101di j\u016bs varat palaist \u0161o funkciju. pg_blocking_pids<\/code>.<\/p>\n<\/p>\n
Un tas ir \u013coti noder\u012bgi. M\u0113s to pievienoj\u0101m tikai ar 9.6 versiju, t\u0101p\u0113c \u0161ai funkcijai ir tikai 5 gadi, bet t\u0101 ir \u013coti, \u013coti noder\u012bga. Tas pats attiecas uz otro piepras\u012bjumu. Tas par\u0101da tie\u0161i to, ko mums vajag redz\u0113t. <\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/d1975be02ba0cf516ba7bce400228e08.png\")
<\/p>\n
<\/p>\n
Tas ir tas, par ko es grib\u0113ju ar jums run\u0101t. Un k\u0101 es gaid\u012bju, m\u0113s izmantoj\u0101m visu m\u016bsu laiku, jo bija tik daudz slaidu. Un slaidi ir pieejami lejupiel\u0101dei. Es v\u0113l\u0113tos pateikties jums, ka bij\u0101t \u0161eit. Esmu p\u0101rliecin\u0101ts, ka jums patiks p\u0101r\u0113j\u0101 konference, liels paldies!<\/p>\n
<\/p>\n
Jaut\u0101jumi:<\/p>\n
<\/p>\n
Piem\u0113ram, ja es cen\u0161os atjaunin\u0101t rindas, bet otrais sesija cen\u0161as dz\u0113st visu tabulu. Cik es saprotu, tur vajadz\u0113tu b\u016bt kaut kas l\u012bdz\u012bgs intent lock. Vai Postgres ir t\u0101ds?<\/em><\/p>\n<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/ffe86d27f8bc467aeb3884320258d032.png\")
<\/p>\n
<\/p>\n
Atgrie\u017eamies pie pa\u0161a s\u0101kuma. Iesp\u0113jams, j\u016bs atceraties, ka, kad j\u016bs dar\u0101t jebko, piem\u0113ram, kad veicat SELECT, m\u0113s izsniedzam AccessShareLock. Un tas nov\u0113r\u0161 tabulas dz\u0113\u0161anu. T\u0101p\u0113c, ja j\u016bs, piem\u0113ram, v\u0113laties atjaunin\u0101t rindu tabul\u0101 vai dz\u0113st rindu, tad k\u0101ds nevar dz\u0113st visu tabulu vienlaic\u012bgi ar to, jo j\u016bs turat \u0161o AccessShareLock virs visas tabulas un virs rindas. Un tikl\u012bdz esat pabeigu\u0161i, vi\u0146i var to dz\u0113st. Bet, kam\u0113r j\u016bs tie\u0161i kaut ko tur main\u0101t, vi\u0146i to nevar izdar\u012bt.<\/p>\n
<\/p>\n
Apskat\u012bsim v\u0113lreiz. P\u0101riesim pie dz\u0113\u0161anas piem\u0113ra. Un j\u016bs redzat, k\u0101 rind\u0101 ir ekskluz\u012bvs blo\u0137\u0113jums visai tabulai. <\/p>\n
<\/p>\n
Tas izskat\u012bsies k\u0101 ekskluz\u012bvs blo\u0137\u0113jums, pareizi?<\/em><\/p>\n<\/p>\n
J\u0101, tas izskat\u0101s t\u0101. Es saprotu, par ko j\u016bs run\u0101jat. J\u016bs sak\u0101t, ka, ja es izpild\u012b\u0161u SELECT, man b\u016bs ShareExclusive, un tad es to p\u0101rv\u0113rstu Row Exclusive, vai tas k\u013c\u016bst par probl\u0113mu? Bet p\u0101rsteidzo\u0161i tas nerada probl\u0113mas. Tas izskat\u0101s k\u0101 blo\u0137\u0113\u0161anas pak\u0101pes palielin\u0101\u0161ana, bet b\u016bt\u012bb\u0101 man ir blo\u0137\u0113\u0161ana, kas nov\u0113r\u0161 dz\u0113\u0161anu. Un tagad, kad es \u0161o blo\u0137\u0113\u0161anu padaru sp\u0113c\u012bg\u0101ku, t\u0101 joproj\u0101m nov\u0113r\u0161 dz\u0113\u0161anu. T\u0101tad, tas nav t\u0101, ka es k\u0101ptu augst\u0101k. Proti, tas nov\u0113rsa to ar\u012b tad, kad tas bija zem\u0101k\u0101 l\u012bmen\u012b, t\u0101p\u0113c, kad es paaugstinu t\u0101 l\u012bmeni, tas joproj\u0101m nov\u0113r\u0161 tabulas dz\u0113\u0161anu. <\/p>\n
<\/p>\n
Es saprotu, par ko j\u016bs run\u0101jat. \u0160eit nav situ\u0101cijas, kad blo\u0137\u0113\u0161anas pak\u0101pe palielin\u0101s, kur j\u016bs m\u0113\u0123in\u0101t atteikties no vienas blo\u0137\u0113\u0161anas, lai ieviestu sp\u0113c\u012bg\u0101ku. \u0160eit tas vienk\u0101r\u0161i visur palielina \u0161o nov\u0113r\u0161anu, t\u0101d\u0113\u013c tas nerada konfliktus. Bet tas ir labs jaut\u0101jums. Liels paldies, ka to uzdev\u0101t!<\/p>\n
<\/p>\n
Ko mums nepiecie\u0161ams dar\u012bt, lai izvair\u012btos no deadlock situ\u0101cijas, kad mums ir daudz sesiju, liels lietot\u0101ju skaits?<\/em><\/p>\n<\/p>\n
Postgres autom\u0101tiski paman\u012bs deadlock situ\u0101cijas. Un autom\u0101tiski izdz\u0113s\u012bs vienu no sesij\u0101m. Vien\u012bgais veids, k\u0101 izvair\u012bties no miru\u0161aj\u0101m blo\u0137\u0113\u0161an\u0101m, ir blo\u0137\u0113t cilv\u0113kus taj\u0101 pa\u0161\u0101 sec\u012bb\u0101. T\u0101tad, kad j\u016bs skat\u0101ties uz savu lietotni, bie\u017ei vien deadlock iemesls ... Iedom\u0101jieties, ka es gribu blo\u0137\u0113t divas da\u017e\u0101das lietas. Viens lietotne blo\u0137\u0113 tabulu 1, bet otra lietotne blo\u0137\u0113 2, un p\u0113c tam tabulu 1. Un visvienk\u0101r\u0161\u0101kais veids, k\u0101 izvair\u012bties no deadlocks, ir paskat\u012bties uz savu lietotni un m\u0113\u0123in\u0101t nodro\u0161in\u0101t, lai blo\u0137\u0113\u0161ana notiktu taj\u0101 pa\u0161\u0101 sec\u012bb\u0101 vis\u0101s lietotn\u0113s. Un tas parasti nov\u0113r\u0161 80% probl\u0113mu, jo da\u017e\u0101di cilv\u0113ki raksta \u0161\u012bs lietotnes. Un, ja j\u016bs tos blo\u0137\u0113jat taj\u0101 pa\u0161\u0101 sec\u012bb\u0101, tad j\u016bs nesaskarsities ar deadlock situ\u0101ciju.<\/p>\n
<\/p>\n
Liels paldies par j\u016bsu runu! J\u016bs run\u0101j\u0101t par vacuum full un, ja es pareizi saprotu, vacuum full deform\u0113 ierakstu k\u0101rt\u012bbu atsevi\u0161\u0137\u0101 glab\u0101\u0161an\u0101, t\u0101p\u0113c eso\u0161ie ieraksti paliek nemain\u012bgi. Un k\u0101p\u0113c vacuum full ieg\u016bst ekskluz\u012bvas blo\u0137\u0113\u0161anas piek\u013cuvi un k\u0101p\u0113c tas non\u0101k konflikt\u0101 ar rakst\u012b\u0161anas oper\u0101cij\u0101m?<\/em><\/p>\n<\/p>\n
Tas ir labs jaut\u0101jums. Iemesls ir t\u0101ds, ka vacuum full aiz\u0146em tabulu. Un m\u0113s b\u016bt\u012bb\u0101 izveidojam jaunu tabulas versiju. T\u0101p\u0113c tabula b\u016bs jauna. Rezult\u0101t\u0101 t\u0101 b\u016bs piln\u012bgi jauna tabulas versija. Un probl\u0113ma ir t\u0101da, ka, kad m\u0113s to dar\u0101m, m\u0113s nev\u0113lamies, lai cilv\u0113ki to lasa, jo mums ir nepiecie\u0161ams, lai vi\u0146i redz\u0113tu jauno tabulu. T\u0101p\u0113c tas saist\u0101s ar iepriek\u0161\u0113jo jaut\u0101jumu. Ja vienlaikus var\u0113tu las\u012bt, m\u0113s nevar\u0113tu to p\u0101rvietot un novirz\u012bt cilv\u0113kus uz jauno tabulu. Mums b\u016btu j\u0101gaida, lai katrs pabeigtu las\u012bt \u0161o tabulu, un t\u0101p\u0113c, b\u016bt\u012bb\u0101, ir situ\u0101cija ar ekskluz\u012bvu blo\u0137\u0113\u0161anu.
\nM\u0113s vienk\u0101r\u0161i sak\u0101m, ka m\u0113s blo\u0137\u0113jam no pa\u0161a s\u0101kuma, jo m\u0113s zin\u0101m, ka pa\u0161\u0101 beig\u0101s mums b\u016bs nepiecie\u0161ama ekskluz\u012bva blo\u0137\u0113\u0161ana, lai p\u0101rvietotu visus uz jauno kopiju. T\u0101p\u0113c potenci\u0101li m\u0113s varam to \u013caut. Un m\u0113s to dar\u0101m ar vienlaic\u012bgu indeks\u0113\u0161anu. Bet to ir daudz gr\u016bt\u0101k izdar\u012bt. Un tas \u013coti cie\u0161i attiecas uz j\u016bsu iepriek\u0161\u0113jo jaut\u0101jumu par ekskluz\u012bvu blo\u0137\u0113\u0161anu.<\/p>\n
<\/p>\n
Vai ir iesp\u0113jams pievienot blo\u0137\u0113\u0161anas laika ierobe\u017eojumu Postgres? Oracle es varu, piem\u0113ram, uzrakst\u012bt \"izv\u0113l\u0113t atjaunin\u0101\u0161anai\" un gaid\u012bt 50 sekundes l\u012bdz atjaunin\u0101\u0161anai. Tas bija labi lietotnei. Bet Postgres man j\u0101veic nekav\u0113joties un nevienu mirkli negaidot, vai j\u0101gaida l\u012bdz noteiktam laikam.<\/em> <\/p>\n<\/p>\n
J\u0101, j\u016bs varat izv\u0113l\u0113ties laika ierobe\u017eojumu j\u016bsu blo\u0137\u0113\u0161anai, uz j\u016bsu locks. J\u016bs ar\u012b varat izdot komandu no way, kas b\u016bs ..., ja j\u016bs nevar\u0113siet nekav\u0113joties ieg\u016bt blo\u0137\u0113\u0161anu. T\u0101tad vai nu blo\u0137\u0113\u0161anas laika ierobe\u017eojums, vai kas cits, kas to at\u013caus. Tas nav izdar\u0101ms sintaktisk\u0101 l\u012bmen\u012b. Tas tiek dar\u012bts k\u0101 main\u012bgais server\u012b. Da\u017ereiz to nevar izmantot.<\/p>\n
<\/p>\n
Vai j\u016bs varat atv\u0113rt 75. slaidu?<\/em> <\/p>\n<\/p>\n
J\u0101.<\/p>\n
<\/p>\n
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/5f0f3ee9ba29e0c231dd6e0eb66b4f6c.png\")
<\/p>\n
<\/p>\n
Un mans jaut\u0101jums ir \u0161\u0101ds: k\u0101p\u0113c abi atjaunin\u0101\u0161anas procesi gaida 703?<\/em><\/p>\n<\/p>\n
Un tas ir lielisks jaut\u0101jums. Es nesaprotu, k\u0101p\u0113c Postgres to dara. Bet, kad tika izveidots 703, tas gaid\u012bja 702. Un, kad par\u0101d\u0101s 704 un 705, \u0161\u0137iet, ka tie nezina, ko gaida, jo tur v\u0113l nekas nav. Un Postgres to dara \u0161\u0101di: kad j\u016bs nevarat ieg\u016bt blo\u0137\u0113\u0161anu, tas raksta: \"K\u0101ds j\u0113ga j\u016bs apstr\u0101d\u0101t?\", jo j\u016bs jau gaid\u0101t k\u0101du. T\u0101p\u0113c \u013causim tam kar\u0101jamies gais\u0101, tas visp\u0101r to neatjauno. Bet kas \u0161eit notika? Tikl\u012bdz 702 pabeidz procesu un 703 sa\u0146em savu blo\u0137\u0113\u0161anu, sist\u0113ma atgrie\u017eas atpaka\u013c. Un saka, ka tagad mums ir divi cilv\u0113ki, kas gaida. Un tad \u013causim vi\u0146iem abus atjaunot kop\u0101. Un nor\u0101d\u012bsim, ka abi gaida. <\/p>\n
<\/p>\n
Es nezinu, k\u0101p\u0113c Postgres t\u0101 dara. Bet ir probl\u0113ma, ko sauc par f\u2026. Man \u0161\u0137iet, ka tas nav termins krieviski. Tas ir, kad visi gaida vienu blo\u0137\u0113\u0161anu, pat ja ir 20 instan\u010du, kas gaida blo\u0137\u0113\u0161anu. Un p\u0113k\u0161\u0146i vi\u0146i visi vienlaikus pamostas. Un visi s\u0101k m\u0113\u0123in\u0101t rea\u0123\u0113t. Bet sist\u0113ma dara t\u0101, ka visi gaida 703. Jo vi\u0146i visi gaida, un m\u0113s vi\u0146us uzreiz visus izk\u0101rtojam rind\u0101. Un, ja par\u0101d\u0101s jebkur\u0161 cits jauns piepras\u012bjums, kas tika veidots p\u0113c tam, piem\u0113ram, 707, tad tur atkal b\u016bs tuk\u0161ums. <\/p>\n
<\/p>\n
Un man \u0161\u0137iet, ka tas tiek dar\u012bts, lai var\u0113tu teikt, ka \u0161aj\u0101 posm\u0101 702 gaida 703, bet visiem, kas n\u0101ks p\u0113c tam, neb\u016bs nek\u0101das ieraksta \u0161aj\u0101 lauk\u0101. Bet, tikl\u012bdz pirmais gaid\u012bt\u0101js pamet, visi tie, kas gaid\u012bja \u0161aj\u0101 br\u012bd\u012b l\u012bdz atjauno\u0161anai, sa\u0146em to pa\u0161u mar\u0137ieri. Un t\u0101p\u0113c, man \u0161\u0137iet, tas ir izdar\u012bts, lai m\u0113s var\u0113tu apstr\u0101d\u0101t sec\u012bb\u0101, lai tie b\u016btu pareizi sak\u0101rtoti. <\/p>\n
<\/p>\n
Es vienm\u0113r uz to skat\u012bjos k\u0101 uz diezgan d\u012bvainu fenomenu. Jo \u0161eit, piem\u0113ram, m\u0113s vi\u0146us nemaz neuzskait\u0101m. Bet, man \u0161\u0137iet, katru reizi, kad m\u0113s pie\u0161\u0137iram jaunu blo\u0137\u0113\u0161anu, m\u0113s skat\u0101mies uz visiem, kas ir gaid\u012b\u0161anas proces\u0101. Tad m\u0113s vi\u0146us visus izk\u0101rtojam rind\u0101. Un tad jebkur\u0161 jauns, kas n\u0101k, nok\u013c\u016bst rind\u0101 tikai tad, kad n\u0101kamais cilv\u0113ks ir pabeidzis apstr\u0101di. \u013boti labs jaut\u0101jums. Liels paldies par jaut\u0101jumu!<\/p>\n
<\/p>\n
Man \u0161\u0137iet, daudz lo\u0123isk\u0101k, ja 705 gaida 704.<\/em> <\/p>\n<\/p>\n
Tom\u0113r \u0161eit ir probl\u0113ma. Tehniski j\u016bs varat modin\u0101t vai nu to, vai to. T\u0101d\u0113\u013c m\u0113s modin\u0101sim vienu vai otru. Bet kas notiek sist\u0113mas darb\u012bb\u0101? J\u016bs redzat, ka 703 visaug\u0161pus\u0113 blo\u0137\u0113ja savu pa\u0161a transakcijas ID. Tie\u0161i t\u0101 Postgres darbojas. Un 703 tiek blo\u0137\u0113ts ar savu pa\u0161a transakcijas ID, t\u0101p\u0113c, ja k\u0101ds v\u0113las gaid\u012bt, vi\u0146\u0161 gaid\u012bs 703. Un b\u016bt\u012bb\u0101 703 pabeidz. Un tikai p\u0113c t\u0101 pabeig\u0161anas k\u0101ds no procesiem tiek modin\u0101ts. Un m\u0113s nezin\u0101m, kur\u0161 process tas b\u016bs. P\u0113c tam m\u0113s pak\u0101peniski viss apstr\u0101d\u0101jam. Bet nav skaidrs, kur\u0161 tie\u0161i process tiek modin\u0101ts pirmais, jo tas var b\u016bt jebkur\u0161 no \u0161iem procesiem. B\u016bt\u012bb\u0101 mums bija pl\u0101not\u0101js, kur\u0161 teica, ka tagad varam modin\u0101t jebkuru no \u0161iem procesiem. M\u0113s vienk\u0101r\u0161i izv\u0113lamies vienu nejau\u0161i. T\u0101d\u0113\u013c abiem no tiem j\u0101b\u016bt atz\u012bm\u0113tiem, jo m\u0113s varam modin\u0101t jebkuru no tiem. <\/p>\n
<\/p>\n
Un probl\u0113ma ir t\u0101da, ka mums ir CP-bezgala. T\u0101d\u0113\u013c ir \u013coti iesp\u0113jams, ka m\u0113s varam modin\u0101t v\u0113l\u0101ko. Un, piem\u0113ram, ja m\u0113s modin\u0101m v\u0113l\u0101ko, gaid\u012bsim to, kur\u0161 tikko ieguva blo\u0137\u0113\u0161anu, t\u0101p\u0113c m\u0113s nosak\u0101m, kur\u0161 tie\u0161i tiks modin\u0101ts pirmais. M\u0113s vienk\u0101r\u0161i izveidojam t\u0101du situ\u0101ciju, un sist\u0113ma tos modin\u0101s nejau\u0161\u0101 sec\u012bb\u0101. <\/p>\n
<\/p>\n
Ir
![\"Postgres](\"\/wp-content\/uploads\/2020\/07\/1f75fbb0cb41c306c33c823ef55f7238.png\")
<\/p>\n