{"id":87954,"date":"2020-07-12T01:42:48","date_gmt":"2020-07-11T23:42:48","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian"},"modified":"2020-07-12T01:42:48","modified_gmt":"2020-07-11T23:42:48","slug":"unlocking-the-postgres-lock-manager-bryus-momzhian","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","title":{"rendered":"\u00c7lirimi i Menaxherit t\u00eb Bllokimeve Postgres. Bruc Momjian","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Shqyrtimi i raportit t\u00eb vitit 2020 nga Bruce Momjian \"\u00c7lirimi i Menaxherit t\u00eb Bllokimeve Postgres\".<\/p>\n

<\/p>\n

\"\u00c7lirimi<\/p>\n

<\/p>\n

(Vini re: T\u00eb gjitha k\u00ebrkesat SQL nga diapozitiv\u00ebt mund t\u00eb merren nga ky lidhje: http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex>)<\/p>\n

<\/p>\n

P\u00ebrsh\u00ebndetje! \u00cbsht\u00eb shk\u00eblqyer t\u00eb jem s\u00ebrish k\u00ebtu n\u00eb Rusi. M\u00eb vjen keq q\u00eb nuk arrita t\u00eb vij vitin e kaluar, por k\u00ebt\u00eb vit Ivan dhe un\u00eb kemi plane t\u00eb m\u00ebdha. Shpresoj se do t\u00eb jem k\u00ebtu shum\u00eb m\u00eb shpesh. M\u00eb p\u00eblqen shum\u00eb t\u00eb vij n\u00eb Rusi. Do t\u00eb vizitoj Tyumen dhe Tver. Jam shum\u00eb i lumtur q\u00eb do t\u00eb kem mund\u00ebsin\u00eb t\u00eb jem n\u00eb k\u00ebto qytete. <\/p>\n

<\/p>\n

Un\u00eb quhem Bruce Momjian. Punoj n\u00eb EnterpriseDB dhe kam punuar me Postgres p\u00ebr m\u00eb shum\u00eb se 23 vjet. Jetoj n\u00eb Filadelfia, SHBA. Udhtoj rreth 90 dit\u00eb n\u00eb vit dhe marr pjes\u00eb n\u00eb rreth 40 konferenca. Web faqja ime e cila p\u00ebrmban diapozitiv\u00ebt q\u00eb do t'ju tregoj tani. Pra, pas konferenc\u00ebs mund t'i shkarkoni nga faqja ime personale. Aty gjithashtu mund t\u00eb gjeni rreth 30 prezantime. Gjithashtu ka video dhe nj\u00eb num\u00ebr t\u00eb madh sh\u00ebnimesh n\u00eb blog, m\u00eb shum\u00eb se 500. Kjo \u00ebsht\u00eb nj\u00eb burim mjaft i dobish\u00ebm. N\u00ebse jeni t\u00eb interesuar n\u00eb k\u00ebt\u00eb material, ju ftoj ta p\u00ebrdorni.<\/a><\/noindex>Isha m\u00eb par\u00eb nj\u00eb m\u00ebsues, profesor para se t\u00eb filloja pun\u00ebn me Postgres. Jam shum\u00eb i emocionuar q\u00eb do t\u00eb kem mund\u00ebsin\u00eb t'ju flas p\u00ebr at\u00eb q\u00eb do t'ju tregoj tani. Kjo \u00ebsht\u00eb nj\u00eb nga prezantimet m\u00eb interesante q\u00eb kam. Dhe kjo prezantim ka 110 diapozitiva. Do t\u00eb fillojm\u00eb me gj\u00ebra t\u00eb thjeshta, e m\u00eb pas prezantimi do t\u00eb b\u00ebhet gjithnj\u00eb e m\u00eb kompleks. <\/p>\n

<\/a><\/noindex><\/p>\n

Ky \u00ebsht\u00eb nj\u00eb bised\u00eb e m\u00ebrzitshme. Bllokimi nuk \u00ebsht\u00eb nj\u00eb tem\u00eb shum\u00eb popullore. Ne duam q\u00eb ajo t\u00eb zhduket. \u00cbsht\u00eb si t\u00eb shkelesh te stomatologu.<\/p>\n

<\/p>\n

\"\u00c7lirimi<\/p>\n

<\/p>\n

Bllokimi \u00ebsht\u00eb nj\u00eb problem p\u00ebr shum\u00eb njer\u00ebz q\u00eb punojn\u00eb me bazat e t\u00eb dh\u00ebnave dhe kan\u00eb disa procese q\u00eb funksionojn\u00eb nj\u00ebkoh\u00ebsisht. Ata kan\u00eb nevoj\u00eb p\u00ebr bllokim. Pra, sot do t'ju jap baza mbi bllokimin. <\/p>\n

<\/p>\n

\"\u00c7lirimi<\/p>\n

<\/p>\n

    \n
  1. Identifikuesit e transaksioneve. Kjo \u00ebsht\u00eb nj\u00eb pjes\u00eb e m\u00ebrzitshme e prezantimit, por \u00ebsht\u00eb e nevojshme ta kuptoni. <\/li>\n
  2. M\u00eb pas do t\u00eb flasim p\u00ebr llojet e bllokimeve. Kjo \u00ebsht\u00eb nj\u00eb pjes\u00eb mjaft mekanike.<\/li>\n
  3. Dhe m\u00eb pas do t\u00eb japim disa shembuj bllokimesh. Kjo do t\u00eb jet\u00eb e nd\u00ebrlikuar p\u00ebr t'u kuptuar. <\/li>\n
  4. Le t\u00eb flasim p\u00ebr bllokimet. <\/li>\n<\/ol>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Terminologjia \u00ebsht\u00eb mjaft komplekse. Sa prej jush e din\u00eb se nga vjen ky fragment? Dy njer\u00ebz. Ky \u00ebsht\u00eb nga nj\u00eb loj\u00eb q\u00eb quhet \"Krahu i Madh i Ndihm\u00ebs\". Ishte nj\u00eb loj\u00eb kompjuterike tekstuale n\u00eb vitet '80, m\u00eb duket. Duhej t\u00eb hyje n\u00eb nj\u00eb shpella, n\u00eb nj\u00eb labirint dhe teksti ndryshonte, megjithat\u00eb p\u00ebrmbajtja ishte p\u00ebraf\u00ebrsisht e nj\u00ebjt\u00eb \u00e7do her\u00eb. K\u00ebshtu e mbaj mend k\u00ebt\u00eb loj\u00eb.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe k\u00ebtu shohim emrat e bllokimeve q\u00eb na vijn\u00eb nga Oracle. Ne i p\u00ebrdorim ato. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebtu shohim terma q\u00eb m\u00eb ngat\u00ebrrojn\u00eb. P\u00ebr shembull, SHARE UPDATE EXCLUSIVE. M\u00eb pas SHARE RAW EXCLUSIVE. P\u00ebr t\u00eb qen\u00eb t\u00eb sinqert\u00eb, k\u00ebto emra nuk jan\u00eb shum\u00eb t\u00eb qart\u00eb. Do t\u00eb p\u00ebrpiqemi t'i shqyrtojm\u00eb ato m\u00eb n\u00eb detaje. Disa p\u00ebrmbajn\u00eb fjal\u00ebn \"share\", q\u00eb do t\u00eb thot\u00eb \u2013 t\u00eb ndahesh. Disa p\u00ebrmbajn\u00eb fjal\u00ebn \"exclusive\" \u2014 ekskluzive. Disa p\u00ebrmbajn\u00eb t\u00eb dyja. Do t\u00eb doja t\u00eb filloja me m\u00ebnyr\u00ebn se si funksionojn\u00eb k\u00ebto bllokime. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Gjithashtu \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme fjala \"qasje\" \u2014 access. Dhe fjal\u00ebt \"row\" \u2014 rresht. Pra, ndarja e qasjes, ndarja e rreshtave. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Nj\u00eb problem tjet\u00ebr q\u00eb duhet t\u00eb kuptohet n\u00eb Postgres, p\u00ebr fat t\u00eb keq, nuk do t\u00eb mund t'iu flas p\u00ebr t\u00eb n\u00eb prezantimin tim, \u00ebsht\u00eb MVCC. Kam nj\u00eb prezantim t\u00eb ve\u00e7ant\u00eb mbi k\u00ebt\u00eb tem\u00eb n\u00eb web faqen time. Dhe n\u00ebse mendoni se kjo prezantim \u00ebsht\u00eb e nd\u00ebrlikuar, at\u00ebher\u00eb MVCC \u00ebsht\u00eb ndoshta e mia m\u00eb e nd\u00ebrlikuar. Dhe n\u00ebse jeni t\u00eb interesuar, mund ta shikoni n\u00eb faqe. Mund t\u00eb shikoni videon. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Nj\u00eb tjet\u00ebr moment q\u00eb duhet t\u00eb kuptojm\u00eb \u2013 jan\u00eb identifikuesit e transaksionit. Shum\u00eb transaksione nuk mund t\u00eb funksionojn\u00eb pa identifikues unik. Dhe k\u00ebtu kemi nj\u00eb shpjegim se \u00e7far\u00eb \u00ebsht\u00eb nj\u00eb transaksion. N\u00eb Postgres ka dy sisteme num\u00ebrimi t\u00eb transaksioneve. E di, kjo nuk \u00ebsht\u00eb nj\u00eb zgjidhje shum\u00eb elegante. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Gjithashtu mbani n\u00eb mend se diapozitiv\u00ebt do t\u00eb jen\u00eb mjaft t\u00eb nd\u00ebrlikuar p\u00ebr t'u kuptuar, k\u00ebshtu q\u00eb e r\u00ebnd\u00ebsishme \u00ebsht\u00eb t\u00eb kushtoni v\u00ebmendje asaj q\u00eb \u00ebsht\u00eb theksuar me ngjyr\u00eb t\u00eb kuqe. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Shikoni. Me ngjyr\u00eb t\u00eb kuqe \u00ebsht\u00eb theksuar numri i transaksionit. Aty tregohen funksioni SELECT pg_back. Ai kthen transaksionin tim dhe ID-n\u00eb e k\u00ebtij transaksioni.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex><\/p>\n

    <\/p>\n

    Nj\u00eb moment tjet\u00ebr, n\u00ebse ju p\u00eblqen kjo prezantim dhe d\u00ebshironi ta ekzekutoni n\u00eb baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave, at\u00ebher\u00eb mund t\u00eb shkoni n\u00eb k\u00ebt\u00eb lidhje, t\u00eb theksuar me ngjyr\u00eb t\u00eb roz\u00eb, dhe t\u00eb shkarkoni SQL p\u00ebr k\u00ebt\u00eb prezantim. Dhe mund ta ekzekutoni thjesht n\u00eb PSQL tuaj dhe t\u00eb gjith\u00eb prezantimi do t\u00eb shfaqet n\u00eb ekranin tuaj menj\u00ebher\u00eb. Nuk do t\u00eb ket\u00eb ngjyrash, por s\u00eb paku do ta shihni. <\/p>\n

    <\/p>\n

    Nj\u00eb pik\u00eb tjet\u00ebr \u00ebsht\u00eb, n\u00ebse ju p\u00eblqeu kjo prezentim dhe d\u00ebshironi ta lan\u00e7oni at\u00eb n\u00eb baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave, mund t\u00eb kaloni p\u00ebrmes k\u00ebsaj lidhjeje t\u00eb ndri\u00e7uar me ngjyr\u00eb roz\u00eb dhe t\u00eb shkarkoni SQL p\u00ebr k\u00ebt\u00eb prezentim. Dhe mund ta drejtoni thjesht n\u00eb PSQL tuaj dhe e gjith\u00eb prezantimi do t\u00eb shfaqet n\u00eb ekranin tuaj menj\u00ebher\u00eb. Nuk do t\u00eb p\u00ebrmbaj\u00eb ngjyra, por s\u00eb paku do ta shohim.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00eb k\u00ebt\u00eb rast, ne shohim ID-n\u00eb e transaksionit. Ky \u00ebsht\u00eb numri q\u00eb i kemi caktuar. Ka gjithashtu nj\u00eb lloj tjet\u00ebr ID t\u00eb transaksionit n\u00eb Postgres, i quajtur ID virtual i transaksionit.<\/p>\n

    <\/p>\n

    Dhe ne duhet ta kuptojm\u00eb k\u00ebt\u00eb. \u00cbsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme, p\u00ebrndryshe nuk do t\u00eb mund t\u00eb kuptojm\u00eb bllokimin n\u00eb Postgres.<\/p>\n

    <\/p>\n

    ID virtual i transaksionit \u00ebsht\u00eb ID e transaksionit q\u00eb nuk p\u00ebrmban vlera t\u00eb p\u00ebrhershme. P\u00ebr shembull, n\u00ebse ekzekutoj komand\u00ebn SELECT, ndoshta nuk do t\u00eb ndryshoj baz\u00ebn e t\u00eb dh\u00ebnave, nuk do t\u00eb bllokoj asgj\u00eb. Prandaj, kur ekzekutojm\u00eb nj\u00eb SELECT t\u00eb thjesht\u00eb, ne nuk i japim k\u00ebsaj transaksioni nj\u00eb ID t\u00eb p\u00ebrhershme. Aty 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-ja virtuale e transaksionit p\u00ebrb\u00ebhet nga dy numra. Numri i par\u00eb para shenj\u00ebs s\u00eb ndarjes \u00ebsht\u00eb ID e backend-it. Nd\u00ebrsa n\u00eb t\u00eb djatht\u00eb shohim thjesht nj\u00eb num\u00ebr counting.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Prandaj, n\u00ebse ekzekutoj nj\u00eb pyetje, ajo thot\u00eb se ID backend \u00ebsht\u00eb 2. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00ebse ekzekutoj nj\u00eb seri t\u00eb till\u00eb transaksionesh, at\u00ebher\u00eb shohim se numri counting rritet \u00e7do her\u00eb kur ekzekutoj nj\u00eb pyetje. P\u00ebr shembuj, kur ekzekutoj pyetje 2\/10, 2\/11, 2\/12 etj. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Mbani mend se k\u00ebtu ka dy kolona. N\u00eb t\u00eb majt\u00eb ne shohim ID virtual t\u00eb transaksionit \u2013 2\/12. Nd\u00ebrsa n\u00eb t\u00eb djatht\u00eb kemi ID-n\u00eb e p\u00ebrhershme 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 caktohem nj\u00eb ID t\u00eb p\u00ebrhershme transaksioni.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Sapo t\u00eb ekzekutoj komand\u00ebn ANALYZE, e nj\u00ebjta pyetje m\u00eb jep nj\u00eb ID t\u00eb p\u00ebrhershme t\u00eb transaksionit. Shikoni se si ka ndryshuar kjo. M\u00eb par\u00eb nuk e kisha k\u00ebt\u00eb ID, tani ka dal\u00eb.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Pra, k\u00ebtu \u00ebsht\u00eb nj\u00eb pyetje tjet\u00ebr, nj\u00eb transaksion tjet\u00ebr. Numri virtual i transaksionit \u00ebsht\u00eb 2\/13. Dhe n\u00ebse k\u00ebrkoj ID-n\u00eb e p\u00ebrhershme t\u00eb transaksionit, at\u00ebher\u00eb kur ekzekutoj pyetjen, do ta marr. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Pra, p\u00ebrs\u00ebri. Ne kemi ID virtual t\u00eb transaksionit dhe ID t\u00eb p\u00ebrhershme t\u00eb transaksionit. Thjesht kuptoni k\u00ebt\u00eb p\u00ebr t\u00eb kuptuar sjelljen e Postgres.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kemi kaluar n\u00eb seksionin e tret\u00eb. K\u00ebtu do t\u00eb kalojm\u00eb p\u00ebrmes llojeve t\u00eb ndryshme t\u00eb bllokimeve n\u00eb Postgres. Kjo nuk \u00ebsht\u00eb shum\u00eb emocionuese. Seksioni i fundit do t\u00eb jet\u00eb shum\u00eb m\u00eb interesant. Por ne duhet t\u00eb shqyrtojm\u00eb gj\u00ebrat bazike, p\u00ebrndryshe nuk do t\u00eb kuptojm\u00eb at\u00eb q\u00eb do t\u00eb vij\u00eb m\u00eb pas. <\/p>\n

    <\/p>\n

    Do t\u00eb kalojm\u00eb n\u00ebp\u00ebr k\u00ebt\u00eb seksion, do t\u00eb shohim \u00e7do lloj bllokimi. Dhe un\u00eb do t'ju tregoj shembuj se si vendosen, si funksionojn\u00eb, do t'ju tregoj disa pyetje q\u00eb mund t\u00eb p\u00ebrdorni p\u00ebr t\u00eb par\u00eb si funksionon bllokimi n\u00eb Postgres.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    P\u00ebr t\u00eb krijuar nj\u00eb pyetje dhe p\u00ebr t\u00eb par\u00eb se \u00e7far\u00eb ndodh n\u00eb Postgres, na nevojitet t\u00eb l\u00ebshojm\u00eb nj\u00eb pyetje n\u00eb view t\u00eb sistemit. N\u00eb k\u00ebt\u00eb rast, ngjyra e kuqe na thekson pg_lock. Pg_lock \u00ebsht\u00eb nj\u00eb tabel\u00eb sistemike q\u00eb na thot\u00eb se cilat bllokime po p\u00ebrdoren aktualisht n\u00eb Postgres.<\/p>\n

    <\/p>\n

    Megjithat\u00eb, \u00ebsht\u00eb shum\u00eb e v\u00ebshtir\u00eb p\u00ebr mua t\u00eb tregoj pg_lock si vet\u00eb, sepse \u00ebsht\u00eb mjaft e komplikuar. Prandaj kam krijuar nj\u00eb view q\u00eb tregon pg_locks. Dhe ai gjithashtu kryen disa pun\u00eb p\u00ebr mua, e cila m\u00eb ndihmon t\u00eb kuptoj m\u00eb mir\u00eb. Do thot\u00eb, ai p\u00ebrjashton bllokimet e mia, sesionin tim etj. Kjo \u00ebsht\u00eb thjesht SQL standard dhe ai do t'ju lejoj\u00eb t\u00eb tregoni m\u00eb mir\u00eb se \u00e7far\u00eb ndodh. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Nj\u00eb tjet\u00ebr problem \u00ebsht\u00eb se ky view \u00ebsht\u00eb shum\u00eb i gjer\u00eb, prandaj duhet t\u00eb krijoj nj\u00eb t\u00eb dyt\u00eb \u2013 lockview2.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi Dhe ai m\u00eb tregon edhe kolona t\u00eb tjera nga tabela. Dhe nj\u00eb tjet\u00ebr q\u00eb m\u00eb tregon kolona t\u00eb tjera. Kjo \u00ebsht\u00eb mjaft e komplikuar, prandaj kam p\u00ebrpjekur ta paraqes sa m\u00eb thjesh q\u00eb \u00ebsht\u00eb e mundur. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Pra, krijuam nj\u00eb tabel\u00eb t\u00eb quajtur Lockdemo. Dhe kemi krijuar nj\u00eb rresht aty. Ky \u00ebsht\u00eb tabela jon\u00eb shembuj. Dhe ne do t\u00eb krijojm\u00eb seksione p\u00ebr t\u00eb treguar shembuj bllokimesh. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/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 ndaluese. Kjo do t\u00eb thot\u00eb se ai pothuajse nuk p\u00ebrplaset me bllokime t\u00eb tjera.<\/p>\n

    <\/p>\n

    Dhe n\u00ebse duam ta p\u00ebrcaktojm\u00eb bllokimin n\u00eb m\u00ebnyr\u00eb eksplicite, ne ekzekutojm\u00eb komand\u00ebn \"lock table\". Dhe ajo do ta bllokoj\u00eb n\u00eb m\u00ebnyr\u00eb eksplicite, do thot\u00eb, n\u00eb m\u00ebnyr\u00eb ACCESS SHARE ne ekzekutojm\u00eb lock table. Dhe n\u00ebse un\u00eb e ekzekutoj PSQL n\u00eb sfond, at\u00ebher\u00eb un\u00eb ekzekutoj k\u00ebshtu nj\u00eb sesion t\u00eb dyt\u00eb nga sesioni im i par\u00eb. Do thot\u00eb, \u00e7far\u00eb do t\u00eb b\u00ebj k\u00ebtu? Un\u00eb kaloj n\u00eb nj\u00eb sesion tjet\u00ebr dhe i them \"trego lockview p\u00ebr k\u00ebt\u00eb pyetje\". Dhe k\u00ebtu kam AccessShareLock n\u00eb k\u00ebt\u00eb tabel\u00eb. Kjo \u00ebsht\u00eb pik\u00ebrisht ajo q\u00eb k\u00ebrkova. Dhe ai thot\u00eb se bllokimi iu caktua. Shum\u00eb e thjesht\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    M\u00eb pas, n\u00ebse shohim n\u00eb kolon\u00ebn e dyt\u00eb, nuk ka asgj\u00eb. Ato jan\u00eb bosh. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00ebse ekzekutoj komand\u00ebn \"SELECT\", kjo \u00ebsht\u00eb nj\u00eb m\u00ebnyr\u00eb e implicit (shfaqur) p\u00ebr t\u00eb k\u00ebrkuar AccessShareLock. Pra, l\u00ebshoj tabel\u00ebn time dhe ekzekutoj pyetjen, dhe pyetja kthen disa rreshta. Dhe n\u00eb nj\u00eb nga rreshtat shohim AccessShareLock. Pra, SELECT shkakton AccessShareLock n\u00eb tabel\u00eb. Dhe nuk p\u00ebrplaset pothuajse me asgj\u00eb, sepse \u00ebsht\u00eb bllokim i nivelit t\u00eb ul\u00ebt. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    \u00c7far\u00eb ndodh n\u00ebse un\u00eb ekzekutoj nj\u00eb SELECT dhe kam tre tabela t\u00eb ndryshme? M\u00eb par\u00eb kam ekzekutuar vet\u00ebm nj\u00eb tabel\u00eb, tani po ekzekutoj tre: pg_class, pg_namespace dhe pg_attribute.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe tani, kur e shqyrtoj k\u00ebrkes\u00ebn, shoh 9 AccessShareLocks n\u00eb tre tabelat. Pse? Me ngjyr\u00eb blu jan\u00eb t\u00eb theksuara tre tabelat: pg_attribute, pg_class, pg_namespace. Por gjithashtu mund t\u00eb shihni se t\u00eb gjitha indekset q\u00eb jan\u00eb t\u00eb p\u00ebrcaktuara p\u00ebrmes k\u00ebtyre tabelave gjithashtu kan\u00eb AccessShareLock.<\/p>\n

    <\/p>\n

    Dhe kjo \u00ebsht\u00eb nj\u00eb bllokim q\u00eb praktikisht nuk shkakton konflikte me t\u00eb tjer\u00ebt. T\u00eb gjitha q\u00eb b\u00ebn \u00ebsht\u00eb se nuk lejon q\u00eb ne t\u00eb heqim tabel\u00ebn derisa po e zgjedhim. Kjo ka kuptim. D.m.th., n\u00ebse ne zgjedhim nj\u00eb tabel\u00eb dhe ajo n\u00eb at\u00eb moment zhduket, kjo \u00ebsht\u00eb e gabuar, 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 nd\u00ebrsa un\u00eb po punoj\".<\/strong>. N\u00eb thelb, kjo \u00ebsht\u00eb gjith\u00e7ka q\u00eb b\u00ebn. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    ROW SHARE \u00ebsht\u00eb nj\u00eb bllokim paksa ndryshe. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Le ta marrim nj\u00eb shembull. SELECT ROW SHARE \u00ebsht\u00eb m\u00ebnyra e bllokimit t\u00eb \u00e7do rreshti ve\u00e7 e ve\u00e7.<\/strong>K\u00ebshtu askush nuk mund t\u2019i fshij\u00eb apo ndryshoj\u00eb ata derisa ne t\u2019i shohim. <\/p>\n

    <\/p>\n

    \"\u00c7lirimiPra, \u00e7far\u00eb b\u00ebn SHARE LOCK? Ne shohim q\u00eb ID e transaksionit 681 p\u00ebr SELECT. Dhe kjo \u00ebsht\u00eb interesante. \u00c7far\u00eb ndodhi k\u00ebtu? Her\u00ebn e par\u00eb ne shohim nj\u00eb num\u00ebr n\u00eb fush\u00ebn \"Lock\". Ne marrim ID e transaksionit dhe ai thot\u00eb se bllokon at\u00eb n\u00eb m\u00ebnyr\u00eb ekskluzive. E gjith\u00eb \u00e7far\u00eb b\u00ebn, \u00ebsht\u00eb se thot\u00eb se kam nj\u00eb rresht q\u00eb teknalisht \u00ebsht\u00eb bllokuar diku n\u00eb tabel\u00eb. Por nuk thot\u00eb se ku sakt\u00ebsisht. M\u00eb von\u00eb do ta shqyrtojm\u00eb k\u00ebt\u00eb m\u00eb n\u00eb detaje.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebtu ne po flasim se bllokimi p\u00ebrdoret nga ne.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Pra, bllokimi ekskluziv n\u00eb m\u00ebnyr\u00eb eksplicite thot\u00eb se \u00ebsht\u00eb ekskluziv. Dhe gjithashtu, n\u00ebse fshini nj\u00eb rresht n\u00eb k\u00ebt\u00eb tabel\u00eb, kjo edhe do t\u00eb ndodh\u00eb, si\u00e7 mund ta shihni. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    SHARE EXCLUSIVE \u00ebsht\u00eb nj\u00eb bllokim m\u00eb i gjat\u00eb.<\/strong><\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kjo \u00ebsht\u00eb komanda (ANALYZE) e analizuesit q\u00eb do t\u00eb p\u00ebrdoret.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    SHARE LOCK - ju mund ta bllokoni n\u00eb m\u00ebnyr\u00eb eksplicite n\u00eb mod\u00ebn share.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ju gjithashtu mund t\u00eb krijoni nj\u00eb indeks unik. Dhe atje mund ta shihni SHARE LOCK, q\u00eb \u00ebsht\u00eb pjes\u00eb e tyre. Dhe ai bllokon tabel\u00ebn dhe vendos mbi t\u00eb nj\u00eb bllokim SHARE LOCK.<\/p>\n

    <\/p>\n

    P\u00ebr default, SHARE LOCK n\u00eb tabel\u00eb do t\u00eb thot\u00eb se njer\u00ebzit e tjer\u00eb mund ta lexojn\u00eb tabel\u00ebn, por askush nuk mund ta modifikoj\u00eb. Dhe kjo ndodh kur krijoni nj\u00eb indeks unik.<\/strong><\/p>\n

    <\/p>\n

    N\u00ebse krijoj nj\u00eb indeks unik me koh\u00eb, do t\u00eb kem nj\u00eb lloj tjet\u00ebr bllokimi, sepse, si\u00e7 e mbani mend, p\u00ebrdorimi i indekseve me koh\u00eb ul k\u00ebrkesat p\u00ebr bllokim. Dhe n\u00ebse p\u00ebrdor bllokimin normal, indeksi normal, at\u00ebher\u00eb n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb parandaloj regjistrimin n\u00eb indeksin e tabel\u00ebs gjat\u00eb krijimit t\u00eb tij. N\u00ebse p\u00ebrdor indeksin me koh\u00eb, at\u00ebher\u00eb duhet t\u00eb p\u00ebrdor nj\u00eb lloj tjet\u00ebr bllokimi. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    SHARE ROW EXCLUSIVE - p\u00ebrs\u00ebri, mund t\u00eb vendoset n\u00eb m\u00ebnyr\u00eb eksplicite.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ose mund t\u00eb krijojm\u00eb nj\u00eb rregull, d.m.th. t\u00eb marrim ndonj\u00eb rast t\u00eb caktuar, n\u00eb t\u00eb cilin ajo do t\u00eb p\u00ebrdoret.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Bllokimi EKSKLUZIV do t\u00eb thot\u00eb se askush tjet\u00ebr nuk mund ta ndryshoj\u00eb tabel\u00ebn. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebtu shohim lloje t\u00eb ndryshme bllokimesh. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    ACCESS EXCLUSIVE, p\u00ebr shembull, \u00ebsht\u00eb komanda bllokuese. P\u00ebr shembull, n\u00ebse b\u00ebni CLUSTER table<\/code>, kjo do t\u00eb thot\u00eb se askush nuk do t\u00eb mund t\u00eb shkruaj\u00eb aty. Dhe bllokon jo vet\u00ebm tabel\u00ebn, por edhe indekset gjithashtu.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kjo \u00ebsht\u00eb faqja e dyt\u00eb e bllokimit ACCESS EXCLUSIVE, ku ne 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

    Kjo \u00ebsht\u00eb gjith\u00eb informacioni bazik q\u00eb doja t\u00eb jepja. Flisnim p\u00ebr bllokimet, p\u00ebr ID e transaksioneve, flisnim p\u00ebr ID e transaksioneve virtuale, p\u00ebr ID e transaksioneve t\u00eb p\u00ebrhershme. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe tani ne do t\u00eb shohim 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 jap nj\u00eb pasqyr\u00eb m\u00eb t\u00eb mir\u00eb se \u00e7far\u00eb b\u00ebn Postgres n\u00eb t\u00eb v\u00ebrtet\u00eb kur p\u00ebrpiqet t\u00eb bllokoj\u00eb gj\u00ebra t\u00eb ndryshme. 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.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ne do t\u00eb fillojm\u00eb me tabelat dhe me nj\u00eb rresht n\u00eb tabel\u00eb. Kur un\u00eb fus di\u00e7ka, m\u00eb shfaqet ExclusiveLock, ID e transaksionit dhe ExclusiveLock n\u00eb tabel\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    \u00c7far\u00eb do t\u00eb ndodhte n\u00ebse do t\u00eb fusja edhe dy rreshta? Dhe tani n\u00eb tabel\u00ebn ton\u00eb kemi tre rreshta. Dhe un\u00eb fus nj\u00eb rresht dhe kam k\u00ebt\u00eb n\u00eb daljen. Dhe n\u00ebse fus edhe dy rreshta, \u00e7far\u00eb \u00ebsht\u00eb e \u00e7uditshme k\u00ebtu? K\u00ebtu ka nj\u00eb \u00e7udit\u00ebri, sepse un\u00eb kam shtuar tre rreshta n\u00eb k\u00ebt\u00eb tabel\u00eb, por ende kam dy rreshta n\u00eb bllokimin e tabel\u00ebs. Dhe kjo \u00ebsht\u00eb, n\u00eb thelb, nj\u00eb sjellje themelore e Postgres.<\/p>\n

    <\/p>\n

    Shum\u00eb njer\u00ebz mendojn\u00eb se n\u00ebse bllokoni 100 rreshta n\u00eb nj\u00eb baz\u00eb t\u00eb dh\u00ebnash, do t'ju nevojiten 100 k\u00ebrkesa bllokimi. N\u00ebse bllokoj 1,000 rreshta nj\u00ebher\u00ebsh, at\u00ebher\u00eb do t\u00eb kemi nevoj\u00eb p\u00ebr 1,000 k\u00ebrkesa t\u00eb tilla. Dhe n\u00ebse dua t\u00eb bllokoj nj\u00eb milion ose nj\u00eb miliard rreshta. Por n\u00ebse veprojm\u00eb k\u00ebshtu, do t\u00eb funksionoj\u00eb jo mir\u00eb. N\u00ebse keni p\u00ebrdorur nj\u00eb sistem q\u00eb krijon k\u00ebrkesa bllokimi p\u00ebr \u00e7do rresht t\u00eb ve\u00e7ant\u00eb, at\u00ebher\u00eb e shihni se sa \u00ebsht\u00eb e nd\u00ebrlikuar. Sepse duhet t\u00eb p\u00ebrcaktoni menj\u00ebher\u00eb nj\u00eb tabel\u00eb bllokimi q\u00eb mund t\u00eb vendoset, por Postgres nuk vepron ashtu. <\/p>\n

    <\/p>\n

    Dhe n\u00eb k\u00ebt\u00eb slajd \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme q\u00eb k\u00ebtu shfaqet qart\u00eb se ka nj\u00eb sistem tjet\u00ebr q\u00eb punon brenda MVCC, q\u00eb bllokon rreshta t\u00eb ve\u00e7ant\u00eb. Prandaj, kur bllokoni miliarda rreshta, Postgres nuk krijon miliarda komanda t\u00eb ve\u00e7anta p\u00ebr bllokim. Dhe kjo ndikon shum\u00eb n\u00eb performanc\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Por \u00e7far\u00eb ndodh kur dua t\u00eb p\u00ebrdit\u00ebsoj? Tani po p\u00ebrdit\u00ebsoj nj\u00eb rresht, dhe mund ta vini re se ai ka kryer menj\u00ebher\u00eb dy operacione t\u00eb ndryshme. Ai ka bllokuar tabel\u00ebn nj\u00ebkoh\u00ebsisht, por ai gjithashtu ka bllokuar indeksin. Dhe i duhej t\u00eb bllokonte indeksin, sepse ka kufizime unik n\u00eb k\u00ebt\u00eb tabel\u00eb. Dhe d\u00ebshirojm\u00eb t\u00eb sigurohemi q\u00eb askush t\u00eb mos e ndryshoj\u00eb, prandaj e bllokojm\u00eb.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    \u00c7far\u00eb do t\u00eb ndodhte n\u00ebse do t\u00eb doja t\u00eb p\u00ebrdit\u00ebsoja dy rreshta? Dhe shohim se ai vepron po ashtu. Ne po realizojm\u00eb dyfish m\u00eb shum\u00eb p\u00ebrdit\u00ebsime, por s\u00ebrish po t\u00eb nj\u00ebjtin num\u00ebr rreshtash bllokimi. <\/p>\n

    <\/p>\n

    N\u00ebse ju intereson se si e b\u00ebn Postgres k\u00ebt\u00eb, duhet t\u00eb d\u00ebgjoni fjalimet e mia mbi MVCC, p\u00ebr t\u00eb kuptuar se si Postgres e etiketojn\u00eb brendsh\u00ebm k\u00ebta rreshta q\u00eb ai ndryshon. Dhe Postgres ka nj\u00eb m\u00ebnyr\u00eb q\u00eb e b\u00ebn k\u00ebt\u00eb, por nuk e b\u00ebn n\u00eb nivelin e bllokimit t\u00eb tabelave, ai e b\u00ebn n\u00eb nj\u00eb nivel m\u00eb t\u00eb ul\u00ebt dhe m\u00eb efektiv.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Por n\u00ebse d\u00ebshiroj t\u00eb fshij di\u00e7ka? N\u00ebse po fshij, p\u00ebr shembull, nj\u00eb rresht dhe un\u00eb ende kam dy k\u00ebrkesa bllokimi, dhe madje edhe n\u00ebse d\u00ebshiroj t'i fshij t\u00eb gjitha, ato ende do t\u00eb jen\u00eb atje.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe, p\u00ebr shembull, n\u00ebse un\u00eb d\u00ebshiroj t\u00eb vendos 1,000 rreshta, e pastaj t\u00eb fshij ose t\u00eb shtoj 1,000 rreshta, ato rreshta individual\u00eb q\u00eb un\u00eb shtoj ose ndryshoj, ato 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 tim mbi MVCC, flas p\u00ebr k\u00ebt\u00eb n\u00eb detaje. Por \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme, kur analizoni bllokimet, t\u00eb siguroheni q\u00eb keni bllokim n\u00eb nivel tabelash dhe q\u00eb k\u00ebtu nuk shihni se regjistrohen rreshta t\u00eb ve\u00e7ant\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Si q\u00ebndron \u00e7\u00ebshtja me bllokimin eksplicit?<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00ebse un\u00eb klikoj \"p\u00ebrdit\u00ebso\", at\u00ebher\u00eb kam dy rreshta t\u00eb bllokuar. Dhe n\u00ebse i p\u00ebrzgjedh t\u00eb gjith\u00eb dhe klikoj \"p\u00ebrdit\u00ebso n\u00eb t\u00eb gjith\u00eb\", at\u00ebher\u00eb ende do t'i kem dy regjistrime bllokimi. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ne nuk krijojm\u00eb regjistrime t\u00eb ve\u00e7anta p\u00ebr \u00e7do rresht t\u00eb ve\u00e7ant\u00eb. Sepse at\u00ebher\u00eb performanca do t\u00eb bjer\u00eb, mund t\u00eb ket\u00eb tep\u00ebr shum\u00eb prej tyre. Dhe mund t\u00eb ndodhemi n\u00eb nj\u00eb situat\u00eb t\u00eb pak\u00ebndshme. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Edhe ashtu, n\u00ebse e b\u00ebjm\u00eb n\u00eb m\u00ebnyr\u00eb t\u00eb ndar\u00eb, mund t\u00eb b\u00ebjm\u00eb p\u00ebr t\u00eb gjith\u00eb 30 her\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ne rikthejm\u00eb tabel\u00ebn ton\u00eb, fshijm\u00eb gjith\u00e7ka, pastaj s\u00ebrish vendosim nj\u00eb rresht.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Nj\u00eb njohje tjet\u00ebr t\u00eb sjelljes q\u00eb shihni n\u00eb Postgres, \u00ebsht\u00eb nj\u00eb sjellje shum\u00eb e njohur dhe e d\u00ebshiruar \u2013 \u00ebsht\u00eb q\u00eb ju mund t\u00eb b\u00ebni update ose select. Dhe mund ta b\u00ebni at\u00eb nj\u00ebkoh\u00ebsisht. Dhe select nuk bllokon update dhe t\u00eb nj\u00ebjt\u00ebn gj\u00eb ndodh n\u00eb an\u00ebn tjet\u00ebr. Ne i themi lexuesit t\u00eb mos bllokoj\u00eb at\u00eb q\u00eb shkruan, dhe atij q\u00eb shkruan, t\u00eb mos bllokoj\u00eb lexuesin. <\/p>\n

    <\/p>\n

    Do t'ju tregoj nj\u00eb shembull t\u00eb k\u00ebsaj. Tani do t\u00eb b\u00ebj nj\u00eb p\u00ebrzgjedhje. M\u00eb pas do t\u00eb b\u00ebjm\u00eb INSERT. Dhe pastaj 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 vendosje. Dhe k\u00ebshtu funksionon. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00ebse tani shikoj ID-n\u00eb time t\u00eb backend-it, ajo \u00ebsht\u00eb b\u00ebr\u00eb \u2013 695. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe mund t\u00eb shoh se 695 shfaqet n\u00eb tabel\u00ebn time. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/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 \u2013 \u00ebsht\u00eb nj\u00eb bllokim ekskluziv, dhe p\u00ebrdit\u00ebsimi ka t\u00eb nj\u00ebjtin sjellje, por mes tyre nuk ndodh asnj\u00eb konflikt, q\u00eb \u00ebsht\u00eb mjaft e pazakonshme. <\/p>\n

    <\/p>\n

    Dhe mund t\u00eb vini re se lart \u2013 \u00ebsht\u00eb ShareLock, dhe m\u00eb posht\u00eb \u2013 \u00ebsht\u00eb ExclusiveLock. Dhe t\u00eb dyja transaksionet u realizuan.<\/p>\n

    <\/p>\n

    Dhe \u00ebsht\u00eb e nevojshme 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 si mund t\u00eb b\u00ebni k\u00ebt\u00eb nj\u00ebkoh\u00ebsisht, pra nj\u00ebkoh\u00ebsisht t\u00eb b\u00ebni SELECT dhe UPDATE.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Le t\u00eb reshtim dhe t\u00eb b\u00ebjm\u00eb nj\u00eb veprim nj\u00ebher\u00eb tjet\u00ebr. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00ebse p\u00ebrpiqeni t\u00eb l\u00ebshoni nj\u00ebkoh\u00ebsisht dy p\u00ebrdit\u00ebsime n\u00eb t\u00eb nj\u00ebjtin rresht, at\u00ebher\u00eb do t\u00eb bllokohet. Dhe mbani mend, kam th\u00ebn\u00eb se lexuesi nuk bllokon at\u00eb q\u00eb shkruan, dhe ai q\u00eb shkruan bllokon lexuesin, por nj\u00eb shkruese bllokon tjetrin. Pra, nuk mund t\u00eb lejojm\u00eb q\u00eb dy persona t\u00eb p\u00ebrdit\u00ebsojn\u00eb nj\u00ebkoh\u00ebsisht t\u00eb nj\u00ebjtin rresht. Duhet t\u00eb presim derisa nj\u00ebri prej tyre t\u00eb p\u00ebrfundoj\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe p\u00ebr t\u00eb ilustruar k\u00ebt\u00eb, do t\u00eb shoh tabel\u00ebn Lockdemo. Dhe do t\u00eb shikojm\u00eb nj\u00eb rresht. P\u00ebr transaksionin 698.<\/p>\n

    <\/p>\n

    E kemi p\u00ebrdit\u00ebsuar n\u00eb 2. 699 \u2013 kjo \u00ebsht\u00eb p\u00ebrdit\u00ebsimi i par\u00eb. Dhe ai ka p\u00ebrfunduar me sukses ose \u00ebsht\u00eb n\u00eb nj\u00eb transaksion t\u00eb pritur dhe pret q\u00eb ne ta konfirmojm\u00eb ose ta anulojm\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Por shihni nj\u00eb tjet\u00ebr \u2013 2\/51 \u2013 kjo \u00ebsht\u00eb transaksioni yn\u00eb i par\u00eb, sesioni yn\u00eb i par\u00eb. 3\/112 \u2013 kjo \u00ebsht\u00eb k\u00ebrkesa e dyt\u00eb, q\u00eb ka dal\u00eb n\u00eb krye dhe ka ndryshuar k\u00ebt\u00eb vler\u00eb n\u00eb 3. Dhe n\u00ebse vini re, ai i sip\u00ebrm e ka bllokuar veten, q\u00eb \u00ebsht\u00eb 699. Por 3\/112 nuk ofroi nj\u00eb bllokim. N\u00eb kolon\u00ebn Lock_mode shkruhet se ai \u00ebsht\u00eb duke pritur. Ai po pret 699. Dhe n\u00ebse shikoni, ku \u00ebsht\u00eb 699, \u00ebsht\u00eb m\u00eb sip\u00ebr. Dhe \u00e7far\u00eb b\u00ebri sesioni i par\u00eb? Ai krijoi nj\u00eb bllokim ekskluziv n\u00eb 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 t\u00eb pritur. Prandaj mund t\u00eb shohim nj\u00eb rresht t\u00eb \u00e7uditsh\u00ebm. <\/p>\n

    <\/p>\n

    Le t\u00eb shohim p\u00ebrs\u00ebri. N\u00eb t\u00eb majt\u00eb 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, nd\u00ebrsa n\u00eb t\u00eb tret\u00ebn shohim lock_type. \u00c7far\u00eb do t\u00eb thot\u00eb kjo? N\u00eb thelb, ajo thot\u00eb se po bllokon ID-n\u00eb e transaksionit. Por vini re se n\u00eb t\u00eb gjitha rreshtat posht\u00eb shkruan relation. Dhe prandaj keni dy lloje bllokimesh n\u00eb tabel\u00eb. Ka bllokim relation. Po ashtu ka bllokim transactionid, ku ne bllokojm\u00eb vet\u00eb, kjo \u00ebsht\u00eb pik\u00ebrisht ajo q\u00eb ndodh n\u00eb rreshtin e par\u00eb ose n\u00eb fund, ku transactionid, ku presim q\u00eb 699 t\u00eb p\u00ebrfundoj\u00eb operacionin e tij. <\/p>\n

    <\/p>\n

    Po shikoj se \u00e7far\u00eb ndodh k\u00ebtu. Dhe k\u00ebtu ndodhin dy gj\u00ebra nj\u00ebkoh\u00ebsisht. Ju po shihni bllokimin sipas ID-s\u00eb s\u00eb transaksionit n\u00eb rreshtin e par\u00eb, q\u00eb bllokon veten. Dhe ajo bllokon veten p\u00ebr t\u00eb b\u00ebr\u00eb q\u00eb njer\u00ebzit t\u00eb presin. <\/p>\n

    <\/p>\n

    N\u00ebse shikoni rreshtin e 6-t\u00eb, \u00ebsht\u00eb e nj\u00ebjta sh\u00ebnim si e para. Dhe prandaj transaksioni 699 bllokohet. 700 gjithashtu bllokohet vet\u00eb. Dhe pastaj n\u00eb rreshtin e posht\u00ebm do t\u00eb shihni se po presim q\u00eb 699 t\u00eb p\u00ebrfundoj\u00eb operacionin e tij.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00eb lock_type, tuple shihni numrat. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Mund t\u00eb shihni se kjo \u00ebsht\u00eb 0\/10. Dhe kjo \u00ebsht\u00eb numri i faqes, dhe gjithashtu offset i k\u00ebtij rreshti konkret. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe shihni se b\u00ebhet 0\/11 kur e p\u00ebrdit\u00ebsojm\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Por n\u00eb t\u00eb v\u00ebrtet\u00eb \u2013 kjo \u00ebsht\u00eb 0\/10, sepse po ndodh presioni i k\u00ebtij operacioni. Ne kemi mund\u00ebsin\u00eb t\u00eb shohim se ky \u00ebsht\u00eb rreshti q\u00eb po pres p\u00ebr t\u00eb konfirmuar. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Sa her\u00eb q\u00eb e konfirmojm\u00eb dhe klikojm\u00eb commit, dhe kur p\u00ebrdit\u00ebsimi p\u00ebrfundon, kjo \u00ebsht\u00eb ajo q\u00eb marrim p\u00ebrs\u00ebri. Transaksioni 700 \u00ebsht\u00eb bllokimi i vet\u00ebm, ai nuk pret ask\u00ebnd tjet\u00ebr, sepse \u00ebsht\u00eb konfirmuar. Ai vet\u00ebm pret q\u00eb transaksioni t\u00eb p\u00ebrfundoj\u00eb. Sa her\u00eb q\u00eb 699 p\u00ebrfundon, ne nuk presim m\u00eb. Dhe tani transaksioni 700 thot\u00eb se gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull, t\u00eb gjitha bllokimet q\u00eb i duhen, i ka n\u00eb t\u00eb gjitha tabelat e lejuara. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe p\u00ebr ta komplikuar akoma m\u00eb shum\u00eb, ne krijojm\u00eb nj\u00eb tjet\u00ebr pamje, e cila k\u00ebt\u00eb her\u00eb do t\u00eb na ofroj\u00eb nj\u00eb hierarki. Nuk e pres q\u00eb ta kuptoni k\u00ebt\u00eb k\u00ebrkes\u00eb. Por kjo do t\u00eb na jap\u00eb nj\u00eb pamje m\u00eb t\u00eb qart\u00eb se \u00e7far\u00eb po ndodh. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kjo \u00ebsht\u00eb nj\u00eb pamje rekurzive, e cila ka gjithashtu nj\u00eb seksion tjet\u00ebr. Dhe ajo pastaj kthehet p\u00ebrs\u00ebri e gjitha. Le t\u00eb p\u00ebrdorim k\u00ebt\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    \u00c7far\u00eb, n\u00ebse b\u00ebjm\u00eb tri p\u00ebrdit\u00ebsime t\u00eb nj\u00ebkohshme dhe themi se rreshti tani \u00ebsht\u00eb i barabart\u00eb me tre. Dhe ne do ta ndryshojm\u00eb 3 n\u00eb 4. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe ja, shohim 4. Dhe ID e transaksionit 702. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe pastaj e ndryshoj 4 n\u00eb 5. Dhe 5 n\u00eb 6, dhe 6 n\u00eb 7. Dhe radhit njer\u00ebzit q\u00eb do t\u00eb presin deri sa kjo nj\u00eb transaksion t\u00eb p\u00ebrfundoj\u00eb.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/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 caktoi k\u00ebt\u00eb vler\u00eb. Por \u00e7far\u00eb kam shkruar n\u00eb kolon\u00ebn Granted? Kam sh\u00ebnime f<\/code>. K\u00ebto jan\u00eb p\u00ebrdit\u00ebsimet e mia, q\u00eb (5, 6, 7) nuk mund t\u00eb miratohen, sepse presim q\u00eb ID e transaksionit 702 t\u00eb p\u00ebrfundoj\u00eb. Aty kemi nj\u00eb bllokim t\u00eb ID e transaksionit. Dhe rezulton 5 bllokime t\u00eb IDs s\u00eb transaksionit.<\/p>\n

    <\/p>\n

    Dhe n\u00ebse shikoni 704, n\u00eb 705, aty nuk ka asgj\u00eb t\u00eb shkruar, sepse ata akoma nuk e din\u00eb se \u00e7far\u00eb po ndodh. Ata thjesht shkruajn\u00eb, q\u00eb nuk kan\u00eb asnj\u00eb ide se \u00e7far\u00eb po ndodh. Dhe ata thjesht do t\u00eb shkojn\u00eb n\u00eb gjum\u00eb, sepse presin q\u00eb dikush t\u00eb p\u00ebrfundoj\u00eb dhe t'i zgjoj\u00eb kur t\u00eb paraqitet mund\u00ebsia p\u00ebr t\u00eb ndryshuar rreshtin. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebshtu duket. E qart\u00eb, se ata t\u00eb gjith\u00eb presin rreshtin 12. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kjo \u00ebsht\u00eb ajo q\u00eb pam\u00eb k\u00ebtu. Ja, 0\/12. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe k\u00ebshtu, sa her\u00eb q\u00eb transaksioni i par\u00eb miratohet, mund t\u00eb shihni se si funksionon hierarkia. Dhe tani b\u00ebhet gjith\u00e7ka e qart\u00eb. T\u00eb gjith\u00eb b\u00ebhen t\u00eb past\u00ebr. Dhe ata n\u00eb t\u00eb v\u00ebrtet\u00eb ende jan\u00eb n\u00eb pritje. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebtu \u00ebsht\u00eb se \u00e7far\u00eb po ndodh. 702 po b\u00ebn komitim. Tani 703 merr k\u00ebt\u00eb bllokim rreshti, dhe pastaj 704 fillon t\u00eb pres\u00eb derisa 703 t\u00eb p\u00ebrfundoj\u00eb. 705 gjithashtu po pret k\u00ebt\u00eb. Dhe kur t\u00eb gjith\u00eb k\u00ebto p\u00ebrfundojn\u00eb, ata vet\u00eb pastrohen. D\u00ebshiroj t\u00eb theksoj se t\u00eb gjith\u00eb rreshtohen. Kjo \u00ebsht\u00eb shum\u00eb e ngjashme me nj\u00eb situat\u00eb ng\u00ebr\u00e7i, kur t\u00eb gjith\u00eb presin makin\u00ebn e par\u00eb. Makin\u00eb e par\u00eb ndalon, dhe t\u00eb gjith\u00eb rreshtohen n\u00eb nj\u00eb linj\u00eb t\u00eb gjat\u00eb. Pastaj ajo l\u00ebviz, pastaj makina tjet\u00ebr mund t\u00eb kaloj\u00eb p\u00ebrpara dhe t\u00eb marr\u00eb bllokimin e saj, etj. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00ebse ky duket si nj\u00eb problem i mjaftuesh\u00ebm, tani do t\u00eb flasim p\u00ebr ng\u00ebr\u00e7et. Nuk e di se sa nga ju kan\u00eb ardhur n\u00eb kontakt me to. Kjo \u00ebsht\u00eb nj\u00eb problem relativisht i zakonsh\u00ebm n\u00eb sistemet e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Por ng\u00ebr\u00e7et jan\u00eb rast kur nj\u00eb seanc\u00eb pret q\u00eb nj\u00eb seanc\u00eb tjet\u00ebr t\u00eb kryej\u00eb di\u00e7ka. Nd\u00ebrkoh\u00eb, seanca tjet\u00ebr pret q\u00eb seanca e par\u00eb t\u00eb kryej\u00eb di\u00e7ka. <\/p>\n

    <\/p>\n

    Dhe, p\u00ebr shembull, n\u00ebse Ivan thot\u00eb: \u00abM\u00eb jep di\u00e7ka\u00bb, dhe un\u00eb th\u00ebm: \u00abJo, do t\u00eb t\u00eb jap k\u00ebt\u00eb vet\u00ebm n\u00ebse ti m\u00eb jep di\u00e7ka tjet\u00ebr\u00bb. Ai thot\u00eb: \u00abJo, nuk do t\u00eb t\u00eb jap k\u00ebt\u00eb n\u00ebse ti nuk m\u00eb jep\u00bb. K\u00ebshtu krijohet nj\u00eb situat\u00eb ng\u00ebr\u00e7i. Jam i sigurt q\u00eb Ivan nuk do ta b\u00ebj\u00eb k\u00ebt\u00eb, por ju kuptoni kuptimin, q\u00eb kemi dy njer\u00ebz q\u00eb duan t\u00eb marrin di\u00e7ka dhe ata nuk jan\u00eb t\u00eb gatsh\u00ebm ta japin at\u00eb derisa personi tjet\u00ebr t'ua jap\u00eb at\u00eb q\u00eb ata duan. Dhe nuk ka zgjidhje. <\/p>\n

    <\/p>\n

    Dhe, n\u00eb thelb, 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\u00ebr\u00ebsi ato do t\u00eb mbeten atje p\u00ebrgjithmon\u00eb. 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

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe tani do t\u00eb vendosim dy ng\u00ebr\u00e7et. Ne 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

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe pastaj do t\u00eb ndryshoj 80 n\u00eb 81, dhe 50 n\u00eb 51. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    K\u00ebshtu do t\u00eb duket. Dhe prandaj 710 ka bllokimin e rreshtit, nd\u00ebrsa 711 pret konfirmimin. E pam\u00eb k\u00ebt\u00eb kur p\u00ebrdit\u00ebsuam. 710 \u00ebsht\u00eb pronari i rreshtit ton\u00eb. Dhe 711 pret q\u00eb 710 t\u00eb p\u00ebrfundoj\u00eb transaksionin.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe aty madje shkruhet se n\u00eb cilin rresht ndodhin ng\u00ebr\u00e7et. Dhe k\u00ebtu fillon t\u00eb b\u00ebhet e \u00e7uditshme.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Tani po p\u00ebrdit\u00ebsojm\u00eb 80 n\u00eb 80. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe k\u00ebtu fillon ng\u00ebr\u00e7i. 710 po pret nj\u00eb p\u00ebrgjigje nga 711, dhe 711 po pret nga 710. Dhe kjo nuk do t\u00eb p\u00ebrfundoj\u00eb mir\u00eb. Do t\u00eb presin p\u00ebrgjigje nga nj\u00ebri-tjetri. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe kjo do t\u00eb filloj\u00eb t\u00eb vonohet. Dhe nuk e duam k\u00ebt\u00eb. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00eb Postgres ka m\u00ebnyra p\u00ebr t\u00eb kuptuar kur kjo ndodh. Dhe kur ndodh, ju merrni nj\u00eb mesazh t\u00eb till\u00eb gabimi. Dhe \u00ebsht\u00eb e qart\u00eb se ky proces po pret nj\u00eb SHARE LOCK nga nj\u00eb proces tjet\u00ebr, q\u00eb \u00ebsht\u00eb bllokuar nga procesi 711. Ai proces priste q\u00eb t\u2019i jepej nj\u00eb SHARE LOCK n\u00eb nj\u00eb ID transaksioni t\u00eb caktuar dhe ishte bllokuar nga nj\u00eb proces i till\u00eb. Prandaj, kjo \u00ebsht\u00eb nj\u00eb situat\u00eb ng\u00ebr\u00e7i.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    A ndodhin ng\u00ebr\u00e7et trepal\u00ebsh? A \u00ebsht\u00eb e mundur kjo? Po. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ne i fusim k\u00ebto numra n\u00eb tabel\u00eb. Ne ndryshojm\u00eb 40 n\u00eb 40, ne b\u00ebjm\u00eb bllokimin. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ndryshojm\u00eb 60 n\u00eb 61, 80 n\u00eb 81. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe pastaj ne ndryshojm\u00eb 80, dhe pastaj - bum! <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe 714 tani po pret nga 715. 716-i po pret nga 715. Dhe me k\u00ebt\u00eb nuk ka \u00e7far\u00eb t\u00eb b\u00ebhet.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Tani nuk ka dy njer\u00ebz, por tre. Un\u00eb dua di\u00e7ka nga ti, ky 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 trepal\u00ebshe, 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

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe Postgres e di n\u00eb cilin rresht ndodh kjo. Prandaj do t'ju jap\u00eb nj\u00eb mesazh t\u00eb till\u00eb, q\u00eb tregon se keni nj\u00eb problem, ku tre procese po bllokojn\u00eb nj\u00ebri-tjetrin. Dhe k\u00ebtu nuk ka kufizime. Mund\u00ebsia \u00ebsht\u00eb q\u00eb 20 regjistrime t\u00eb bllokojn\u00eb nj\u00ebri-tjetrin. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Problemi tjet\u00ebr \u00ebsht\u00eb serializable. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00ebse bllokimi special \u00ebsht\u00eb serializable.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe kthehemi te 719. Ai ka nj\u00eb produkt t\u00eb plot\u00eb normal. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/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

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe ju kuptoni se tani keni nj\u00eb lloj tjet\u00ebr bllokimi SA - q\u00eb do t\u00eb thot\u00eb serializable. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Prandaj kemi nj\u00eb lloj t\u00eb ri bllokimi q\u00eb quhet SARieadLock, i cili \u00ebsht\u00eb nj\u00eb bllokim seri dhe lejon futjen e serive.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe gjithashtu mund t\u00eb futni indekse unike. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00eb k\u00ebt\u00eb tabel\u00eb kemi indekse unike.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Prandaj, n\u00ebse un\u00eb fus numrin 2 k\u00ebtu, un\u00eb kam 2. Por n\u00eb krye, un\u00eb fus nj\u00eb tjet\u00ebr 2. Dhe ju mund t\u00eb shihni q\u00eb 721 ka bllokim ekskluziv. Por tani 722 po pret q\u00eb 721 t\u00eb p\u00ebrfundoj\u00eb operacionin e tij, sepse ai nuk mund t\u00eb fus\u00eb 2 p\u00ebrderisa nuk e di se \u00e7far\u00eb do t\u00eb ndodh\u00eb me 721. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe n\u00ebse ne b\u00ebjm\u00eb subtransaction.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ja ku jemi me 723.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    N\u00ebse ne ruajm\u00eb nj\u00eb pik\u00eb dhe pastaj e p\u00ebrdit\u00ebsojm\u00eb, ne marrim nj\u00eb ID t\u00eb re transaksioni. Kjo \u00ebsht\u00eb nj\u00eb tjet\u00ebr karakteristik\u00eb q\u00eb duhet t\u00eb dini. N\u00ebse ne e kthejm\u00eb at\u00eb, ID e transaksionit largohet. 724 largohet. Por tani na del 725. <\/p>\n

    <\/p>\n

    \u00c7far\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: qofshin k\u00ebto bllokime serializues ose SAVEPOINT \u2013 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

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Ky \u00ebsht\u00eb krijimi i bllokimeve eksplicite (t\u00eb qarta) me pg_advisory_lock. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe ju shihni se tipi i bllokimit k\u00ebtu \u00ebsht\u00eb sh\u00ebnuar si advisory. Dhe k\u00ebtu \u00ebsht\u00eb shkruar me t\u00eb kuqe 'advisory'. Dhe ju mund ta bllokoni at\u00eb nj\u00ebkoh\u00ebsisht me pg_advisory_unlock.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe p\u00ebr t\u00eb p\u00ebrfunduar, do t\u00eb doja t'ju tregoj di\u00e7ka tjet\u00ebr t\u00eb mahnitshme. Do t\u00eb krijoj nj\u00eb lloj tjet\u00ebr. Por do ta lidh tabel\u00ebn pg_locks me tabel\u00ebn pg_stat_activity. Pse dua ta b\u00ebj k\u00ebt\u00eb? Sepse do t\u00eb m\u00eb lejoj\u00eb t\u00eb shoh dhe t\u00eb shikoj t\u00eb gjitha sesionet aktuale dhe t\u00eb shoh se \u00e7far\u00eb bllokimesh po presin. Dhe kjo \u00ebsht\u00eb mjaft interesante kur ne bashkojm\u00eb tabel\u00ebn e bllokimeve dhe tabel\u00ebn e k\u00ebrkesave. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe k\u00ebtu krijojm\u00eb pg_stat_view. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe ne e p\u00ebrdit\u00ebsojm\u00eb nj\u00eb rresht me nj\u00eb. Dhe k\u00ebtu ne shohim 724. Pastaj ne e p\u00ebrdit\u00ebsojm\u00eb rreshtin ton\u00eb n\u00eb tre. \u00c7far\u00eb shihni tani k\u00ebtu? K\u00ebto jan\u00eb k\u00ebrkesat, pra, ju shihni t\u00eb gjith\u00eb list\u00ebn e k\u00ebrkesave q\u00eb jan\u00eb t\u00eb renditura n\u00eb kolon\u00ebn e majt\u00eb. Pastaj n\u00eb an\u00ebn e djatht\u00eb ju 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 \u2013 a duhet t\u00eb bashkoheni me t\u00eb apo jo. Kjo e b\u00ebn p\u00ebr ne. <\/p>\n

    <\/p>\n

    Nj\u00eb tjet\u00ebr funksion 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 kjo? Ajo na lejon t\u00eb themi se p\u00ebr k\u00ebt\u00eb sesion 11740, cilat jan\u00eb ID-t\u00eb e proceseve q\u00eb po pret. Dhe ju mund t\u00eb shihni se 11740 pret 724. Dhe 724 \u00ebsht\u00eb n\u00eb krye. Nd\u00ebrsa 11306 \u00ebsht\u00eb ID i procesit tuaj. N\u00eb thelb, ky funksion kalon p\u00ebrmes tabel\u00ebs tuaj t\u00eb bllokimeve. E di se \u00ebsht\u00eb pak e komplikuar, por ju po e kuptoni. N\u00eb thelb, ky funksion kalon p\u00ebrmes k\u00ebsaj tabele bllokimesh dhe p\u00ebrpiqet t\u00eb gjej\u00eb se ku \u00ebsht\u00eb ky ID procesi, duke marr\u00eb parasysh ato bllokime q\u00eb po pret. Dhe gjithashtu p\u00ebrpiqet t\u00eb llogaris\u00eb se cili \u00ebsht\u00eb ID procesi, i atij procesi q\u00eb po pret bllokimin. Pra, ju mund ta ekzekutoni k\u00ebt\u00eb funksion pg_blocking_pids<\/code>.<\/p>\n

    <\/p>\n

    Dhe kjo \u00ebsht\u00eb shum\u00eb e dobishme. E kemi shtuar k\u00ebt\u00eb vet\u00ebm me versionin 9.6, pra, ky funksion \u00ebsht\u00eb vet\u00ebm pes\u00eb vjet i vjet\u00ebr, por \u00ebsht\u00eb shum\u00eb dhe shum\u00eb i dobish\u00ebm. Dhe kjo vlen edhe p\u00ebr k\u00ebrkes\u00ebn e dyt\u00eb. Ajo tregon pik\u00ebrisht at\u00eb q\u00eb na nevojitet t\u00eb shohim. <\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/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 e p\u00ebrdor\u00ebm gjith\u00eb koh\u00ebn ton\u00eb, sepse kishte kaq shum\u00eb slajde. Dhe slajdet jan\u00eb t\u00eb disponueshme p\u00ebr shkarkim. Do doja t'ju fal\u00ebnderoj p\u00ebr pranin\u00eb tuaj. Jam i sigurt se do t'ju p\u00eblqej\u00eb pjesa tjet\u00ebr e konferenc\u00ebs, faleminderit shum\u00eb!<\/p>\n

    <\/p>\n

    Pyetje:<\/p>\n

    <\/p>\n

    P\u00ebr shembull, n\u00ebse un\u00eb p\u00ebrpiqem t\u00eb p\u00ebrdit\u00ebsoj rreshta, nd\u00ebrsa nj\u00eb sesion tjet\u00ebr p\u00ebrpiqet t\u00eb fshij\u00eb t\u00eb gjith\u00eb tabel\u00ebn. Si\u00e7 e kuptoj, aty duhet t\u00eb jet\u00eb di\u00e7ka si nj\u00eb intent lock. A ekziston di\u00e7ka e till\u00eb n\u00eb Postgres?<\/em><\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Kthehemi n\u00eb fillim. Ndoshta e mbani mend se kur b\u00ebni ndonj\u00eb gj\u00eb, p\u00ebr shembull, kur b\u00ebni SELECT, ne l\u00ebshojm\u00eb AccessShareLock. Dhe kjo parandalon fshirjen e tabel\u00ebs. Pra, n\u00ebse ju, p\u00ebr shembull, doni t\u00eb p\u00ebrdit\u00ebsoni nj\u00eb rresht n\u00eb tabel\u00eb ose t\u00eb fshini nj\u00eb rresht, at\u00ebher\u00eb dikush nuk mund ta fshij\u00eb t\u00eb gjith\u00eb tabel\u00ebn nj\u00ebkoh\u00ebsisht me k\u00ebt\u00eb, sepse mbani k\u00ebt\u00eb AccessShareLock mbi t\u00eb gjith\u00eb tabel\u00ebn dhe mbi rreshtin. Dhe sa her\u00eb q\u00eb e p\u00ebrfundoni, ata mund ta fshij\u00eb. Por p\u00ebr sa koh\u00eb q\u00eb ju jeni duke b\u00ebr\u00eb di\u00e7ka atje, ata nuk do t\u00eb mund ta b\u00ebjn\u00eb.<\/p>\n

    <\/p>\n

    Le t\u00eb b\u00ebjm\u00eb edhe nj\u00eb her\u00eb. Le t\u00eb kalojm\u00eb n\u00eb shembullin e fshirjes. Dhe ju shihni se si ka nj\u00eb bllokim ekskluziv mbi t\u00eb gjith\u00eb tabel\u00ebn. <\/p>\n

    <\/p>\n

    Kjo do t\u00eb dukej si nj\u00eb bllokim ekskluziv, e drejt\u00eb?<\/em><\/p>\n

    <\/p>\n

    Po, po duket k\u00ebshtu. E kuptoj p\u00ebr \u00e7far\u00eb po flisni. Po thoni se, n\u00ebse un\u00eb b\u00ebj SELECT, do t\u00eb kem nj\u00eb ShareExclusive, dhe pastaj e kaloj k\u00ebt\u00eb n\u00eb nj\u00eb gjendje Row Exclusive, a b\u00ebhet kjo nj\u00eb problem? Por p\u00ebr habi, kjo nuk krijon nj\u00eb problem. Kjo duket si nj\u00eb rritje e shkall\u00ebs s\u00eb bllokimit, por, n\u00eb thelb, un\u00eb 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 ende parandalon fshirjen. Prandaj, nuk \u00ebsht\u00eb se po ngjitem lart. Pra, ai parandalonte edhe kur ishte n\u00eb nj\u00eb nivel m\u00eb t\u00eb ul\u00ebt, k\u00ebshtu q\u00eb, kur e rris nivelin e tij, ai ende parandalon fshirjen e tabel\u00ebs. <\/p>\n

    <\/p>\n

    E kuptoj se p\u00ebr \u00e7far\u00eb po flisni. Nuk ka asnj\u00eb rast t\u00eb rritjes s\u00eb nivelit t\u00eb bllokimit, ku p\u00ebrpiqeni t\u00eb refuzoni nj\u00eb bllokim p\u00ebr t\u00eb futur nj\u00eb m\u00eb t\u00eb fuqish\u00ebm. K\u00ebtu thjesht rritet ky parandalim, k\u00ebshtu q\u00eb nuk shkakton asnj\u00eb konflikt. Por \u00ebsht\u00eb nj\u00eb pyetje e mir\u00eb. Faleminderit q\u00eb e ngrit\u00ebt k\u00ebt\u00eb!<\/p>\n

    <\/p>\n

    \u00c7far\u00eb duhet t\u00eb b\u00ebjm\u00eb p\u00ebr t\u00eb shmangur situat\u00ebn e deadlock-ut, kur kemi shum\u00eb sesi, nj\u00eb num\u00ebr t\u00eb madh p\u00ebrdoruesish?<\/em><\/p>\n

    <\/p>\n

    Postgres e evidenton automatikisht situat\u00ebn e deadlock-ut. Dhe do t\u00eb eliminoj\u00eb automatikisht nj\u00eb nga sesionet. Nj\u00eb nga m\u00ebnyrat q\u00eb ndihmon t\u00eb shmanget situata e bllokimeve t\u00eb vdekura \u00ebsht\u00eb t\u00eb bllokoni njer\u00ebzit n\u00eb t\u00eb nj\u00ebjt\u00ebn renditje. Pra, kur shikoni aplikacionin tuaj, shpesh arsyeja e deadlocks... Le t\u00eb 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, e m\u00eb pas tabel\u00ebn 1. Dhe m\u00ebnyra m\u00eb e thjesht\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\u00ebjt\u00ebn renditje n\u00eb t\u00eb gjitha aplikacionet. Kjo, n\u00eb p\u00ebrgjith\u00ebsi, eliminon 80% t\u00eb problemeve, sepse njer\u00ebz t\u00eb ndrysh\u00ebm i shkruajn\u00eb k\u00ebto aplikacione. Dhe n\u00ebse i bllokoni n\u00eb t\u00eb nj\u00ebjt\u00ebn renditje, at\u00ebher\u00eb nuk p\u00ebrballeni me situat\u00ebn e deadlock-ut.<\/p>\n

    <\/p>\n

    Faleminderit shum\u00eb p\u00ebr fjalimin tuaj! Flisnit p\u00ebr vacuum full dhe, n\u00ebse e kuptoj mir\u00eb, vacuum full deformon rendin e regjistrimeve n\u00eb ruajtjen e ve\u00e7ant\u00eb, prandaj ruan regjistrimet aktuale t\u00eb pandryshuara. Pse vacuum full merr akses bllokues ekskluziv dhe pse ai konflikon me operacionet e shkrimit?<\/em><\/p>\n

    <\/p>\n

    \u00cbsht\u00eb nj\u00eb pyetje e mir\u00eb. Arsyeja \u00ebsht\u00eb se vacuum full merr tabel\u00ebn. Dhe n\u00eb thelb krijojm\u00eb nj\u00eb version t\u00eb ri t\u00eb tabel\u00ebs. Dhe tabela do t\u00eb jet\u00eb e re. Pra, do t\u00eb kemi nj\u00eb version krejt\u00ebsisht t\u00eb ri t\u00eb tabel\u00ebs. Problemi \u00ebsht\u00eb se, kur e b\u00ebjm\u00eb k\u00ebt\u00eb, nuk duam q\u00eb njer\u00ebzit ta lexojn\u00eb k\u00ebt\u00eb, sepse na duhet q\u00eb ata t\u00eb shohin tabel\u00ebn e re. Dhe prandaj kjo lidhet me pyetjen e m\u00ebparshme. N\u00ebse do t\u00eb mund t\u00eb lexohej nj\u00ebkoh\u00ebsisht, at\u00ebher\u00eb nuk do t\u00eb mund t\u00eb l\u00ebviznim dhe t\u00eb drejtonim njer\u00ebzit n\u00eb tabel\u00ebn e re. Do t\u00eb duhej t\u00eb prisnim q\u00eb secili t\u00eb p\u00ebrfundonte t\u00eb lexonte k\u00ebt\u00eb tabel\u00eb, dhe prandaj, n\u00eb thelb, kjo \u00ebsht\u00eb nj\u00eb situat\u00eb bllokimi ekskluziv.
    \nThjesht po themi se bllokojm\u00eb qysh n\u00eb fillim, sepse e dim\u00eb se n\u00eb fund do t\u00eb na nevojitet nj\u00eb bllokim ekskluziv p\u00ebr t\u00eb l\u00ebvizur t\u00eb gjith\u00eb n\u00eb nj\u00eb kopje t\u00eb re. Pra, potencialisht mund ta zgjidhim k\u00ebt\u00eb. Dhe ne e b\u00ebjm\u00eb k\u00ebt\u00eb me indeksim paralel. Por kjo \u00ebsht\u00eb shum\u00eb m\u00eb e komplikuar p\u00ebr t'u realizuar. Dhe kjo lidhet shum\u00eb me pyetjen tuaj t\u00eb m\u00ebparshme mbi bllokimin ekskluziv.<\/p>\n

    <\/p>\n

    A \u00ebsht\u00eb e mundur t\u00eb shtoni nj\u00eb koh\u00eb skadimi p\u00ebr bllokimin n\u00eb Postgres? N\u00eb Oracle mund t\u00eb shkruaj, p\u00ebr shembull, 'zgjedh p\u00ebr t\u00eb p\u00ebrdit\u00ebsuar' dhe t\u00eb pres 50 sekonda p\u00ebr p\u00ebrdit\u00ebsimin. Kjo ishte e mir\u00eb p\u00ebr aplikacionin. Por n\u00eb Postgres un\u00eb ose duhet ta b\u00ebj menj\u00ebher\u00eb dhe t\u00eb mos pres fare, ose t\u00eb pres deri n\u00eb nj\u00eb moment t\u00eb caktuar.<\/em> <\/p>\n

    <\/p>\n

    Po, mund t\u00eb zgjidhni nj\u00eb koh\u00eb skadimi p\u00ebr bllokimet tuaja. Gjithashtu, mund t\u00eb jepni urdh\u00ebrin no way, i cili do t\u00eb... n\u00ebse nuk mund t\u00eb merrni menj\u00ebher\u00eb bllokimin. Pra, ose koh\u00eb skadimi bllokimi, ose di\u00e7ka tjet\u00ebr q\u00eb do t'ju lejoj\u00eb ta b\u00ebni k\u00ebt\u00eb. Kjo nuk b\u00ebhet n\u00eb nivelin sintaksor. Kjo b\u00ebhet si nj\u00eb variab\u00ebl n\u00eb server. Disa her\u00eb kjo nuk mund t\u00eb p\u00ebrdoret.<\/p>\n

    <\/p>\n

    Mund t\u00eb hapni sliden 75?<\/em> <\/p>\n

    <\/p>\n

    Po.<\/p>\n

    <\/p>\n

    \"\u00c7lirimi<\/p>\n

    <\/p>\n

    Dhe pyetja ime \u00ebsht\u00eb kjo. Pse t\u00eb dy proceset e p\u00ebrdit\u00ebsimit presin 703?<\/em><\/p>\n

    <\/p>\n

    Dhe kjo \u00ebsht\u00eb nj\u00eb pyetje e shk\u00eblqyer. Nuk e kuptoj, p\u00ebrsa i p\u00ebrket, pse Postgres e b\u00ebn k\u00ebt\u00eb. Por kur 703 u krijua, ai priste 702. Dhe kur 704 dhe 705 shfaqen, duket se ata nuk e din\u00eb se \u00e7far\u00eb po presin, sepse atje nuk ka ende asgj\u00eb. Dhe Postgres e b\u00ebn k\u00ebt\u00eb: kur nuk mund t\u00eb merrni bllokimin, ai shkruan 'Cila \u00ebsht\u00eb pika t\u00eb vazhdoni me ju?', sepse ju tashm\u00eb po prisni dik\u00eb. Prandaj, thjesht le t\u00eb mbes\u00eb n\u00eb pritje, ai nuk e p\u00ebrdit\u00ebson fare. Por \u00e7far\u00eb ndodhi k\u00ebtu? Sapo 702 p\u00ebrfundoi procesin dhe 703 mori bllokimin e tij, sistemi u kthye mbrapa. Dhe tha tani kemi dy njer\u00ebz q\u00eb jan\u00eb n\u00eb pritje. Dhe tani le t'i p\u00ebrdit\u00ebsojm\u00eb ata s\u00eb bashku. <\/p>\n

    <\/p>\n

    Nuk di, pse Postgres e b\u00ebn\u00eb k\u00ebshtu. Por ka nj\u00eb problem q\u00eb quhet f\u2026. M\u00eb duket se nuk \u00ebsht\u00eb nj\u00eb term n\u00eb rusisht. Kjo \u00ebsht\u00eb kur t\u00eb gjith\u00eb presin nj\u00eb bllokim, edhe pse ka 20 instanca q\u00eb presin bllokimin. Dhe papritmas ata t\u00eb gjith\u00eb zgjojn\u00eb nj\u00ebher\u00ebsh. Dhe t\u00eb gjith\u00eb fillojn\u00eb t\u00eb p\u00ebrpiqen t\u00eb reagojn\u00eb. Por sistemi e b\u00ebn q\u00eb t\u00eb gjith\u00eb t\u00eb presin 703. Sepse ata t\u00eb gjith\u00eb presin, dhe ne do t\u2019i rendisim menj\u00ebher\u00eb n\u00eb radh\u00eb. Dhe n\u00ebse ndonj\u00eb k\u00ebrkes\u00eb e re shfaqet, e formuar pas k\u00ebsaj, p\u00ebr shembull, 707, atje s\u00ebrish do t\u00eb ket\u00eb boshll\u00ebk. <\/p>\n

    <\/p>\n

    M\u00eb duket se kjo b\u00ebhet p\u00ebr t\u00eb th\u00ebn\u00eb se n\u00eb k\u00ebt\u00eb faz\u00eb 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 sh\u00ebnim n\u00eb k\u00ebt\u00eb fush\u00eb. Por sa her\u00eb q\u00eb ai q\u00eb pret i largohet, 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 p\u00ebr t\u00eb mund\u00ebsuar q\u00eb t\u00eb trajtojm\u00eb n\u00eb rend, q\u00eb ata t\u00eb jen\u00eb n\u00eb rend t\u00eb duhur. <\/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, ne nuk i rendisim fare. Por, m\u00eb duket se \u00e7do her\u00eb q\u00eb ne japim nj\u00eb bllokim t\u00eb ri, ne shohim t\u00eb gjith\u00eb ata q\u00eb jan\u00eb n\u00eb procesin e pritjes. At\u00ebher\u00eb i rendisim t\u00eb gjith\u00eb n\u00eb radh\u00eb. Dhe \u00e7do i ri q\u00eb vjen, bie n\u00eb radh\u00eb vet\u00ebm at\u00ebher\u00eb kur personi tjet\u00ebr p\u00ebrfundon procesin. 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 si vijon. Teknikisht ju mund t\u00eb zgjoni at\u00eb, ose k\u00ebt\u00eb. Dhe prandaj ne do t\u00eb zgjojm\u00eb at\u00eb ose k\u00ebt\u00eb. Por \u00e7far\u00eb ndodh n\u00eb funksionimin e sistemit? Ju shihni se si 703 n\u00eb krye bllokoi 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 pas p\u00ebrfundimit t\u00eb tij, ndonj\u00eb nga proceset zgjohet. Dhe ne nuk e dim\u00eb se cili proces do t\u00eb jet\u00eb. Pastaj ne e trajtojm\u00eb gradualisht t\u00eb gjithin. Por nuk \u00ebsht\u00eb e qart\u00eb se cili proces zgjohet i pari, sepse mund t\u00eb jet\u00eb \u00e7far\u00ebdo nga k\u00ebta procese. N\u00eb thelb, kishim nj\u00eb planifikues q\u00eb tha se tani mund t\u00eb zgjojm\u00eb \u00e7far\u00ebdo nga k\u00ebta procese. Thjesht zgjedhim nj\u00eb rast\u00ebsisht. Prandaj t\u00eb dy duhet t\u00eb merren parasysh, sepse ne mund t\u00eb zgjojm\u00eb \u00e7far\u00ebdo nga ata. <\/p>\n

    <\/p>\n

    Dhe problemi \u00ebsht\u00eb q\u00eb kemi CP-p\u00ebrjet\u00ebsi. Prandaj \u00ebsht\u00eb shum\u00eb e mundur q\u00eb t\u00eb zgjojm\u00eb m\u00eb von\u00eb. Dhe n\u00ebse, p\u00ebr shembull, ne do t\u00eb zgjojm\u00eb m\u00eb von\u00eb, at\u00ebher\u00eb do t\u00eb presim at\u00eb q\u00eb sapo mori bllokimin, prandaj nuk e p\u00ebrcaktojm\u00eb se kush do t\u00eb zgjohet i pari. Ne thjesht krijojm\u00eb nj\u00eb situat\u00eb t\u00eb till\u00eb, dhe sistemi do t\u2019i zgjoj\u00eb ata n\u00eb m\u00ebnyr\u00eb t\u00eb rast\u00ebsishme. <\/p>\n

    <\/p>\n

    Ka artikuj p\u00ebr locks Eora Rogova<\/a><\/noindex>. Shikoni, ato jan\u00eb gjithashtu interesante dhe t\u00eb dobishme. Tema, sigurisht, \u00ebsht\u00eb shum\u00eb e komplikuar. Faleminderit shum\u00eb, Bruce!<\/em><\/p>\n

    Burimi: 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\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47\u00c7lirimi i Menaxherit t\u00eb Bllokimeve Postgres. Bruce Momjian | ProHoster","description":"Dekodimi i raportit t\u00eb vitit 2020 nga Bruce Momjian \"Zhbllokimi i Menaxherit t\u00eb Nd\u00ebrtimeve Postgres\".","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47Unlocking the Postgres Lock Manager. \u0411\u0440\u044e\u0441 \u041c\u043e\u043c\u0436\u0438\u0430\u043d | ProHoster","og:description":"\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".","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-11T23:42:48+00:00","article:modified_time":"2020-07-11T23:42:48+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"87954","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:40:34","updated":"2022-09-27 23:56:25","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/87954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=87954"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/87954\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/87955"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=87954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=87954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=87954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}