http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex><\/p>\n<\/p>\n
Shikojm\u00eb. Me ngjyr\u00eb t\u00eb kuqe \u00ebsht\u00eb nxjerr\u00eb numri i transaksionit. K\u00ebtu shfaqet funksioni SELECT pg_back. Ai kthen transaksionin tim dhe ID-n\u00eb e atij transaksioni. <\/p>\n
<\/p>\n
Nj\u00eb pika tjet\u00ebr \u00ebsht\u00eb q\u00eb, n\u00ebse ju p\u00eblqen kjo prezantim dhe d\u00ebshironi ta shkarkoni n\u00eb baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave, mund t\u00eb kaloni n\u00eb k\u00ebt\u00eb lidhje t\u00eb theksuar me ngjyr\u00eb roz\u00eb dhe t\u00eb shkarkoni SQL p\u00ebr k\u00ebt\u00eb prezantim. Mund ta ekzekutoni n\u00eb PSQL tuaj dhe e gjith\u00eb prezantimi do t\u00eb shfaqet menj\u00ebher\u00eb n\u00eb ekranin tuaj. Nuk do t\u00eb p\u00ebrmbaj\u00eb ngjyra, por s\u00eb paku do ta shohim.<\/p>\n
<\/p>\n
<\/p>\n
N\u00eb k\u00ebt\u00eb rast shohim ID-n\u00eb e transaksionit. Ky \u00ebsht\u00eb numri q\u00eb i kemi caktuar. Ka edhe nj\u00eb lloj tjet\u00ebr ID transaksioni n\u00eb Postgres, i quajtur ID virtual e transaksionit.<\/p>\n
<\/p>\n
Dhe ne duhet ta kuptojm\u00eb k\u00ebt\u00eb. \u00cbsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme, ndryshe nuk do t\u00eb mund ta kuptojm\u00eb bllokimin n\u00eb Postgres.<\/p>\n
<\/p>\n
ID virtual e transaksionit \u00ebsht\u00eb ID e nj\u00eb transaksioni q\u00eb nuk p\u00ebrmban vlera t\u00eb p\u00ebrhershme. P\u00ebr shembull, n\u00ebse un\u00eb ekzekutoj komand\u00ebn SELECT, do t\u00eb ishte e mundur q\u00eb t\u00eb mos nd\u00ebrroj baz\u00ebn e t\u00eb dh\u00ebnave, nuk do t\u00eb bllokoja asgj\u00eb. Prandaj, kur ekzekutojm\u00eb nj\u00eb SELECT t\u00eb thjesht\u00eb, nuk i japim k\u00ebsaj transaksioni nj\u00eb ID t\u00eb p\u00ebrhershme. Atij i japim vet\u00ebm nj\u00eb ID virtual.<\/p>\n
<\/p>\n
Dhe kjo rrit performanc\u00ebn e Postgres, p\u00ebrmir\u00ebson mund\u00ebsit\u00eb p\u00ebr pastrim, prandaj ID virtual i transaksionit p\u00ebrb\u00ebhet nga dy numra. Numri i par\u00eb para shkronj\u00ebs \u00ebsht\u00eb ID i backend-it. Nd\u00ebrsa n\u00eb t\u00eb djatht\u00eb shohim thjesht nj\u00eb num\u00ebr.<\/p>\n
<\/p>\n
<\/p>\n
Prandaj, n\u00ebse ekzekutoj nj\u00eb k\u00ebrkes\u00eb, ai thot\u00eb se ID i backend-it \u00ebsht\u00eb 2. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse un\u00eb ekzekutoj nj\u00eb seri transaksionesh t\u00eb tilla, shohim se numri rritet \u00e7do her\u00eb, kur ekzekutoj k\u00ebrkes\u00ebn. P\u00ebr shembull, kur ekzekutoj k\u00ebrkes\u00ebn 2\/10, 2\/11, 2\/12 etj. <\/p>\n
<\/p>\n
<\/p>\n
Mbani parasysh se k\u00ebtu jan\u00eb dy kolona. N\u00eb t\u00eb majt\u00eb shohim ID-n\u00eb virtuale t\u00eb transaksionit \u2013 2\/12. Nd\u00ebrsa n\u00eb t\u00eb djatht\u00eb kemi ID t\u00eb p\u00ebrhersh\u00ebm t\u00eb transaksionit. Dhe ky fush\u00eb \u00ebsht\u00eb bosh. Dhe ky transaksion nuk modifikon baz\u00ebn e t\u00eb dh\u00ebnave. Prandaj, nuk i caktova nj\u00eb ID t\u00eb p\u00ebrhersh\u00ebm transaksioni.<\/p>\n
<\/p>\n
<\/p>\n
Sapo t\u00eb ekzekutoj komand\u00ebn analiz\u00eb (ANALYZE), i nj\u00ebjti k\u00ebrkes\u00eb m\u00eb jep nj\u00eb ID t\u00eb p\u00ebrhersh\u00ebm t\u00eb transaksionit. Shikoni si ka ndryshuar kjo. M\u00eb par\u00eb nuk e kisha k\u00ebt\u00eb ID, tani e kam.<\/p>\n
<\/p>\n
<\/p>\n
Pra, k\u00ebtu \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb tjet\u00ebr, nj\u00eb transaksion tjet\u00ebr. Numri virtual i transaksionit \u00ebsht\u00eb 2\/13. Dhe n\u00ebse k\u00ebrkoj ID t\u00eb p\u00ebrhersh\u00ebm t\u00eb transaksionit, at\u00ebher\u00eb, kur do t\u00eb ekzekutoj k\u00ebrkes\u00ebn, do ta marr. <\/p>\n
<\/p>\n
<\/p>\n
Pra, p\u00ebrs\u00ebri. Kemi ID virtual t\u00eb transaksionit dhe ID t\u00eb p\u00ebrhersh\u00ebm t\u00eb transaksionit. Thjesht kuptoni k\u00ebt\u00eb pik\u00eb p\u00ebr t\u00eb kuptuar sjelljen e Postgres.<\/p>\n
<\/p>\n
<\/p>\n
Kemi duke kaluar n\u00eb seksionin e tret\u00eb. K\u00ebtu do t\u00eb kalojm\u00eb n\u00ebp\u00ebr llojet e ndryshme t\u00eb bllokimeve n\u00eb Postgres. Nuk \u00ebsht\u00eb shum\u00eb interesante. Seksioni i fundit do t\u00eb jet\u00eb shum\u00eb m\u00eb interesant. Por duhet t\u00eb shqyrtojm\u00eb gj\u00ebrat bazike, sepse p\u00ebrndryshe nuk do ta kuptojm\u00eb at\u00eb q\u00eb do t\u00eb vij\u00eb m\u00eb pas. <\/p>\n
<\/p>\n
Ne do t\u00eb kalojm\u00eb n\u00ebp\u00ebr k\u00ebt\u00eb seksion, do t\u00eb shohim \u00e7do lloj bllokimi. Dhe do t'ju tregoj shembuj se si vendosen, si funksionojn\u00eb, do t'ju tregoj disa pyetje q\u00eb mund t\u00eb p\u00ebrdoren p\u00ebr t\u00eb par\u00eb se si punon bllokimi n\u00eb Postgres.<\/p>\n
<\/p>\n
<\/p>\n
P\u00ebr t\u00eb krijuar nj\u00eb pyetje dhe p\u00ebr t\u00eb par\u00eb se \u00e7far\u00eb po ndodh n\u00eb Postgres, na nevojitet t\u00eb l\u00ebshojm\u00eb nj\u00eb pyetje n\u00eb pamjen e sistemit. N\u00eb k\u00ebt\u00eb rast, n\u00eb ngjyr\u00eb t\u00eb kuqe kemi theksuar pg_lock. Pg_lock \u00ebsht\u00eb nj\u00eb tabel\u00eb sistemike q\u00eb na tregon cilat bllokime p\u00ebrdoren aktualisht n\u00eb Postgres.<\/p>\n
<\/p>\n
Megjithat\u00eb, m\u00eb \u00ebsht\u00eb shum\u00eb e v\u00ebshtir\u00eb t'ju tregoj pg_lock vet\u00eb, sepse \u00ebsht\u00eb mjaft e komplikuar. Pra, kam krijuar nj\u00eb pamje q\u00eb tregon pg_locks. Dhe ajo gjithashtu kryen p\u00ebr mua disa pun\u00eb q\u00eb m\u00eb lejojn\u00eb ta kuptoj m\u00eb mir\u00eb. Kjo do t\u00eb thot\u00eb, ajo p\u00ebrjashton bllokimet e mia, sesionin tim t\u00eb vet\u00eb, etj. Kjo \u00ebsht\u00eb thjesht SQL standard dhe m\u00eb lejon t\u00eb tregoj q\u00eb \u00ebsht\u00eb m\u00eb e qart\u00eb p\u00ebr ju se \u00e7far\u00eb ndodh. <\/p>\n
<\/p>\n
<\/p>\n
Nj\u00eb problem tjet\u00ebr \u00ebsht\u00eb se kjo pamje \u00ebsht\u00eb shum\u00eb e gjer\u00eb, prandaj m\u00eb duhet t\u00eb krijoj nj\u00eb t\u00eb dyt\u00eb \u2013 lockview2.<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n
Pra, ne krijuam nj\u00eb tabel\u00eb, e cila quhet Lockdemo. Dhe ne krijuam atje nj\u00eb rresht. Kjo \u00ebsht\u00eb tabela jon\u00eb e modelit. Dhe do t\u00eb krijojm\u00eb seksione p\u00ebr t\u00eb treguar thjesht shembuj bllokimesh. <\/p>\n
<\/p>\n
<\/p>\n
Pra, nj\u00eb rresht, nj\u00eb kolon\u00eb. Lloji i par\u00eb i bllokimit quhet ACCESS SHARE. Ky \u00ebsht\u00eb bllokimi m\u00eb pak restriktiv. Kjo do t\u00eb thot\u00eb se praktikisht nuk p\u00ebrplaset me bllokimet e tjera.<\/p>\n
<\/p>\n
Dhe n\u00ebse duam t\u00eb p\u00ebrcaktojm\u00eb shprehimisht bllokimin, ne ekzekutojm\u00eb komand\u00ebn \u00ablock table\u00bb. Ajo e bllokon qart\u00eb, pra, n\u00eb m\u00ebnyr\u00ebn ACCESS SHARE ne ekzekutojm\u00eb lock table. Dhe n\u00ebse nis PSQL n\u00eb sfond, at\u00ebher\u00eb nis k\u00ebshtu nj\u00eb sesion t\u00eb dyt\u00eb nga sesioni im i par\u00eb. \u00c7far\u00eb do t\u00eb b\u00ebj k\u00ebtu? Un\u00eb kaloj n\u00eb nj\u00eb sesion tjet\u00ebr dhe i them \u00abm\u00eb trego lockview p\u00ebr k\u00ebt\u00eb k\u00ebrkes\u00eb\u00bb. Dhe k\u00ebtu kam AccessShareLock n\u00eb k\u00ebt\u00eb tabel\u00eb. Kjo \u00ebsht\u00eb si\u00e7 e k\u00ebrkova. Dhe ai thot\u00eb se bllokimi i \u00ebsht\u00eb caktuar. Mjaft e thjesht\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
M\u00eb pas, n\u00ebse shohim n\u00eb kolon\u00ebn e dyt\u00eb, atje s\u2019ka asgj\u00eb. Jan\u00eb bosh. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse ekzekutoj komand\u00ebn \u00abSELECT\u00bb, kjo \u00ebsht\u00eb nj\u00eb m\u00ebnyr\u00eb implicit (shpreh\u00ebse) p\u00ebr t\u00eb k\u00ebrkuar AccessShareLock. Prandaj un\u00eb liroj tabel\u00ebn time dhe ekzekutoj k\u00ebrkes\u00ebn, dhe k\u00ebrkesa kthen disa rreshta. Dhe n\u00eb nj\u00eb nga rreshtat shohim AccessShareLock. Pra, SELECT shkakton AccessShareLock n\u00eb tabel\u00eb. Dhe ajo nuk krijon konflikte pothuajse me asgj\u00eb, sepse \u00ebsht\u00eb nj\u00eb bllokim me nivel t\u00eb ul\u00ebt. <\/p>\n
<\/p>\n
<\/p>\n
\u00c7far\u00eb ndodh n\u00ebse ekzekutoj SELECT dhe kam tri tabela t\u00eb ndryshme? M\u00eb par\u00eb kam ekzekutuar vet\u00ebm nj\u00eb tabel\u00eb, tani po ekzekutoj tri: pg_class, pg_namespace dhe pg_attribute.<\/p>\n
<\/p>\n
<\/p>\n
Dhe tani, kur shoh k\u00ebrkes\u00ebn, shoh 9 AccessShareLocks n\u00eb tre tabela. Pse? Me ngjyr\u00eb blu jan\u00eb theksuar tri tabelat: pg_attribute, pg_class, pg_namespace. Por gjithashtu mund t\u00eb shihni se t\u00eb gjitha indekset q\u00eb jan\u00eb caktuar p\u00ebrmes k\u00ebtyre tabelave gjithashtu kan\u00eb AccessShareLock.<\/p>\n
<\/p>\n
Dhe ky \u00ebsht\u00eb nj\u00eb bllokim q\u00eb pothuajse nuk krijon konflikte me t\u00eb tjer\u00ebt. Ajo q\u00eb b\u00ebn \u00ebsht\u00eb thjesht t\u00eb mos na lejoj\u00eb t\u00eb lirojm\u00eb tabel\u00ebn derisa ta zgjedhim. Ka kuptim. Pra, n\u00ebse zgjedhim tabel\u00ebn, ajo n\u00eb at\u00eb moment humbet, kjo \u00ebsht\u00eb e pasakt\u00eb, prandaj AccessShare \u2013 \u00ebsht\u00eb nj\u00eb bllokim i nivelit t\u00eb ul\u00ebt, i cili na thot\u00eb \"mos e fshini k\u00ebt\u00eb tabel\u00eb, derisa t\u00eb p\u00ebrfundoj pun\u00ebn\".<\/strong>. N\u00eb thelb, kjo \u00ebsht\u00eb gjith\u00e7ka q\u00eb b\u00ebn. <\/p>\n<\/p>\n
<\/p>\n
ROW SHARE \u00ebsht\u00eb nj\u00eb bllokim q\u00eb paksa ndryshon. <\/p>\n
<\/p>\n
<\/p>\n
Le t\u00eb marrim nj\u00eb shembull. SELECT ROW SHARE \u00ebsht\u00eb m\u00ebnyra e bllokimit t\u00eb \u00e7do rreshti ve\u00e7 e ve\u00e7.<\/strong>. Pra, askush nuk mund t'i fshij\u00eb apo i ndryshoj\u00eb ato derisa ne t'i shikojm\u00eb. <\/p>\n<\/p>\n
<\/p>\n
<\/p>\n
K\u00ebtu po flasim se bllokimi p\u00ebrdoret nga ne.<\/p>\n
<\/p>\n
<\/p>\n
Pra, bllokimi ekskluziv e thot\u00eb qart\u00eb, q\u00eb \u00ebsht\u00eb ekskluziv. Po ashtu, n\u00ebse fshini nj\u00eb rresht n\u00eb k\u00ebt\u00eb tabel\u00eb, at\u00ebher\u00eb kjo do t\u00eb ndodh\u00eb, si\u00e7 mund ta shihni. <\/p>\n
<\/p>\n
<\/p>\n
SHARE EXCLUSIVE \u2013 \u00ebsht\u00eb nj\u00eb bllokim m\u00eb i gjat\u00eb.<\/strong><\/p>\n<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb (ANALYZE) komanda e analizuesit, e cila do t\u00eb p\u00ebrdoret.<\/p>\n
<\/p>\n
<\/p>\n
SHARE LOCK \u2013 ju mund ta bllokoni n\u00eb m\u00ebnyr\u00eb eksplicite n\u00eb modin share.<\/p>\n
<\/p>\n
<\/p>\n
Ju gjithashtu mund t\u00eb krijoni nj\u00eb indeks unik. Dhe atje mund t\u00eb shihni SHARE LOCK, i cili \u00ebsht\u00eb pjes\u00eb e tyre. Dhe ai bllokon tabel\u00ebn dhe vendos mbi t\u00eb bllokimin SHARE LOCK.<\/p>\n
<\/p>\n
N\u00eb parazgjedhje, SHARE LOCK n\u00eb tabel\u00eb do t\u00eb thot\u00eb se njer\u00ebzit e tjer\u00eb mund t\u00eb lexojn\u00eb tabel\u00ebn, por askush nuk mund ta modifikoj\u00eb at\u00eb. Dhe kjo ndodh kur krijoni nj\u00eb indeks unik.<\/strong><\/p>\n<\/p>\n
N\u00ebse krijoj nj\u00eb indeks unik nj\u00ebkoh\u00ebsisht, at\u00ebher\u00eb do t\u00eb kem nj\u00eb lloj tjet\u00ebr bllokimi, sepse, si\u00e7 e mbani mend, p\u00ebrdorimi i indekseve nj\u00ebkoh\u00ebsisht ul k\u00ebrkesat p\u00ebr bllokim. Dhe n\u00ebse p\u00ebrdor nj\u00eb bllokim normal, indeksi normal, at\u00ebher\u00eb k\u00ebshtu do t\u00eb parandaloj shkrimin n\u00eb tabel\u00ebn e indeksit gjat\u00eb krijimit t\u00eb saj. N\u00ebse p\u00ebrdor nj\u00eb indeks nj\u00ebkoh\u00ebsisht, at\u00ebher\u00eb duhet t\u00eb p\u00ebrdor nj\u00eb lloj tjet\u00ebr bllokimi. <\/p>\n
<\/p>\n
<\/p>\n
SHARE ROW EXCLUSIVE \u2013 p\u00ebrs\u00ebri mund t\u00eb konstatohet eksplicit (qart\u00eb).<\/p>\n
<\/p>\n
<\/p>\n
Ose mund t\u00eb krijojm\u00eb nj\u00eb rregull, pra t\u00eb marrim nj\u00eb rast t\u00eb caktuar, kur do t\u00eb p\u00ebrdoret.<\/p>\n
<\/p>\n
<\/p>\n
Bllokimi EXCLUSIVE do t\u00eb thot\u00eb q\u00eb askush tjet\u00ebr nuk do t\u00eb mund ta modifikonte tabel\u00ebn. <\/p>\n
<\/p>\n
<\/p>\n
K\u00ebtu shohim lloje t\u00eb ndryshme bllokimesh. <\/p>\n
<\/p>\n
<\/p>\n
ACCESS EXCLUSIVE, p\u00ebr shembull, \u00ebsht\u00eb nj\u00eb komand\u00eb bllokimi. P\u00ebr shembull, n\u00ebse b\u00ebni CLUSTER table<\/code>, at\u00ebher\u00eb do t\u00eb thot\u00eb se askush nuk do t\u00eb mund t\u00eb shkruaj\u00eb aty. Dhe ajo bllokon jo vet\u00ebm tabel\u00ebn, por gjithashtu indekset.<\/p>\n<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb faqja e dyt\u00eb e bllokimit ACCESS EXCLUSIVE, ku shohim sakt\u00ebsisht se \u00e7far\u00eb bllokon n\u00eb tabel\u00eb. Ajo bllokon rreshta t\u00eb ve\u00e7ant\u00eb n\u00eb tabel\u00eb, q\u00eb \u00ebsht\u00eb mjaft interesante.<\/p>\n
<\/p>\n
Ky \u00ebsht\u00eb gjith\u00eb informacioni baz\u00eb q\u00eb doja t\u00eb jepja. Fol\u00ebm p\u00ebr bllokimet, p\u00ebr ID-t\u00eb e transaksioneve, fol\u00ebm p\u00ebr ID virtuale t\u00eb transaksioneve, p\u00ebr ID t\u00eb q\u00ebndrueshme t\u00eb transaksioneve. <\/p>\n
<\/p>\n
<\/p>\n
Dhe tani do t\u00eb kalojm\u00eb n\u00eb shembujt e bllokimeve. Kjo \u00ebsht\u00eb pjesa m\u00eb interesante. Do t\u00eb shohim raste shum\u00eb interesante. Dhe detyra ime n\u00eb k\u00ebt\u00eb prezantim \u00ebsht\u00eb t'ju ofroj nj\u00eb kuptim m\u00eb t\u00eb mir\u00eb t\u00eb asaj q\u00eb Postgres n\u00eb t\u00eb v\u00ebrtet\u00eb b\u00ebn kur p\u00ebrpiqet t\u00eb bllokoj\u00eb gj\u00ebra t\u00eb caktuara. M\u00eb duket se ai e b\u00ebn shum\u00eb mir\u00eb bllokimin e pjes\u00ebve t\u00eb ve\u00e7anta. <\/p>\n
<\/p>\n
Le t\u00eb shqyrtojm\u00eb disa shembuj t\u00eb caktuar.<\/p>\n
<\/p>\n
<\/p>\n
Do t\u00eb fillojm\u00eb me tabelat dhe nj\u00eb rresht n\u00eb tabel\u00eb. Kur t\u00eb insertoj di\u00e7ka, do t\u00eb shoh nj\u00eb ExclusiveLock, ID-n\u00eb e transaksionit dhe nj\u00eb ExclusiveLock n\u00eb tabel\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Por \u00e7far\u00eb do t\u00eb ndodh\u00eb n\u00ebse insertoj edhe dy rreshta t\u00eb tjer\u00eb? Tani n\u00eb tabel\u00ebn ton\u00eb kemi tre rreshta. Dhe un\u00eb insertoj nj\u00eb rresht dhe kam k\u00ebt\u00eb rezultat. Dhe n\u00ebse insertoj edhe dy rreshta, \u00e7far\u00eb \u00ebsht\u00eb e \u00e7uditshme k\u00ebtu? Ka nj\u00eb \u00e7udi, sepse un\u00eb kam shtuar tre rreshta n\u00eb k\u00ebt\u00eb tabel\u00eb, por un\u00eb ende kam dy rreshta n\u00eb bllokim. Dhe kjo, n\u00eb thelb, \u00ebsht\u00eb nj\u00eb sjellje themelore e Postgres.<\/p>\n
<\/p>\n
Shum\u00eb mendojn\u00eb se n\u00ebse n\u00eb databaz\u00eb bllokoni 100 rreshta, do t\u00eb nevojiten 100 hyrje bllokimi. N\u00ebse un\u00eb bllokoj 1 000 rreshta menj\u00ebher\u00eb, at\u00ebher\u00eb do t\u00eb m\u00eb nevojiten 1 000 k\u00ebrkesa t\u00eb tilla. Dhe n\u00ebse kam nevoj\u00eb t\u00eb bllokoj nj\u00eb milion ose nj\u00eb miliard. Por n\u00ebse do ta b\u00ebnim k\u00ebshtu, nuk do t\u00eb funksiononte mir\u00eb. N\u00ebse keni p\u00ebrdorur nj\u00eb sistem q\u00eb krijon hyrje bllokimi p\u00ebr \u00e7do rresht t\u00eb ve\u00e7ant\u00eb, e shihni se \u00ebsht\u00eb e komplikuar. Sepse duhet t\u00eb parashikoni tabel\u00ebn e bllokimit e cila mund t\u00eb mbushet, por Postgres nuk e b\u00ebn k\u00ebt\u00eb. <\/p>\n
<\/p>\n
Dhe n\u00eb k\u00ebt\u00eb slajd \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme q\u00eb k\u00ebtu demonstron qart\u00eb se ka nj\u00eb sistem tjet\u00ebr q\u00eb punon brenda MVCC, i cili bllokon rreshta t\u00eb ve\u00e7ant\u00eb. Pra, kur bllokoni miliarda rreshta, Postgres nuk krijon miliard komanda t\u00eb ve\u00e7anta p\u00ebr bllokim. Dhe kjo ka nj\u00eb ndikim shum\u00eb pozitiv n\u00eb performanc\u00ebn. <\/p>\n
<\/p>\n
<\/p>\n
\u00c7far\u00eb ndodh me p\u00ebrdit\u00ebsimin? Po p\u00ebrdit\u00ebsoj disa rreshta tani, dhe mund ta vini re se ai menaxhoi t\u00eb dy operacionet n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb. Ai bllokoi tabel\u00ebn, por gjithashtu bllokoi dhe indeksin. Duhej t\u00eb bllokonte indeksin sepse ka kufizime unike n\u00eb k\u00ebt\u00eb tabel\u00eb. Dhe ne duam t\u00eb sigurohemi q\u00eb askush t\u00eb mos e ndryshoj\u00eb, prandaj e bllokojm\u00eb at\u00eb.<\/p>\n
<\/p>\n
<\/p>\n
\u00c7far\u00eb ndodh n\u00ebse dua t\u00eb p\u00ebrdit\u00ebsoj dy rreshta? Dhe ne shohim se ai sillet po ashtu. Ne b\u00ebjm\u00eb dy her\u00eb m\u00eb shum\u00eb p\u00ebrdit\u00ebsime, por sakt\u00ebsisht t\u00eb nj\u00ebjtin num\u00ebr rreshtash t\u00eb bllokuar. <\/p>\n
<\/p>\n
N\u00ebse jeni t\u00eb interesuar si Postgres e b\u00ebn k\u00ebt\u00eb, duhet t\u00eb d\u00ebgjoni fjalimet e mia p\u00ebr MVCC, p\u00ebr t\u00eb kuptuar se si Postgres brenda tij i etiketon k\u00ebta rreshta q\u00eb ndryshon. Dhe Postgres ka nj\u00eb m\u00ebnyr\u00eb se si e b\u00ebn k\u00ebt\u00eb, por nuk e b\u00ebn n\u00eb nivelin e bllokimit t\u00eb tabelave, e b\u00ebn n\u00eb nj\u00eb nivel m\u00eb t\u00eb ul\u00ebt dhe m\u00eb efikas.<\/p>\n
<\/p>\n
<\/p>\n
Por \u00e7far\u00eb ndodh n\u00ebse dua t\u00eb fshi nj\u00eb gj\u00eb? N\u00ebse fshij, p\u00ebr shembull, nj\u00eb rresht dhe ende kam t\u00eb dyja bllokimet e mia, dhe edhe n\u00ebse d\u00ebshiroj t'i fshij t\u00eb gjitha, ato ende jan\u00eb aty.<\/p>\n
<\/p>\n
<\/p>\n
Dhe, p\u00ebr shembull, n\u00ebse dua t\u00eb fus 1,000 rreshta, dhe pastaj t\u00eb fshij ose t\u00eb shtoj 1,000 rreshta, ato rreshta individual\u00eb q\u00eb shtoj ose ndryshoj, nuk regjistrohen k\u00ebtu. Ato regjistrohen n\u00eb nj\u00eb nivel m\u00eb t\u00eb ul\u00ebt brenda vet\u00eb rreshtit. Dhe gjat\u00eb fjalimit p\u00ebr MVCC, kam folur rreth k\u00ebsaj n\u00eb detaje. Por \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme q\u00eb kur analizoni bllokimet, t\u00eb siguroheni q\u00eb keni nj\u00eb bllokim n\u00eb nivelin e tabel\u00ebs dhe q\u00eb k\u00ebtu nuk e shihni se si po regjistrohen rreshtat individual\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
\u00c7far\u00eb ndodh me bllokimin eksplicit?<\/p>\n
<\/p>\n
<\/p>\n
N\u00ebse klikoj \"p\u00ebrdit\u00ebso\", at\u00ebher\u00eb kam dy rreshta t\u00eb bllokuar. Dhe n\u00ebse i zgjedh t\u00eb gjith\u00eb ata dhe klikoj \"p\u00ebrdit\u00ebso n\u00eb t\u00eb gjith\u00eb\", prap\u00eb kam dy regjistra bllokimi. <\/p>\n
<\/p>\n
<\/p>\n
Ne nuk krijojm\u00eb regjistra t\u00eb ve\u00e7ant\u00eb p\u00ebr \u00e7do rresht individual. Sepse at\u00ebher\u00eb performanca bie, mund t\u00eb ket\u00eb shum\u00eb prej tyre. Dhe mund t\u00eb gjendemi n\u00eb nj\u00eb situat\u00eb t\u00eb pak\u00ebndshme. <\/p>\n
<\/p>\n
<\/p>\n
Po ashtu ndodh n\u00ebse b\u00ebjm\u00eb shared, mund t\u00eb b\u00ebjm\u00eb p\u00ebr t\u00eb gjith\u00eb 30 her\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Ne rikonstruktojm\u00eb tabel\u00ebn ton\u00eb, fshijm\u00eb gjith\u00e7ka, pastaj s\u00ebrish fusim nj\u00eb rresht.<\/p>\n
<\/p>\n
<\/p>\n
Nj\u00eb tjet\u00ebr lloj sjelljeje q\u00eb shihni n\u00eb Postgres \u00ebsht\u00eb nj\u00eb sjellje shum\u00eb e njohur dhe e d\u00ebshiruar \u2013 \u00ebsht\u00eb se mund t\u00eb kryeni update ose select. Dhe mund ta b\u00ebni k\u00ebt\u00eb nj\u00ebkoh\u00ebsisht. Dhe select nuk bllokon update dhe e nj\u00ebjta gj\u00eb ndodh n\u00eb an\u00ebn tjet\u00ebr. Ne po themi q\u00eb ai q\u00eb lexon nuk bllokon at\u00eb q\u00eb shkruan, dhe ai q\u00eb shkruan, nuk bllokon lexuesin. <\/p>\n
<\/p>\n
Do t'ju tregoj nj\u00eb shembull t\u00eb k\u00ebsaj. Po b\u00ebj tani nj\u00eb zgjedhje. M\u00eb pas do t\u00eb b\u00ebjm\u00eb INSERT. Dhe do t\u00eb mund t\u00eb shihni \u2013 694. Do t\u00eb mund t\u00eb shihni ID-n\u00eb e transaksionit q\u00eb b\u00ebri k\u00ebt\u00eb insertim. Dhe kjo \u00ebsht\u00eb se si funksionon. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse tani shikoj ID-n\u00eb time n\u00eb backend, ajo \u00ebsht\u00eb b\u00ebr\u00eb \u2013 695. <\/p>\n
<\/p>\n
<\/p>\n
Dhe mund t\u00eb shoh se 695 shfaqet n\u00eb tabel\u00ebn time. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse b\u00ebj nj\u00eb p\u00ebrdit\u00ebsim k\u00ebtu k\u00ebshtu, at\u00ebher\u00eb marr nj\u00eb rast tjet\u00ebr. N\u00eb k\u00ebt\u00eb rast 695 \u00ebsht\u00eb bllokim ekskluziv, dhe p\u00ebrdit\u00ebsimi ka nj\u00eb sjellje t\u00eb ngjashme, por midis tyre nuk ka konflikte, q\u00eb \u00ebsht\u00eb mjaft e \u00e7uditshme. <\/p>\n
<\/p>\n
Dhe mund t\u00eb vini re se n\u00eb krye \u00ebsht\u00eb ShareLock, nd\u00ebrsa posht\u00eb \u00ebsht\u00eb ExclusiveLock. Dhe t\u00eb dy transaksionet u realizuan.<\/p>\n
<\/p>\n
Dhe duhet t\u00eb d\u00ebgjoni fjalimin tim mbi MVCC, p\u00ebr t\u00eb kuptuar se si ndodh kjo. Por kjo \u00ebsht\u00eb nj\u00eb ilustrim se mund ta b\u00ebni k\u00ebt\u00eb nj\u00ebkoh\u00ebsisht, pra t\u00eb b\u00ebni SELECT dhe UPDATE nj\u00ebkoh\u00ebsisht.<\/p>\n
<\/p>\n
<\/p>\n
Le t\u00eb heqim dhe t\u00eb b\u00ebjm\u00eb s\u00ebrish nj\u00eb operacion. <\/p>\n
<\/p>\n
<\/p>\n
N\u00ebse provoni t\u00eb ekzekutoni dy update nj\u00ebkoh\u00ebsisht n\u00eb t\u00eb nj\u00ebjtin rresht, at\u00ebher\u00eb do t\u00eb bllokohet. Dhe kujtoni, thash\u00eb q\u00eb lexuesi nuk bllokon at\u00eb q\u00eb shkruan, dhe ai q\u00eb shkruan bllokon lexuesin, por nj\u00eb shkruese bllokon nj\u00eb tjet\u00ebr shkruese. Pra, nuk mund t\u00eb b\u00ebjm\u00eb q\u00eb dy persona t\u00eb p\u00ebrdit\u00ebsojn\u00eb nj\u00eb rresht t\u00eb nj\u00ebjt\u00eb nj\u00ebkoh\u00ebsisht. Duhet t\u00eb presim derisa nj\u00ebri prej tyre t\u00eb p\u00ebrfundoj\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Dhe p\u00ebr ta ilustruar k\u00ebt\u00eb, do t\u00eb shikoj tabel\u00ebn Lockdemo. Dhe do t\u00eb shohim nj\u00eb rresht. N\u00eb transaksionin 698.<\/p>\n
<\/p>\n
E kemi p\u00ebrdit\u00ebsuar deri n\u00eb 2. 699 \u00ebsht\u00eb p\u00ebrdit\u00ebsimi i par\u00eb. Dhe ka kaluar me sukses ose \u00ebsht\u00eb n\u00eb nj\u00eb transaksion n\u00eb pritje dhe pret q\u00eb t\u00eb konfirmojm\u00eb ose t\u00eb anulojm\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Por shikoni di\u00e7ka tjet\u00ebr - 2\/51 - kjo \u00ebsht\u00eb transaksioni yn\u00eb i par\u00eb, sesioni yn\u00eb i par\u00eb. 3\/112 - ky \u00ebsht\u00eb k\u00ebrkesa e dyt\u00eb q\u00eb doli nga lart dhe q\u00eb e ndryshoi k\u00ebt\u00eb vler\u00eb n\u00eb 3. Dhe n\u00ebse e vini re, e sip\u00ebrmja e bllokoi veten, e cila \u00ebsht\u00eb 699. Por 3\/112 nuk ofroi bllokim. N\u00eb kolon\u00ebn Lock_mode shkruhet se po pret. Ai po pret 699. Dhe n\u00ebse shikoni se ku \u00ebsht\u00eb 699, \u00ebsht\u00eb mbi. Dhe \u00e7far\u00eb b\u00ebri sesioni i par\u00eb? Ai krijoi nj\u00eb bllokim ekskluziv mbi ID-n\u00eb e tij t\u00eb transaksionit. K\u00ebshtu e b\u00ebn Postgres. Ai bllokon ID-n\u00eb e tij t\u00eb transaksionit. Dhe n\u00ebse doni t\u00eb prisni derisa dikush t\u00eb konfirmoj\u00eb ose t\u00eb anuloj\u00eb, duhet t\u00eb prisni derisa t\u00eb ket\u00eb nj\u00eb transaksion q\u00eb pret. Dhe p\u00ebr k\u00ebt\u00eb mund t\u00eb shohim nj\u00eb rresht t\u00eb \u00e7uditsh\u00ebm. <\/p>\n
<\/p>\n
Le t\u00eb shohim p\u00ebrs\u00ebri. Nga e majta shohim ID-n\u00eb ton\u00eb t\u00eb procesimit. N\u00eb kolon\u00ebn e dyt\u00eb shohim ID-n\u00eb ton\u00eb virtuale t\u00eb transaksionit, dhe n\u00eb t\u00eb tret\u00ebn shohim lock_type. \u00c7far\u00eb do t\u00eb thot\u00eb kjo? N\u00eb thelb, ajo thot\u00eb se bllokon ID-n\u00eb e transaksionit. Por vini re se n\u00eb t\u00eb gjitha rreshtat posht\u00eb shkruhet relacion. Dhe p\u00ebr k\u00ebt\u00eb keni dy lloje bllokimi n\u00eb tabel\u00eb. Ka bllokim relacioni. Por gjithashtu ka bllokim transactionid, ku ne bllokojm\u00eb veten, kjo \u00ebsht\u00eb ajo q\u00eb ndodh n\u00eb rreshtin e par\u00eb ose n\u00eb fundin m\u00eb t\u00eb ul\u00ebt, ku transactionid, ku presim q\u00eb 699 t\u00eb p\u00ebrfundoj\u00eb operacionin e vet. <\/p>\n
<\/p>\n
Un\u00eb shikoj se \u00e7far\u00eb po ndodh k\u00ebtu. Dhe k\u00ebtu ndodhin n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb dy gj\u00ebra. Ju shikoni bllokimin sipas ID-s\u00eb s\u00eb transaksionit n\u00eb rreshtin e par\u00eb, i cili bllokon veten. Dhe ajo bllokon veten p\u00ebr t\u00eb detyruar njer\u00ebzit t\u00eb presin. <\/p>\n
<\/p>\n
N\u00ebse shikoni rreshtin e 6-t\u00eb, \u00ebsht\u00eb e nj\u00ebjt\u00eb me t\u00eb par\u00ebn. Dhe p\u00ebr k\u00ebt\u00eb arsye transaksioni 699 bllokohet. 700 gjithashtu vet\u00ebbllokohet. Dhe pastaj n\u00eb rreshtin e posht\u00ebm do t\u00eb shihni se po presim kur 699 t\u00eb p\u00ebrfundoj\u00eb operacionin e tij.<\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00eb lock_type, tuple shihni numrat. <\/p>\n
<\/p>\n
<\/p>\n
Ju mund t\u00eb shikoni se \u00ebsht\u00eb 0\/10. Dhe kjo \u00ebsht\u00eb numri i faqes, si dhe offset-i i k\u00ebtij rreshti t\u00eb ve\u00e7ant\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Dhe ju shihni se b\u00ebhet 0\/11, kur e p\u00ebrdit\u00ebsojm\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Por n\u00eb t\u00eb v\u00ebrtet\u00eb - \u00ebsht\u00eb 0\/10, sepse ndodh pritja e k\u00ebtij operacioni. Ne kemi mund\u00ebsin\u00eb t\u00eb shohim se ky \u00ebsht\u00eb rreshti q\u00eb po pres p\u00ebr ta konfirmuar. <\/p>\n
<\/p>\n
<\/p>\n
Sapo e konfirmuam dhe e shtyp\u00ebm commit, dhe kur p\u00ebrfundoi p\u00ebrdit\u00ebsimi, ky \u00ebsht\u00eb rezultati q\u00eb marrim p\u00ebrs\u00ebri. Transaksioni 700 \u00ebsht\u00eb bllokimi i vet\u00ebm, ai nuk pret m\u00eb ask\u00ebnd, sepse \u00ebsht\u00eb angazhuar. Ai thjesht pret q\u00eb transaksioni t\u00eb p\u00ebrfundoj\u00eb. Sapo t\u00eb p\u00ebrfundoj\u00eb 699, nuk presim m\u00eb asgj\u00eb. Dhe tani transaksioni 700 thot\u00eb se gjith\u00e7ka \u00ebsht\u00eb mir\u00eb, se t\u00eb gjitha bllokimet q\u00eb i nevojiten, i ka n\u00eb t\u00eb gjitha tabelat e lejuara. <\/p>\n
<\/p>\n
<\/p>\n
Dhe p\u00ebr ta b\u00ebr\u00eb akoma m\u00eb t\u00eb komplikuar, ne krijojm\u00eb nj\u00eb shikim tjet\u00ebr, i cili k\u00ebt\u00eb her\u00eb do t'i jap\u00eb nj\u00eb hierarki. Nuk pres q\u00eb ju ta kuptoni k\u00ebt\u00eb k\u00ebrkes\u00eb. Por kjo do t'i ofroj\u00eb nj\u00eb pamje m\u00eb t\u00eb qart\u00eb mbi at\u00eb q\u00eb po ndodh. <\/p>\n
<\/p>\n
<\/p>\n
Ky \u00ebsht\u00eb nj\u00eb shikim rekursiv, i cili gjithashtu ka nj\u00eb seksion tjet\u00ebr. Dhe pastaj kthen gjith\u00e7ka p\u00ebrs\u00ebri s\u00eb bashku. Le ta p\u00ebrdorim k\u00ebt\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
\u00c7far\u00eb ndodh n\u00ebse b\u00ebjm\u00eb tre p\u00ebrdit\u00ebsime t\u00eb nj\u00ebkohshme dhe themi se rreshti tani \u00ebsht\u00eb i barabart\u00eb me tre. Dhe ne e ndryshojm\u00eb 3 n\u00eb 4. <\/p>\n
<\/p>\n
<\/p>\n
Dhe ja ku shohim 4. Dhe ID e transaksionit 702. <\/p>\n
<\/p>\n
<\/p>\n
Dhe pastaj e ndryshoj 4 n\u00eb 5. E 5 n\u00eb 6, e 6 n\u00eb 7. Dhe un\u00eb radhitem njer\u00ebzit q\u00eb do t\u00eb presin q\u00eb kjo nj\u00eb transaksion t\u00eb p\u00ebrfundoj\u00eb.<\/p>\n
<\/p>\n
<\/p>\n
Dhe gjith\u00e7ka b\u00ebhet e qart\u00eb. Cili \u00ebsht\u00eb rreshti i par\u00eb? Ky \u00ebsht\u00eb 702. Ky \u00ebsht\u00eb ID e transaksionit q\u00eb fillimisht vendosi k\u00ebt\u00eb vler\u00eb. Dhe \u00e7far\u00eb kam shkruar n\u00eb kolon\u00ebn e Miratimeve? Kam sh\u00ebnimet f<\/code>. K\u00ebto jan\u00eb p\u00ebrdit\u00ebsimet e mia, q\u00eb (5, 6, 7) nuk mund t\u00eb miratohen, sepse po presim q\u00eb ID e transaksionit 702 t\u00eb p\u00ebrfundoj\u00eb. Aty kemi nj\u00eb bllokim t\u00eb ID e transaksionit. Dhe del 5 bllokime t\u00eb ID e transaksionit.<\/p>\n<\/p>\n
Dhe n\u00ebse e shikoni 704, 705, atje nuk ka ende asgj\u00eb, sepse ata ende nuk e din\u00eb se \u00e7far\u00eb po ndodh. Thjesht shkruajn\u00eb se nuk kan\u00eb asnj\u00eb ide se \u00e7far\u00eb po ndodh. Dhe ata gjithashtu do t\u00eb bien n\u00eb gjum\u00eb, sepse presin q\u00eb dikush t\u00eb p\u00ebrfundoj\u00eb dhe t'i zgjoj\u00eb kur t\u00eb vij\u00eb mund\u00ebsia p\u00ebr t\u00eb ndryshuar rreshtin. <\/p>\n
<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb si duket. E qart\u00eb \u00ebsht\u00eb se t\u00eb gjith\u00eb presin rreshtin 12. <\/p>\n
<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb ajo q\u00eb pam\u00eb k\u00ebtu. Ja 0\/12. <\/p>\n
<\/p>\n
<\/p>\n
Pra, sapo transaksioni i par\u00eb miratohet, mund ta shihni k\u00ebtu se si funksionon hierakia. Dhe tani gjith\u00e7ka b\u00ebhet e qart\u00eb. T\u00eb gjitha b\u00ebhen t\u00eb pastra. Dhe ata n\u00eb t\u00eb v\u00ebrtet\u00eb jan\u00eb ende n\u00eb pritje. <\/p>\n
<\/p>\n
<\/p>\n
K\u00ebtu \u00ebsht\u00eb se \u00e7far\u00eb po ndodh. 702 po angazhohet. Tani 703 merr k\u00ebt\u00eb bllokim radh\u00eb, dhe pastaj 704 fillon t\u00eb pres\u00eb deri sa 703 t\u00eb angazhohet. Dhe 705 gjithashtu po e pret k\u00ebt\u00eb. Kur t\u00eb gjitha k\u00ebto p\u00ebrfundojn\u00eb, ato vet\u00eb pastruan. D\u00ebshiroj t\u00eb theksoj se t\u00eb gjith\u00eb rreshtohen. Kjo \u00ebsht\u00eb shum\u00eb e ngjashme me situat\u00ebn me trafikun, kur t\u00eb gjith\u00eb presin makin\u00ebn e par\u00eb. Makin\u00ebs e par\u00eb ndalon, dhe t\u00eb gjith\u00eb rreshtohen n\u00eb nj\u00eb linj\u00eb t\u00eb gjat\u00eb. Pastaj ajo l\u00ebviz, dhe pastaj makina tjet\u00ebr mund t\u00eb kaloj\u00eb p\u00ebrpara dhe t\u00eb marr\u00eb bllokimin e saj, etj. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse ju duket se kjo nuk \u00ebsht\u00eb mjaft e komplikuar, tani do t\u00eb flasim p\u00ebr deadlocks. Nuk e di kush nga ju ka hasur n\u00eb to. Kjo \u00ebsht\u00eb nj\u00eb problem relativisht i zakonsh\u00ebm n\u00eb sistemet e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Por deadlocks jan\u00eb ai rast, kur nj\u00eb seanc\u00eb po pret q\u00eb di\u00e7ka t\u00eb p\u00ebrfundoj\u00eb nga nj\u00eb seanc\u00eb tjet\u00ebr. Nd\u00ebrkoh\u00eb, seanca tjet\u00ebr po pret p\u00ebr di\u00e7ka nga seanca e par\u00eb. <\/p>\n
<\/p>\n
Dhe, p\u00ebr shembull, n\u00ebse Ivan thot\u00eb: \"M\u00eb jep di\u00e7ka\", dhe un\u00eb i them: \"Jo, un\u00eb do t'ja jap vet\u00ebm n\u00ebse ti m\u00eb jep di\u00e7ka tjet\u00ebr\". Dhe ai thot\u00eb: \"Jo, nuk do t'ja jap n\u00ebse ti nuk m\u00eb jep\". Dhe ne kemi nj\u00eb situat\u00eb deadlock. Jam i sigurt q\u00eb Ivan nuk do ta b\u00ebnte k\u00ebt\u00eb, por ju kuptoni kuptimin, q\u00eb kemi dy persona q\u00eb duan t\u00eb marrin di\u00e7ka dhe nuk jan\u00eb t\u00eb gatsh\u00ebm ta japin derisa personi tjet\u00ebr t'ua jap\u00eb at\u00eb q\u00eb ata duan. Dhe k\u00ebtu nuk ka zgjidhje. <\/p>\n
<\/p>\n
Dhe, n\u00eb esenc\u00eb, baza juaj e t\u00eb dh\u00ebnave duhet ta identifikoj\u00eb k\u00ebt\u00eb. Dhe pastaj \u00ebsht\u00eb e nevojshme t\u00eb fshihet ose t\u00eb mbyllet nj\u00eb nga seancat, sepse n\u00eb t\u00eb kund\u00ebrt ato do t\u00eb mbeten aty p\u00ebrgjithmon\u00eb. Dhe ne e shohim k\u00ebt\u00eb n\u00eb bazat e t\u00eb dh\u00ebnave, e shohim n\u00eb sistemet operative. Dhe n\u00eb t\u00eb gjitha vendet ku kemi procese paralele, kjo mund t\u00eb ndodh\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Dhe tani do t\u00eb vendosim dy deadlocks. Do t\u00eb vendosim 50 dhe 80. N\u00eb rreshtin e par\u00eb do t\u00eb kryej nj\u00eb p\u00ebrdit\u00ebsim nga 50 n\u00eb 50. Do t\u00eb kem numrin e transaksionit 710. <\/p>\n
<\/p>\n
<\/p>\n
Dhe pastaj do ta nd\u00ebrronj 80 me 81, dhe 50 me 51. <\/p>\n
<\/p>\n
<\/p>\n
Dhe k\u00ebshtu do t\u00eb duket. Dhe prandaj 710 ka nj\u00eb bllokim radh\u00eb, nd\u00ebrsa 711 pret konfirmimin. E kemi par\u00eb k\u00ebt\u00eb kur p\u00ebrdit\u00ebsuam. 710 \u00ebsht\u00eb pronari i radh\u00ebs son\u00eb. Dhe 711 po pret q\u00eb 710 t\u00eb p\u00ebrfundoj\u00eb transaksionin.<\/p>\n
<\/p>\n
<\/p>\n
Dhe madje shkruhet se n\u00eb cilin radh\u00eb ndodhin deadlocks. Dhe k\u00ebtu fillon t\u00eb b\u00ebhet e \u00e7uditshme.<\/p>\n
<\/p>\n
<\/p>\n
Tani po e p\u00ebrdit\u00ebsojm\u00eb 80 n\u00eb 80. <\/p>\n
<\/p>\n
<\/p>\n
Dhe ja ku fillon deadlocks. 710 po pret p\u00ebrgjigje nga 711, nd\u00ebrsa 711 po pret 710. Dhe kjo do t\u00eb p\u00ebrfundoj\u00eb keq. Nuk ka zgjidhje p\u00ebr k\u00ebt\u00eb. Ata do t\u00eb presin p\u00ebrgjigjen nga nj\u00ebri-tjetri. <\/p>\n
<\/p>\n
<\/p>\n
Dhe kjo do filloj\u00eb t\u00eb ndaloj\u00eb gjith\u00e7ka. Dhe ne nuk e duam k\u00ebt\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
N\u00eb Postgres, ka m\u00ebnyra p\u00ebr t\u00eb v\u00ebrejtur kur ndodhin k\u00ebto. Kur ndodhin, merrni k\u00ebt\u00eb l\u00ebshim. Kjo tregon se nj\u00eb proces po pret SHARE LOCK nga nj\u00eb proces tjet\u00ebr, dmth. q\u00eb bllokohet nga procesi 711. Procesi tjet\u00ebr priste q\u00eb t\u00eb jepet SHARE LOCK p\u00ebr nj\u00eb ID transaksioni t\u00eb caktuar dhe ishte bllokuar nga nj\u00eb tjet\u00ebr proces. Prandaj, kjo \u00ebsht\u00eb nj\u00eb situat\u00eb deadlock.<\/p>\n
<\/p>\n
<\/p>\n
A ndodhin deadlocks me tre pal\u00eb? A \u00ebsht\u00eb e mundur? Po. <\/p>\n
<\/p>\n
<\/p>\n
Ne shkruajm\u00eb k\u00ebto numra n\u00eb tabel\u00eb. Ne e ndryshojm\u00eb 40 n\u00eb 40, ne b\u00ebjm\u00eb bllokimin. <\/p>\n
<\/p>\n
<\/p>\n
E ndryshojm\u00eb 60 n\u00eb 61, 80 n\u00eb 81. <\/p>\n
<\/p>\n
<\/p>\n
Pastaj e ndryshojm\u00eb 80 dhe \u2013 bum! <\/p>\n
<\/p>\n
<\/p>\n
Dhe 714 tani pret 715. 716-a pret 715-\u00ebn. Dhe k\u00ebtu nuk ka asgj\u00eb p\u00ebr t\u00eb b\u00ebr\u00eb.<\/p>\n
<\/p>\n
<\/p>\n
K\u00ebtu nuk jan\u00eb dy persona, por tre persona. Un\u00eb dua di\u00e7ka prej teje, ai d\u00ebshiron di\u00e7ka nga personi e tret\u00eb, dhe personi i tret\u00eb d\u00ebshiron di\u00e7ka nga un\u00eb. Dhe ne jemi n\u00eb nj\u00eb pritje me tre pal\u00eb, sepse t\u00eb gjith\u00eb presim q\u00eb tjetri t\u00eb p\u00ebrfundoj\u00eb at\u00eb q\u00eb duhet t\u00eb b\u00ebj\u00eb. <\/p>\n
<\/p>\n
<\/p>\n
Dhe Postgres e di n\u00eb cilin rresht ndodh kjo. Prandaj, ai do t'ju jap\u00eb k\u00ebt\u00eb mesazh, i cili tregon se keni nj\u00eb problem, ku tre hyrje bllokojn\u00eb nj\u00ebra-tjetr\u00ebn. K\u00ebtu nuk ka kufij. Kjo mund t\u00eb ndodh\u00eb n\u00eb nj\u00eb rast ku 20 regjistrime bllokojn\u00eb nj\u00ebra-tjetr\u00ebn. <\/p>\n
<\/p>\n
<\/p>\n
Problemi tjet\u00ebr \u00ebsht\u00eb se \u00ebsht\u00eb serializable. <\/p>\n
<\/p>\n
<\/p>\n
N\u00ebse ndodhi nj\u00eb bllokim special serializable.<\/p>\n
<\/p>\n
<\/p>\n
Dhe kthehemi te 719. Ai ka nj\u00eb rezultat krejt normal. <\/p>\n
<\/p>\n
<\/p>\n
Dhe ju mund t\u00eb klikoni p\u00ebr t\u00eb b\u00ebr\u00eb nj\u00eb transaksion nga serializable.<\/p>\n
<\/p>\n
<\/p>\n
Dhe ju kuptoni q\u00eb tani keni nj\u00eb tjet\u00ebr lloj bllokimi SA \u2013 kjo do t\u00eb thot\u00eb serializable. <\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n
Prandaj, kemi nj\u00eb lloj t\u00eb ri t\u00eb bllokimit, i quajtur SARieadLock, i cili \u00ebsht\u00eb nj\u00eb bllokim seri dhe lejon t\u00eb hyjn\u00eb serit\u00eb.<\/p>\n
<\/p>\n
<\/p>\n
Gjithashtu, mund t\u00eb vendosni indekse unike. <\/p>\n
<\/p>\n
<\/p>\n
N\u00eb k\u00ebt\u00eb tabel\u00eb kemi indekse unike.<\/p>\n
<\/p>\n
<\/p>\n
Prandaj, n\u00ebse un\u00eb fut numrin 2 k\u00ebtu, kam 2. Por n\u00eb krye fut nj\u00eb 2 tjet\u00ebr. Dhe ju mund t\u00eb shihni se 721 ka nj\u00eb bllokim ekskluziv. Por tani 722 pret q\u00eb 721 t\u00eb p\u00ebrfundoj\u00eb operacionin e tij, sepse nuk mund t\u00eb fut\u00eb 2 p\u00ebr sa koh\u00eb nuk di se \u00e7far\u00eb do t\u00eb ndodh\u00eb me 721. <\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse b\u00ebjm\u00eb subtransaction.<\/p>\n
<\/p>\n
<\/p>\n
K\u00ebtu kemi 723.<\/p>\n
<\/p>\n
<\/p>\n
Dhe n\u00ebse ruajm\u00eb nj\u00eb pik\u00eb dhe pastaj e azhurnojm\u00eb, ne marrim nj\u00eb ID t\u00eb re transaksioni. Kjo \u00ebsht\u00eb nj\u00eb tjet\u00ebr karakteristik\u00eb e sjelljes q\u00eb duhet t\u00eb dini. N\u00ebse e kthejm\u00eb k\u00ebt\u00eb, ID e transaksionit shkon. 724 shkon. Por tani ne kemi 725. <\/p>\n
<\/p>\n
Dhe \u00e7far\u00eb p\u00ebrpiqem t\u00eb b\u00ebj k\u00ebtu? Po p\u00ebrpiqem t'ju tregoj shembuj t\u00eb bllokimeve t\u00eb pazakonta q\u00eb mund t\u00eb gjeni: qoft\u00eb bllokime serializable ose SAVEPOINT - k\u00ebto jan\u00eb lloje t\u00eb ndryshme bllokimesh q\u00eb do t\u00eb shfaqen n\u00eb tabel\u00ebn e bllokimeve. <\/p>\n
<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb krijimi i bllokimeve eksplicite, t\u00eb cilat kan\u00eb pg_advisory_lock. <\/p>\n
<\/p>\n
<\/p>\n
Dhe shihni se lloji i bllokimit k\u00ebtu liston si advisory. Dhe k\u00ebtu \u00ebsht\u00eb shkruar me t\u00eb kuqe \"advisory\". Dhe mund ta bllokoni k\u00ebshtu n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb me pg_advisory_unlock.<\/p>\n
<\/p>\n
<\/p>\n
Dhe duke p\u00ebrfunduar, do doja t'ju tregoj di\u00e7ka tjet\u00ebr befasuese. Do t\u00eb krijoj nj\u00eb tjet\u00ebr lloj. Por do ta lidh tabel\u00ebn pg_locks me tabel\u00ebn pg_stat_activity. Dhe pse d\u00ebshiroj ta b\u00ebj k\u00ebt\u00eb? Sepse kjo do t\u00eb m\u00eb lejoj\u00eb t\u00eb shoh dhe t\u00eb kuptoj t\u00eb gjitha sesionet aktuale dhe t\u00eb shoh se cilat bllokime po presin. Dhe kjo \u00ebsht\u00eb mjaft interesante kur ne kombinojm\u00eb tabel\u00ebn e bllokimeve dhe tabel\u00ebn e k\u00ebrkesave. <\/p>\n
<\/p>\n
<\/p>\n
Dhe k\u00ebtu ne krijojm\u00eb pg_stat_view. <\/p>\n
<\/p>\n
<\/p>\n
Dhe ne azhurnojm\u00eb nj\u00eb rresht p\u00ebr nj\u00eb. Dhe k\u00ebtu shohim 724. Pastaj e azhurnojm\u00eb rreshtin ton\u00eb deri n\u00eb tre. Dhe \u00e7far\u00eb shihni k\u00ebtu tani? K\u00ebto jan\u00eb k\u00ebrkesat, dmth. ju shihni t\u00ebr\u00eb list\u00ebn e k\u00ebrkesave q\u00eb jan\u00eb t\u00eb listuara n\u00eb kolon\u00ebn e majt\u00eb. Dhe pastaj n\u00eb an\u00ebn e djatht\u00eb mund t\u00eb shihni bllokimet dhe at\u00eb q\u00eb ato krijojn\u00eb. Dhe kjo mund t\u00eb jet\u00eb m\u00eb e qart\u00eb p\u00ebr ju, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mos keni nevoj\u00eb t\u00eb ktheheni \u00e7do her\u00eb te \u00e7do sesion dhe t\u00eb shihni - n\u00ebse duhet t\u00eb bashkoni apo jo. Kjo e b\u00ebjn\u00eb p\u00ebr ne. <\/p>\n
<\/p>\n
Nj\u00eb tjet\u00ebr funksion q\u00eb \u00ebsht\u00eb shum\u00eb i dobish\u00ebm \u00ebsht\u00eb pg_blocking_pids<\/code>Ndoshta nuk keni d\u00ebgjuar ndonj\u00ebher\u00eb p\u00ebr t\u00eb. \u00c7far\u00eb b\u00ebn ajo? Ajo na lejon t\u00eb themi se p\u00ebr k\u00ebt\u00eb seanc\u00eb 11740, cilat jan\u00eb ID-t\u00eb e proceseve q\u00eb ajo pret. Dhe mund t\u00eb shihni se 11740 pret 724. Dhe 724 \u00ebsht\u00eb n\u00eb krye. Nd\u00ebrsa 11306 \u00ebsht\u00eb ID e procesit tuaj. N\u00eb thelb, kjo funksion kalon p\u00ebrmes tabel\u00ebs suaj t\u00eb bllokimeve. E di q\u00eb \u00ebsht\u00eb pak e komplikuar, por ju po arrini ta kuptoni. N\u00eb thelb, kjo funksion kalon p\u00ebrmes k\u00ebsaj tabele bllokimesh dhe p\u00ebrpiqet t\u00eb gjej\u00eb ku \u00ebsht\u00eb ky ID procesi, duke pasur parasysh ato bllokime q\u00eb ajo pret. Dhe gjithashtu p\u00ebrpiqet t\u00eb llogaris\u00eb se cili \u00ebsht\u00eb sakt\u00ebsisht ID i procesit t\u00eb atij procesi q\u00eb pret bllokimat. Prandaj mund t\u00eb filloni k\u00ebt\u00eb funksion. pg_blocking_pids<\/code>.<\/p>\n<\/p>\n
Dhe kjo ndodh t\u00eb jet\u00eb shum\u00eb e dobishme. E kemi shtuar vet\u00ebm me versionin 9.6, prandaj kjo funksion ka vet\u00ebm 5 vjet, por \u00ebsht\u00eb shum\u00eb dhe shum\u00eb e dobishme. Po ashtu p\u00ebr k\u00ebrkes\u00ebn e dyt\u00eb. Ajo tregon sakt\u00ebsisht at\u00eb q\u00eb na nevojitet t\u00eb shohim. <\/p>\n
<\/p>\n
<\/p>\n
Kjo \u00ebsht\u00eb ajo p\u00ebr t\u00eb cil\u00ebn doja t\u00eb flisja me ju. Dhe si\u00e7 e prisja, ne p\u00ebrdor\u00ebm gjith\u00eb koh\u00ebn ton\u00eb, sepse kishte nj\u00eb num\u00ebr t\u00eb madh slajdesh. Dhe slajdet jan\u00eb t\u00eb disponueshme p\u00ebr shkarkim. Doja t'ju falenderoja q\u00eb ishit k\u00ebtu. Jam i sigurt se do t'ju p\u00eblqej\u00eb pjesa tjet\u00ebr e konferenc\u00ebs, shum\u00eb faleminderit!<\/p>\n
<\/p>\n
Pyetje:<\/p>\n
<\/p>\n
P\u00ebr shembull, n\u00ebse p\u00ebrpiqem t\u00eb azhurnoj rreshtat, nd\u00ebrsa nj\u00eb sesion tjet\u00ebr p\u00ebrpiqet t\u00eb fshij\u00eb t\u00ebr\u00eb tabel\u00ebn. Sa e kuptoj, duhet t\u00eb ket\u00eb di\u00e7ka si intent lock. A ekziston kjo n\u00eb Postgres?<\/em><\/p>\n<\/p>\n
<\/p>\n
Kthehemi n\u00eb fillim. Ndoshta e mbani mend q\u00eb kur b\u00ebni nj\u00eb veprim, p\u00ebr shembull, kur b\u00ebni SELECT, ne l\u00ebshojm\u00eb AccessShareLock. Dhe kjo parandalon fshirjen e tabel\u00ebs. Prandaj n\u00ebse, p\u00ebr shembull, d\u00ebshironi t\u00eb azhurnoni nj\u00eb rresht n\u00eb tabel\u00eb ose t\u00eb fshini nj\u00eb rresht, at\u00ebher\u00eb askush nuk mund t\u00eb fshij\u00eb t\u00ebr\u00eb tabel\u00ebn n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, sepse mbani k\u00ebt\u00eb AccessShareLock mbi t\u00ebr\u00eb tabel\u00ebn dhe mbi rreshtin. Dhe sapo t\u00eb keni p\u00ebrfunduar, ata mund ta fshijn\u00eb at\u00eb. Por derisa t\u00eb jeni duke ndryshuar di\u00e7ka atje, ata nuk do t\u00eb ken\u00eb mund\u00ebsi ta b\u00ebjn\u00eb k\u00ebt\u00eb.<\/p>\n
<\/p>\n
Le t\u00eb provojm\u00eb p\u00ebrs\u00ebri. Le t\u00eb kalojm\u00eb te shembulli i fshirjes. Dhe ju shihni se si mbi rresht \u00ebsht\u00eb nj\u00eb lock ekskluziv mbi t\u00ebr\u00eb tabel\u00ebn. <\/p>\n
<\/p>\n
Kjo do t\u00eb duket si lock ekskluziv, e v\u00ebrtet\u00eb?<\/em><\/p>\n<\/p>\n
Po, kjo duket si kjo. E kuptoj se p\u00ebr \u00e7far\u00eb po flisni. Po thoni se, n\u00ebse un\u00eb kryej nj\u00eb SELECT, do t\u00eb kem ShareExclusive, dhe m\u00eb pas e kaloj k\u00ebt\u00eb n\u00eb gjendjen Row Exclusive, a do t\u00eb ishte kjo nj\u00eb problem? Por p\u00ebr mrekulli, kjo nuk krijon nj\u00eb problem. Duket si nj\u00eb rritje e shkall\u00ebs s\u00eb bllokimit, por n\u00eb thelb, kam nj\u00eb bllokim q\u00eb parandalon fshirjen. Dhe tani, kur e b\u00ebj k\u00ebt\u00eb bllokim m\u00eb t\u00eb fuqish\u00ebm, ai akoma parandalon fshirjen. Prandaj, nuk \u00ebsht\u00eb se po rritem lart. P.sh., ai e parandalonte k\u00ebt\u00eb edhe kur ishte n\u00eb nj\u00eb nivel m\u00eb t\u00eb ul\u00ebt, prandaj, kur e rris nivelin e tij, ai akoma e parandalon fshirjen e tabel\u00ebs. <\/p>\n
<\/p>\n
E kuptoj p\u00ebr \u00e7far\u00eb po flisni. K\u00ebtu nuk ka nj\u00eb rast rritjeje t\u00eb shkall\u00ebs s\u00eb bllokimit, ku po p\u00ebrpiqeni t\u00eb heqni nj\u00eb bllokim p\u00ebr t\u00eb futur nj\u00eb m\u00eb t\u00eb fuqish\u00ebm. K\u00ebtu thjesht e rrit at\u00eb parandalim n\u00eb t\u00eb gjith\u00eb vendin, prandaj nuk shkakton ndonj\u00eb konflikt. Por \u00ebsht\u00eb nj\u00eb pyetje e mir\u00eb. Faleminderit shum\u00eb q\u00eb e ngrit\u00ebt k\u00ebt\u00eb!<\/p>\n
<\/p>\n
\u00c7far\u00eb na nevojitet t\u00eb b\u00ebjm\u00eb p\u00ebr t\u00eb shmangur situat\u00ebn e deadlock\u2019it, kur kemi shum\u00eb seanca, nj\u00eb num\u00ebr t\u00eb madh p\u00ebrdoruesish?<\/em><\/p>\n<\/p>\n
Postgres e v\u00ebrejt automatikisht situat\u00ebn e deadlock\u2019it. Dhe automatikisht do t\u00eb fshij\u00eb nj\u00eb nga seancat. Nj\u00eb m\u00ebnyr\u00eb q\u00eb ndihmon p\u00ebr t\u00eb shmangur situat\u00ebn e deadlock\u2019it \u00ebsht\u00eb t\u00eb bllokoni njer\u00ebzit n\u00eb t\u00eb nj\u00ebjtin rend. Prandaj, kur ta shikoni aplikacionin tuaj, shpesh arsyeja p\u00ebr deadlocks... Le ta imagjinojm\u00eb se dua t\u00eb bllokoj dy gj\u00ebra t\u00eb ndryshme. Nj\u00eb aplikacion bllokon tabel\u00ebn 1, dhe nj\u00eb aplikacion tjet\u00ebr bllokon tabel\u00ebn 2, dhe m\u00eb pas tabel\u00ebn 1. Dhe m\u00ebnyra m\u00eb e leht\u00eb p\u00ebr t\u00eb shmangur deadlocks \u00ebsht\u00eb t\u00eb shikoni aplikacionin tuaj dhe t\u00eb siguroheni q\u00eb bllokimi ndodh n\u00eb t\u00eb nj\u00ebjtin rend n\u00eb t\u00eb gjith\u00eb aplikacionet. Dhe kjo, n\u00eb p\u00ebrgjith\u00ebsi, eleminon 80% t\u00eb problemeve, sepse shum\u00eb njer\u00ebz t\u00eb ndrysh\u00ebm shkruajn\u00eb k\u00ebto aplikacione. Dhe n\u00ebse i bllokoni ato n\u00eb t\u00eb nj\u00ebjtin rend, at\u00ebher\u00eb nuk do t\u00eb p\u00ebrballeni me situat\u00ebn e deadlock\u2019it.<\/p>\n
<\/p>\n
Faleminderit shum\u00eb p\u00ebr prezantimin tuaj! Flit\u00ebt p\u00ebr vacuum full dhe, n\u00ebse e kuptoj sakt\u00ebsisht, vacuum full deformon rendin e rekord\u00ebve n\u00eb ruajtjen p\u00ebrkat\u00ebse, prandaj mban rekordet aktuale t\u00eb pat\u00ebmet\u00eb. Por pse vacuum full merr nj\u00eb bllokim ekskluziv dhe pse ai \u00ebsht\u00eb n\u00eb konflikt me operacionet e shkruar?<\/em><\/p>\n<\/p>\n
\u00cbsht\u00eb nj\u00eb pyetje e mir\u00eb. Arsyeja \u00ebsht\u00eb se vacuum full merr tabel\u00ebn. N\u00eb thelb, ne po krijojm\u00eb nj\u00eb version t\u00eb ri t\u00eb tabel\u00ebs. Pra, tabela do t\u00eb jet\u00eb e re. Kjo do t\u00eb thot\u00eb se do t\u00eb jet\u00eb nj\u00eb version krejt\u00ebsisht i ri i tabel\u00ebs. Problemi \u00ebsht\u00eb se kur e b\u00ebjm\u00eb k\u00ebt\u00eb, nuk duam q\u00eb njer\u00ebzit ta shohin, sepse na nevojitet q\u00eb ata t\u00eb shohin tabel\u00ebn e re. Prandaj kjo lidhet me pyetjen e m\u00ebparshme. N\u00ebse do t\u00eb mund t\u00eb lexoja n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, at\u00ebher\u00eb nuk do t\u00eb mund ta l\u00ebviznim dhe t\u00eb udh\u00ebzonim njer\u00ebzit n\u00eb tabel\u00ebn e re. Do t\u00eb na duhej t\u00eb prisnim q\u00eb secili t\u00eb p\u00ebrfundonte leximin e k\u00ebsaj tabele dhe k\u00ebshtu, n\u00eb thelb, kjo \u00ebsht\u00eb nj\u00eb situat\u00eb lock exclusive.
<\/p>\n
A \u00ebsht\u00eb e mundur t\u00eb shtohet nj\u00eb locking timeout n\u00eb Postgres? N\u00eb Oracle, mund t\u00eb shkruaj 'zgjedh n\u00eb p\u00ebrdit\u00ebsim' dhe t\u00eb pres 50 sekonda deri n\u00eb p\u00ebrdit\u00ebsim. Kjo ishte e mir\u00eb p\u00ebr aplikacionin. Por n\u00eb Postgres, ose duhet ta b\u00ebj menj\u00ebher\u00eb dhe t\u00eb mos pres fare, ose t\u00eb pres deri n\u00eb nj\u00eb koh\u00eb t\u00eb caktuar.<\/em> <\/p>\n<\/p>\n
Po, mund t\u00eb zgjidhni nj\u00eb koh\u00eb krijimi p\u00ebr bllokimet tuaja. Mund t\u00eb l\u00ebsh nj\u00eb komand\u00eb no way, e cila do t\u00eb jet\u00eb..., n\u00ebse nuk mund ta merrni menj\u00ebher\u00eb bllokimin. Pra, ose bllokoni timeout, ose ndonj\u00eb tjet\u00ebr q\u00eb do t'ju lejoj\u00eb ta b\u00ebni k\u00ebt\u00eb. Kjo nuk b\u00ebhet n\u00eb nivelin sintaksor. B\u00ebhet si nj\u00eb variab\u00ebl n\u00eb server. Ndonj\u00ebher\u00eb nuk mund ta p\u00ebrdorni k\u00ebt\u00eb.<\/p>\n
<\/p>\n
Mund t\u00eb hapni sliden 75?<\/em> <\/p>\n<\/p>\n
Po.<\/p>\n
<\/p>\n
<\/p>\n
Dhe pyetja ime \u00ebsht\u00eb si vijon. Pse t\u00eb dy proceset e p\u00ebrdit\u00ebsimit po presin 703?<\/em><\/p>\n<\/p>\n
Dhe kjo \u00ebsht\u00eb nj\u00eb pyetje e shk\u00eblqyer. Nuk e kuptoj, p\u00ebr sa i p\u00ebrket, pse Postgres e b\u00ebn k\u00ebt\u00eb. Por kur u krijua 703, ai priste 702. Dhe kur 704 dhe 705 shfaqen, duket se ata nuk e din\u00eb at\u00eb q\u00eb presin, sepse atje nuk ka ende asgj\u00eb. Dhe Postgres e b\u00ebn k\u00ebt\u00eb: kur nuk mund t\u00eb marr\u00ebsh nj\u00eb bllokim, ai shkruan \"\u00c7far\u00eb ka kuptim t'ju p\u00ebrpunoj?\", sepse ti edhe ashtu po pret dik\u00eb. Prandaj, thjesht le t\u00eb presim n\u00eb aj\u00ebr, ai as q\u00eb e p\u00ebrdit\u00ebson k\u00ebt\u00eb. Por \u00e7far\u00eb ndodhi k\u00ebtu? Sa her\u00eb q\u00eb 702 p\u00ebrfundoi procesin dhe 703 mori bllokimin e tij, sistemi u kthye. Dhe tha se tani kemi dy njer\u00ebz q\u00eb jan\u00eb n\u00eb pritje. Dhe pastaj le t'i p\u00ebrdit\u00ebsojm\u00eb ata s\u00eb bashku. Dhe t\u00eb tregojm\u00eb se t\u00eb dy jan\u00eb n\u00eb pritje. <\/p>\n
<\/p>\n
Nuk e di pse Postgres e b\u00ebn k\u00ebshtu. Por ka nj\u00eb problem q\u00eb quhet f... M\u00eb duket se nuk \u00ebsht\u00eb nj\u00eb term n\u00eb shqip. Kjo \u00ebsht\u00eb kur t\u00eb gjith\u00eb presin nj\u00eb bllokim, edhe n\u00ebse ka 20 instance q\u00eb presin bllokimin. Dhe papritmas, ata t\u00eb gjith\u00eb zgjohen n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb. Dhe t\u00eb gjith\u00eb fillojn\u00eb t\u00eb p\u00ebrpiqen t\u00eb reagojn\u00eb. Por sistemi b\u00ebn n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb gjith\u00eb t\u00eb presin 703. Sepse ata t\u00eb gjith\u00eb presin, dhe ne i vije n\u00eb radh\u00eb t\u00eb gjith\u00eb menj\u00ebher\u00eb. Dhe n\u00ebse ndonj\u00eb k\u00ebrkes\u00eb tjet\u00ebr e re shfaqet, q\u00eb \u00ebsht\u00eb formuar pas k\u00ebsaj, p\u00ebr shembull, 707, at\u00ebher\u00eb aty p\u00ebrs\u00ebri do t\u00eb jet\u00eb boshll\u00ebk. <\/p>\n
<\/p>\n
Dhe m\u00eb duket se kjo b\u00ebhet q\u00eb t\u00eb mund t\u00eb thuhet se n\u00eb k\u00ebt\u00eb hap 702 pret 703, dhe t\u00eb gjith\u00eb ata q\u00eb do t\u00eb vijn\u00eb pas k\u00ebsaj, nuk do t\u00eb ken\u00eb asnj\u00eb regjistrim n\u00eb k\u00ebt\u00eb fush\u00eb. Por sa her\u00eb q\u00eb ai q\u00eb pret i par\u00eb largohet, dhe t\u00eb gjith\u00eb ata q\u00eb prisnin n\u00eb at\u00eb moment deri n\u00eb p\u00ebrdit\u00ebsim, marrin t\u00eb nj\u00ebjtin shenj\u00eb. Prandaj, m\u00eb duket se kjo \u00ebsht\u00eb b\u00ebr\u00eb q\u00eb ne t\u00eb mund t\u00eb p\u00ebrpunojm\u00eb n\u00eb rend, q\u00eb ata t\u00eb jen\u00eb t\u00eb rregulluar sakt\u00eb. <\/p>\n
<\/p>\n
Un\u00eb gjithmon\u00eb e kam par\u00eb k\u00ebt\u00eb si nj\u00eb fenomen mjaft t\u00eb \u00e7uditsh\u00ebm. Sepse k\u00ebtu, p\u00ebr shembull, ato fare nuk p\u00ebrmenden. Por, m\u00eb duket se sa her\u00eb q\u00eb ne i japim nj\u00eb bllokim t\u00eb ri, ne shikojm\u00eb t\u00eb gjith\u00eb ata q\u00eb jan\u00eb n\u00eb procesin e pritjes. At\u00ebher\u00eb ne i radhitim ata t\u00eb gjith\u00eb n\u00eb radh\u00eb. Dhe pastaj \u00e7do i ri q\u00eb vjen, futet n\u00eb radh\u00eb vet\u00ebm kur personi i ardhsh\u00ebm ka p\u00ebrfunduar p\u00ebrpunimin. Nj\u00eb pyetje shum\u00eb e mir\u00eb. Faleminderit shum\u00eb p\u00ebr pyetjen!<\/p>\n
<\/p>\n
M\u00eb duket se \u00ebsht\u00eb shum\u00eb m\u00eb logjike kur 705 pret 704.<\/em> <\/p>\n<\/p>\n
Por problemi k\u00ebtu \u00ebsht\u00eb ky. Teknikisht, mund t\u00eb zgjojm\u00eb nj\u00ebrin ose tjetrin. Prandaj, ne do t\u00eb zgjojm\u00eb nj\u00ebrin ose tjetrin. Por \u00e7far\u00eb ndodh n\u00eb funksionimin e sistemit? Ju shihni se 703 n\u00eb krye ka bllokuar ID-n\u00eb e tij t\u00eb transaksionit. K\u00ebshtu funksionon Postgres. Dhe 703 bllokohet nga ID-ja e tij e transaksionit, prandaj, n\u00ebse dikush d\u00ebshiron t\u00eb pres\u00eb, do t\u00eb pres\u00eb 703. Dhe n\u00eb thelb, 703 p\u00ebrfundon. Dhe vet\u00ebm pasi t\u00eb p\u00ebrfundoj\u00eb, ndonj\u00eb nga proceset zgjohet. Dhe ne nuk e dim\u00eb se cili proces do t\u00eb jet\u00eb. Pastaj ne i trajtojm\u00eb gradualisht t\u00eb gjitha. Por nuk \u00ebsht\u00eb e qart\u00eb se cili proces zgjohet i pari, sepse mund t\u00eb jet\u00eb ndonj\u00eb nga k\u00ebta procese. N\u00eb thelb, kishim nj\u00eb planifikues q\u00eb tha se tani mund t\u00eb zgjojm\u00eb ndonj\u00eb nga k\u00ebta procese. Ne thjesht zgjedhim nj\u00ebrin rast\u00ebsisht. Prandaj t\u00eb dy duhet t\u00eb sh\u00ebnohen, sepse ne mund ta zgjojm\u00eb ndonj\u00ebrin prej tyre. <\/p>\n
<\/p>\n
Dhe problemi \u00ebsht\u00eb se kemi CP-pafund\u00ebsi. Prandaj, \u00ebsht\u00eb shum\u00eb e mundur t\u00eb zgjojm\u00eb t\u00eb vonuarin. Dhe n\u00ebse, p\u00ebr shembull, do t\u00eb zgjojm\u00eb t\u00eb vonuarin, do t\u00eb presim at\u00eb q\u00eb sapo mori bllokimin, prandaj nuk e p\u00ebrcaktojm\u00eb se cili do t\u00eb zgjohet i pari. Ne krijojm\u00eb thjesht nj\u00eb situat\u00eb t\u00eb till\u00eb, dhe sistemi do t'i zgjoj\u00eb n\u00eb rend t\u00eb rast\u00ebsish\u00ebm. <\/p>\n
<\/p>\n
Ka artikuj p\u00ebr locks t\u00eb Egor Rogov<\/a><\/noindex>. Shikoni, ata gjithashtu jan\u00eb interesant\u00eb dhe t\u00eb dobish\u00ebm. Tema, sigurisht, \u00ebsht\u00eb jasht\u00ebzakonisht e komplikuar. Faleminderit shum\u00eb, Bruce!<\/em><\/p>\nBurimi: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"\u0420\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u0430 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 2020 \u0433\u043e\u0434\u0430 \u0411\u0440\u044e\u0441\u0430 \u041c\u043e\u043c\u0436\u0438\u0430\u043d\u0430 "Unlocking the Postgres Lock Manager". (\u041f\u0440\u0438\u043c\u0435\u0447\u0430\u043d\u0438\u0435: \u0412\u0441\u0435 SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0438\u0437 \u0441\u043b\u0430\u0439\u0434\u043e\u0432 \u0432\u044b \u043c\u043e\u0436\u0435\u0442\u0435 \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043f\u043e \u044d\u0442\u043e\u0439 \u0441\u0441\u044b\u043b\u043a\u0435: http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql) \u041f\u0440\u0438\u0432\u0435\u0442! \u0417\u0430\u043c\u0435\u0447\u0430\u0442\u0435\u043b\u044c\u043d\u043e \u0441\u043d\u043e\u0432\u0430 \u0431\u044b\u0442\u044c \u0437\u0434\u0435\u0441\u044c \u0432 \u0420\u043e\u0441\u0441\u0438\u0438. \u042f \u043f\u0440\u043e\u0448\u0443 \u043f\u0440\u043e\u0449\u0435\u043d\u0438\u0435, \u0447\u0442\u043e \u044f \u043d\u0435 \u0441\u043c\u043e\u0433 \u043f\u0440\u0438\u0435\u0445\u0430\u0442\u044c \u0432 \u043f\u0440\u043e\u0448\u043b\u043e\u043c \u0433\u043e\u0434\u0443, \u043d\u043e \u0432 \u044d\u0442\u043e\u043c \u0433\u043e\u0434\u0443 \u0443 \u0418\u0432\u0430\u043d\u0430 \u0438 \u0443 \u043c\u0435\u043d\u044f \u0431\u043e\u043b\u044c\u0448\u0438\u0435 \u043f\u043b\u0430\u043d\u044b. \u042f, \u043d\u0430\u0434\u0435\u044e\u0441\u044c, […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":87955,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-87954","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t
![\"Zhbllokimi](\"\/wp-content\/uploads\/2020\/07\/1f75fbb0cb41c306c33c823ef55f7238.png\")
<\/p>\n