{"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\/sk\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","title":{"rendered":"Odomknutie Postgres Lock Managera. Bruce Momjian","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Dek\u00f3dovanie spr\u00e1vy Brucea Momjiana z roku 2020 \"Odomknutie spr\u00e1vcu z\u00e1mkov Postgres\".<\/p>\n

<\/p>\n

\"Odomknutie<\/p>\n

<\/p>\n

(Pozn\u00e1mka: V\u0161etky SQL pr\u00edkazy zo sn\u00edmok si m\u00f4\u017eete stiahnu\u0165 na tomto odkaze: http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex>)<\/p>\n

<\/p>\n

Ahojte! Je skvel\u00e9 by\u0165 op\u00e4\u0165 tu v Rusku. Ospravedl\u0148ujem sa, \u017ee som sa nemohol pr\u00eds\u0165 minul\u00fd rok, ale tento rok m\u00e1me s Ivanom ve\u013ek\u00e9 pl\u00e1ny. D\u00fafam, \u017ee budem tu ove\u013ea \u010dastej\u0161ie. Milujem prich\u00e1dza\u0165 do Ruska. Nav\u0161t\u00edvim Tjume\u0148 a Tver. Ve\u013emi sa te\u0161\u00edm, \u017ee sa mi podar\u00ed nav\u0161t\u00edvi\u0165 tieto mest\u00e1. <\/p>\n

<\/p>\n

Vol\u00e1m sa Bruce Momjian. Pracujem v EnterpriseDB a pracujem s Postgresom viac ako 23 rokov. \u017dijem vo Philadelphii v USA. Cestujem pribli\u017ene 90 dn\u00ed v roku a nav\u0161tevujem asi 40 konferenci\u00ed. M\u00f4j webstr\u00e1nka<\/a><\/noindex>, ktor\u00e1 obsahuje sn\u00edmky, ktor\u00e9 v\u00e1m teraz uk\u00e1\u017eem. Preto si ich po konferencii m\u00f4\u017eete stiahnu\u0165 z mojej osobnej str\u00e1nky. Obsahuje tie\u017e pribli\u017ene 30 prezent\u00e1ci\u00ed, vide\u00e1 a mno\u017estvo blogov\u00fdch pr\u00edspevkov, viac ako 500. Je to pomerne obsahov\u00fd zdroj. A ak v\u00e1s tento materi\u00e1l zauj\u00edma, poz\u00fdvam v\u00e1s, aby ste ho vyu\u017eili. <\/p>\n

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

Predt\u00fdm som bol u\u010dite\u013eom, profesorom, ne\u017e som za\u010dal pracova\u0165 s Postgresom. A som ve\u013emi r\u00e1d, \u017ee v\u00e1m teraz m\u00f4\u017eem poveda\u0165 to, \u010do v\u00e1m chcem poveda\u0165. Toto je jedna z najzauj\u00edmavej\u0161\u00edch mojich prezent\u00e1ci\u00ed a t\u00e1to prezent\u00e1cia obsahuje 110 sn\u00edmok. Za\u010dneme s jednoduch\u00fdmi vecami a na konci bude prezent\u00e1cia \u010doraz zlo\u017eitej\u0161ia a zlo\u017eitej\u0161ia.<\/p>\n

<\/p>\n

\"Odomknutie<\/p>\n

<\/p>\n

Je to dos\u0165 nepr\u00edjemn\u00e1 diskusia. Blokovanie nie je najob\u013e\u00fabenej\u0161\u00ed predmet. Chceme, aby to niekam zmizlo. Je to ako \u00eds\u0165 k zub\u00e1rovi. <\/p>\n

<\/p>\n

\"Odomknutie<\/p>\n

<\/p>\n

    \n
  1. Blokovanie je probl\u00e9m pre mnoh\u00fdch \u013eud\u00ed, ktor\u00ed pracuj\u00fa s datab\u00e1zami a maj\u00fa viacero procesov be\u017eiacich s\u00fa\u010dasne. Potrebuj\u00fa blokovanie. To znamen\u00e1, \u017ee dnes v\u00e1m poskytnem z\u00e1kladn\u00e9 znalosti o blokovan\u00ed. <\/li>\n
  2. Identifik\u00e1tory transakci\u00ed. Toto je pomerne nudn\u00e1 \u010das\u0165 prezent\u00e1cie, ale je potrebn\u00e9 ich pochopi\u0165.<\/li>\n
  3. Potom budeme hovori\u0165 o typoch blokovania. Toto je dos\u0165 mechanick\u00e1 \u010das\u0165. <\/li>\n
  4. A nesk\u00f4r prinesieme niektor\u00e9 pr\u00edklady blokovania. A to bude dos\u0165 \u0165a\u017ek\u00e9 na pochopenie. <\/li>\n<\/ol>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Po\u010fme hovori\u0165 o blokovaniach.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Terminol\u00f3gia je u n\u00e1s dos\u0165 zlo\u017eit\u00e1. Ko\u013eko z v\u00e1s vie, odkia\u013e je tento \u00faryvok? Dvaja \u013eudia. Je to z hry, ktor\u00e1 sa vol\u00e1 \u201eKolos\u00e1lne dobrodru\u017estvo v jaskyni\u201c. Bola to textov\u00e1 po\u010d\u00edta\u010dov\u00e1 hra v 80-tych rokoch, mysl\u00edm. Museli ste vojs\u0165 do jaskyne, do labyrintu a text sa menil, ale obsah zost\u00e1val pribli\u017ene rovnak\u00fd ka\u017ed\u00fdkr\u00e1t. Takto si pam\u00e4t\u00e1m t\u00fato hru. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A tu vid\u00edme n\u00e1zvy blok\u00e1d, ktor\u00e9 k n\u00e1m pri\u0161li z Oracle. Pou\u017e\u00edvame ich. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu vid\u00edme term\u00edny, ktor\u00e9 ma m\u00fdlia. Napr\u00edklad, SHARE UPDATE EXCLUSIVE. \u010ealej SHARE RAW EXCLUSIVE. \u00daprimne povedan\u00e9, tieto n\u00e1zvy nie s\u00fa ve\u013emi zrozumite\u013en\u00e9. Pok\u00fasime sa ich presk\u00fama\u0165 podrobnej\u0161ie. Niektor\u00e9 obsahuj\u00fa slovo \u201eshare\u201c, ktor\u00e9 znamen\u00e1 \u2013 oddeli\u0165 sa. Niektor\u00e9 obsahuj\u00fa slovo \u201eexclusive\u201c \u2014 exkluz\u00edvny. V niektor\u00fdch s\u00fa oba tieto v\u00fdrazy. Chcel by som za\u010da\u0165 t\u00fdm, ako tieto blok\u00e1dy funguj\u00fa. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A takisto je ve\u013emi d\u00f4le\u017eit\u00e9 slovo \u201epr\u00edstup\u201c \u2014 access. A slovo \u201erow\u201c \u2014 riadok. T. j. rozdelenie pr\u00edstupu, rozdelenie riadkov. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    E\u0161te jedna ot\u00e1zka, ktor\u00fa je potrebn\u00e9 pochopi\u0165 v Postgres, o ktorej, \u017eia\u013e, nebudem m\u00f4c\u0165 hovori\u0165 vo svojom vyst\u00fapen\u00ed, je MVCC. M\u00e1m na t\u00fato t\u00e9mu samostatn\u00fa prezent\u00e1ciu na svojej webovej str\u00e1nke. A ak si mysl\u00edte, \u017ee t\u00e1to prezent\u00e1cia je zlo\u017eit\u00e1, tak MVCC je pravdepodobne moja najzlo\u017eitej\u0161ia. A ak v\u00e1s to zauj\u00edma, m\u00f4\u017eete si ju pozrie\u0165 na webe. M\u00f4\u017eete si pozrie\u0165 video. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    E\u0161te jeden bod, ktor\u00fd mus\u00edme pochopi\u0165 \u2013 s\u00fa identifik\u00e1tory transakci\u00ed. Mnoh\u00e9 transakcie nem\u00f4\u017eu fungova\u0165 bez jedine\u010dn\u00fdch identifik\u00e1torov. A tu m\u00e1me vysvetlenie toho, \u010do je transakcia. V Postgres s\u00fa dva syst\u00e9my \u010d\u00edslovania transakci\u00ed. Viem, \u017ee to nie je ve\u013emi pekn\u00e9 rie\u0161enie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Takisto si pam\u00e4tajte, \u017ee sn\u00edmky bud\u00fa dos\u0165 zlo\u017eit\u00e9 na pochopenie, tak\u017ee na to, \u010do je zv\u00fdraznen\u00e9 \u010dervenou farbou, by sa mala venova\u0165 zv\u00fd\u0161en\u00e1 pozornos\u0165.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

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

    <\/p>\n

    Pozrime sa. \u010cervenou farbou je zv\u00fdraznen\u00e9 \u010d\u00edslo transakcie. Tu je zobrazen\u00e1 funkcia SELECT pg_back. Vracia moju transakciu a ID tejto transakcie. <\/p>\n

    <\/p>\n

    E\u0161te jedna vec, ak sa v\u00e1m t\u00e1to prezent\u00e1cia p\u00e1\u010di a chcete ju spusti\u0165 vo svojej datab\u00e1ze, m\u00f4\u017eete prejs\u0165 na tento odkaz ozna\u010den\u00fd ru\u017eovou farbou a stiahnu\u0165 SQL pre t\u00fato prezent\u00e1ciu. A m\u00f4\u017eete ju jednoducho spusti\u0165 vo va\u0161om PSQL a cel\u00e1 prezent\u00e1cia sa objav\u00ed na va\u0161ej obrazovke okam\u017eite. Nebude obsahova\u0165 farby, ale aspo\u0148 ju budeme m\u00f4c\u0165 vidie\u0165.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    V tomto pr\u00edpade vid\u00edme ID transakcie. To je \u010d\u00edslo, ktor\u00e9 sme mu priradili. A existuje aj in\u00fd typ ID transakcie v Postgres, ktor\u00fd sa naz\u00fdva virtu\u00e1lne ID transakcie.<\/p>\n

    <\/p>\n

    A mus\u00edme to pochopi\u0165. Je to ve\u013emi d\u00f4le\u017eit\u00e9, inak nebudeme schopn\u00ed porozumie\u0165 blokovaniu v Postgres.<\/p>\n

    <\/p>\n

    Virtu\u00e1lne ID transakcie je ID transakcie, ktor\u00e9 neobsahuje trval\u00e9 hodnoty. Napr\u00edklad, ak spust\u00edm pr\u00edkaz SELECT, pravdepodobne nebudem meni\u0165 datab\u00e1zu, ni\u010d nebudem blokova\u0165. Preto, ke\u010f sp\u00fa\u0161\u0165ame jednoduch\u00fd SELECT, ned\u00e1vame tejto transakcii trval\u00e9 ID. D\u00e1vame jej len virtu\u00e1lne ID.<\/p>\n

    <\/p>\n

    A to zvy\u0161uje v\u00fdkon Postgres, zlep\u0161uje mo\u017enosti \u010distenia, preto virtu\u00e1lne ID transakcie pozost\u00e1va z dvoch \u010d\u00edsel. Prv\u00e9 \u010d\u00edslo pred lomkou je ID backendu. A napravo vid\u00edme jednoducho \u010d\u00edta\u010d.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Preto ak sp\u00fa\u0161\u0165am dotaz, hovor\u00ed, \u017ee ID backendu je 2. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A ak sp\u00fa\u0161\u0165am s\u00e9riu tak\u00fdchto transakci\u00ed, vid\u00edme, \u017ee \u010d\u00edta\u010d sa zaka\u017ed\u00fdm zvy\u0161uje, ke\u010f sp\u00fa\u0161\u0165am dotaz. Napr\u00edklad, ke\u010f sp\u00fa\u0161\u0165am dotaz 2\/10, 2\/11, 2\/12 a tak \u010falej. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Majte na pam\u00e4ti, \u017ee tu s\u00fa dve st\u013apce. V\u013eavo vid\u00edme virtu\u00e1lne ID transakcie \u2013 2\/12. A napravo m\u00e1me trval\u00e9 ID transakcie. A toto pole je pr\u00e1zdne. A t\u00e1to transakcia nemodifikuje datab\u00e1zu. Preto jej neprira\u010fujem trval\u00e9 ID transakcie.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Hne\u010f ako spust\u00edm pr\u00edkaz analyzova\u0165 (ANALYZE), rovnak\u00fd dotaz mi d\u00e1 trval\u00e9 ID transakcie. Pozrite sa, ako sa to zmenilo. Predt\u00fdm som nemal toto ID, teraz sa objavilo.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee tu je \u010fal\u0161\u00ed dotaz, \u010fal\u0161ia transakcia. Virtu\u00e1lne \u010d\u00edslo transakcie \u2013 2\/13. A ak po\u017eiadam o trval\u00e9 ID transakcie, tak ke\u010f spust\u00edm dotaz, z\u00edskam ho. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee e\u0161te raz. M\u00e1me virtu\u00e1lne ID transakcie a trval\u00e9 ID transakcie. Len pochopte t\u00fato vec, aby ste mohli pochopi\u0165 spr\u00e1vanie Postgres.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Prejdeme k tretej sekcii. Tu si prejdeme r\u00f4zne typy z\u00e1mkov v Postgres. Nie je to ve\u013emi zauj\u00edmav\u00e9. Posledn\u00e1 sekcia bude ove\u013ea zauj\u00edmavej\u0161ia. Ale mus\u00edme sa pozrie\u0165 na z\u00e1kladn\u00e9 veci, inak nepochop\u00edme, \u010do bude \u010falej. <\/p>\n

    <\/p>\n

    Prejdeme touto sekciou a pozrieme sa na ka\u017ed\u00fd typ z\u00e1mku. Uk\u00e1\u017eem v\u00e1m pr\u00edklady, ako sa nastavuj\u00fa, ako funguj\u00fa a predvediem niektor\u00e9 dotazy, ktor\u00e9 m\u00f4\u017eete pou\u017ei\u0165 na zobrazenie z\u00e1mkov v Postgres.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Aby sme vytvorili dotaz a zistili, \u010do sa deje v Postgres, mus\u00edme spusti\u0165 dotaz v syst\u00e9movom zobrazen\u00ed. V tomto pr\u00edpade je \u010dervenou farbou vyzna\u010den\u00fd pg_lock. Pg_lock je syst\u00e9mov\u00e1 tabu\u013eka, ktor\u00e1 n\u00e1m hovor\u00ed, ak\u00e9 z\u00e1mky s\u00fa moment\u00e1lne pou\u017e\u00edvan\u00e9 v Postgres.<\/p>\n

    <\/p>\n

    Napriek tomu je pre m\u0148a ve\u013emi \u0165a\u017ek\u00e9 uk\u00e1za\u0165 v\u00e1m pg_lock samotn\u00fd, preto\u017ee je to dos\u0165 zlo\u017eit\u00e9. Preto som vytvoril zobrazenie, ktor\u00e9 ukazuje pg_locks. A tie\u017e vykon\u00e1va pre m\u0148a niektor\u00e9 \u00falohy, ktor\u00e9 mi umo\u017e\u0148uj\u00fa lep\u0161ie porozumie\u0165. To znamen\u00e1, \u017ee vylu\u010duje moje z\u00e1mky, moju vlastn\u00fa rel\u00e1ciu at\u010f. Je to len \u0161tandardn\u00fd SQL a umo\u017e\u0148uje mi lep\u0161ie uk\u00e1za\u0165, \u010do sa deje. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \u010eal\u0161\u00edm probl\u00e9mom je, \u017ee toto zobrazenie je ve\u013emi \u0161irok\u00e9, a preto mus\u00edm vytvori\u0165 druh\u00e9 \u2013 lockview2.<\/p>\n

    <\/p>\n

    \"Odomknutie A ukazuje mi \u010fal\u0161ie st\u013apce z tabu\u013eky. A e\u0161te jeden, ktor\u00fd mi ukazuje ostatn\u00e9 st\u013apce. Je to dos\u0165 zlo\u017eit\u00e9, tak\u017ee som sa sna\u017eil to predstavi\u0165 \u010do najjednoduch\u0161ie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee sme vytvorili tabu\u013eku s n\u00e1zvom Lockdemo. A vytvorili sme tam jeden riadok. Toto je na\u0161a vzorov\u00e1 tabu\u013eka. A budeme vytv\u00e1ra\u0165 sekcie, aby sme v\u00e1m jednoducho uk\u00e1zali pr\u00edklady z\u00e1mkov. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee jeden riadok, jeden st\u013apec. Prv\u00fd typ z\u00e1mku sa vol\u00e1 ACCESS SHARE. Je to najmenej obmedzuj\u00faci z\u00e1mok. To znamen\u00e1, \u017ee prakticky nekoliduje s ostatn\u00fdmi z\u00e1mkami.<\/p>\n

    <\/p>\n

    A ak chceme v\u00fdslovne definova\u0165 blokovanie, spust\u00edme pr\u00edkaz \u201elock table\u201c. To jasne zamkne, teda v re\u017eime ACCESS SHARE sp\u00fa\u0161\u0165ame lock table. A ak spust\u00edm PSQL na pozad\u00ed, tak m\u00e1m druh\u00fa rel\u00e1ciu z mojej prvej rel\u00e1cie. Teda, \u010do tu sprav\u00edm? Prejdem k inej rel\u00e1cii a hovor\u00edm jej \u201euk\u00e1\u017e mi lockview pre tento dotaz\u201c. A tu m\u00e1m AccessShareLock v tejto tabu\u013eke. To je presne to, \u010do som po\u017eadoval. A hovor\u00ed, \u017ee blokovanie bolo priraden\u00e9. Ve\u013emi jednoduch\u00e9. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \u010ealej, ak sa pozrieme na druh\u00fd st\u013apec, tak tam ni\u010d nie je. S\u00fa pr\u00e1zdne. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A ak spust\u00edm pr\u00edkaz \u201eSELECT\u201c, tak je to implicitn\u00fd (v\u00fdslovn\u00fd) sp\u00f4sob, ako po\u017eiada\u0165 o AccessShareLock. Preto uvo\u013en\u00edm svoju tabu\u013eku a spust\u00edm dotaz, a dotaz vr\u00e1ti nieko\u013eko riadkov. A v jednom z riadkov vid\u00edme AccessShareLock. Takto SELECT vyvol\u00e1va AccessShareLock v tabu\u013eke. A prakticky nezmieta s ni\u010d\u00edm, preto\u017ee je to n\u00edzke blokovanie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \u010co ak spust\u00edm SELECT a budem ma\u0165 tri r\u00f4zne tabu\u013eky? Predt\u00fdm som sp\u00fa\u0161\u0165al len jednu tabu\u013eku, teraz sp\u00fa\u0161\u0165am tri: pg_class, pg_namespace a pg_attribute.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A teraz, ke\u010f sa pozriem na dotaz, vid\u00edm 9 AccessShareLocks v troch tabu\u013ek\u00e1ch. Pre\u010do? Modro s\u00fa zv\u00fdraznen\u00e9 tri tabu\u013eky: pg_attribute, pg_class, pg_namespace. Ale tie\u017e m\u00f4\u017eete vidie\u0165, \u017ee v\u0161etky indexy, ktor\u00e9 s\u00fa definovan\u00e9 cez tieto tabu\u013eky, maj\u00fa tie\u017e AccessShareLock.<\/p>\n

    <\/p>\n

    A toto je blokovanie, ktor\u00e9 prakticky nekonfliktuje s in\u00fdmi. A v\u0161etko, \u010do rob\u00ed, je, \u017ee n\u00e1m jednoducho nedovo\u013euje uvo\u013eni\u0165 tabu\u013eku, k\u00fdm ju vyber\u00e1me. To d\u00e1va zmysel. Teda, ak vyber\u00e1me tabu\u013eku, t\u00e1to okam\u017eite nezmizne, to by bolo nespr\u00e1vne, preto\u017ee AccessShare je n\u00edzkolevelov\u00e1 z\u00e1mka, ktor\u00e1 n\u00e1m hovor\u00ed \"neodstra\u0148ujte t\u00fato tabu\u013eku, k\u00fdm pracujem\".<\/strong>. To je v podstate v\u0161etko, \u010do rob\u00ed. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    ROW SHARE \u2013 to je blokovanie, ktor\u00e9 je trochu odli\u0161n\u00e9. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Zoberme pr\u00edklad. SELECT ROW SHARE sp\u00f4sob blokovania ka\u017edej jednotliv\u00e9 riadku<\/strong>. Tak\u017ee nikto ich nem\u00f4\u017ee vymaza\u0165 alebo zmeni\u0165, k\u00fdm ich sledujeme. <\/p>\n

    <\/p>\n

    \"OdomknutieTak\u017ee, \u010do rob\u00ed SHARE LOCK? Vid\u00edme, \u017ee ID transakcie 681 pre SELECT. A to je zauj\u00edmav\u00e9. \u010co sa tu stalo? Po prv\u00fd raz vid\u00edme \u010d\u00edslo v poli \u201eLock\u201c. Berieme ID transakcie, a hovor\u00ed, \u017ee ju uzamkne v exkluz\u00edvnom re\u017eime. V\u0161etko, \u010do rob\u00ed, je, \u017ee hovor\u00ed, \u017ee m\u00e1m riadok, ktor\u00fd je technicky uzamknut\u00fd niekde v tabu\u013eke. Ale nehovor\u00ed kde konkr\u00e9tne. O nie\u010do nesk\u00f4r sa na to podrobnej\u0161ie pozrieme.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu hovor\u00edme, \u017ee uzamknutie pou\u017e\u00edvame my.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee exkluz\u00edvne uzamknutie explicitne (jasne) hovor\u00ed, \u017ee je exkluz\u00edvne. A ak odstr\u00e1nite riadok v tejto tabu\u013eke, to sa aj stane, ako m\u00f4\u017eete vidie\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    SHARE EXCLUSIVE \u2013 to je dlh\u0161ie uzamknutie.<\/strong><\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Toto je (ANALYZE) pr\u00edkaz analyz\u00e1tora, ktor\u00fd sa bude pou\u017e\u00edva\u0165.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    SHARE LOCK \u2013 m\u00f4\u017eete explicitne uzamkn\u00fa\u0165 v re\u017eime share.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    M\u00f4\u017eete tie\u017e vytvori\u0165 jedine\u010dn\u00fd index. A tam m\u00f4\u017eete vidie\u0165 SHARE LOCK, ktor\u00fd je jeho s\u00fa\u010das\u0165ou. A uzamkne tabu\u013eku a nastav\u00ed na \u0148u uzamknutie SHARE LOCK.<\/p>\n

    <\/p>\n

    Predvolen\u00e9 SHARE LOCK na tabu\u013eke znamen\u00e1, \u017ee in\u00ed m\u00f4\u017eu tabu\u013eku \u010d\u00edta\u0165, ale nikto ju nem\u00f4\u017ee modifikova\u0165. A presne to sa deje, ke\u010f vytv\u00e1rate jedine\u010dn\u00fd index.<\/strong><\/p>\n

    <\/p>\n

    Ak vytv\u00e1ram jedine\u010dn\u00fd concurrently index, budem ma\u0165 in\u00fd typ uzamknutia, preto\u017ee, ako si pam\u00e4t\u00e1te, pou\u017eitie concurrently indexov zni\u017euje po\u017eadovane uzamknutie. A ak pou\u017e\u00edvam norm\u00e1lne uzamknutie, norm\u00e1lny index, takto zabr\u00e1nim z\u00e1pisu do indexu tabu\u013eky po\u010das jeho vytv\u00e1rania. Ak pou\u017e\u00edvam concurrently index, mus\u00edm pou\u017ei\u0165 in\u00fd typ uzamknutia. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    SHARE ROW EXCLUSIVE \u2013 op\u00e4\u0165 ho mo\u017eno nastavi\u0165 explicitne (jasne).<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Alebo m\u00f4\u017eeme vytvori\u0165 pravidlo, t.j. vzia\u0165 nejak\u00fd konkr\u00e9tny pr\u00edpad, pri ktorom sa bude pou\u017e\u00edva\u0165.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    EXCLUSIVE uzamknutie znamen\u00e1, \u017ee nikto in\u00fd nem\u00f4\u017ee meni\u0165 tabu\u013eku. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu vid\u00edme r\u00f4zne typy uzamknut\u00ed. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    ACCESS EXCLUSIVE, napr\u00edklad, je pr\u00edkaz uzamknutia. Napr\u00edklad, ak rob\u00edte CLUSTER table<\/code>, znamen\u00e1 to, \u017ee nikto nebude m\u00f4c\u0165 zapisova\u0165 dovn\u00fatra. A uzamkne nielen samotn\u00fa tabu\u013eku, ale aj indexy.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Toto je druh\u00e1 str\u00e1nka uzamknutia ACCESS EXCLUSIVE, kde vid\u00edme konkr\u00e9tne, \u010do uzamkne v tabu\u013eke. Uzamkne jednotliv\u00e9 riadky tabu\u013eky, \u010do je dos\u0165 zauj\u00edmav\u00e9.<\/p>\n

    <\/p>\n

    Toto s\u00fa v\u0161etky z\u00e1kladn\u00e9 inform\u00e1cie, ktor\u00e9 som chcel poskytn\u00fa\u0165. Hovorili sme o blokovaniach, o ID transakci\u00ed, o virtu\u00e1lnych ID transakci\u00ed a o trval\u00fdch ID transakci\u00ed. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A teraz si prejdeme pr\u00edpadmi blokovania. Toto je najzauj\u00edmavej\u0161ia \u010das\u0165. Pozrieme sa na ve\u013emi zauj\u00edmav\u00e9 situ\u00e1cie. A moj\u00edm cie\u013eom v tejto prezent\u00e1cii je poskytn\u00fa\u0165 v\u00e1m lep\u0161ie pochopenie toho, \u010do Postgres naozaj rob\u00ed, ke\u010f sa pok\u00fa\u0161a blokova\u0165 r\u00f4zne veci. Mysl\u00edm, \u017ee m\u00e1 ve\u013emi dobr\u00fa schopnos\u0165 blokova\u0165 jednotliv\u00e9 \u010dasti. <\/p>\n

    <\/p>\n

    Po\u010fme sa pozrie\u0165 na konkr\u00e9tne pr\u00edklady.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Za\u010dneme s tabu\u013ekami a s jedn\u00fdm riadkom v tabu\u013eke. Ke\u010f nie\u010do vlo\u017e\u00edm, zobrazuje sa mi ExclusiveLock, ID transakcie a ExclusiveLock na tabu\u013eke. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A \u010do sa stane, ak vlo\u017e\u00edm \u010fal\u0161ie dva riadky? A teraz m\u00e1me v na\u0161ej tabu\u013eke tri riadky. Vlo\u017eil som jeden riadok a dostal som toto ako v\u00fdstup. A ak vlo\u017e\u00edm \u010fal\u0161ie dva riadky, \u010do je tu zvl\u00e1\u0161tne? Tu je zvl\u00e1\u0161tnos\u0165, preto\u017ee som pridal tri riadky k tejto tabu\u013eke, ale m\u00e1m st\u00e1le dva riadky v tabu\u013eke blokovania. A to je v z\u00e1sade z\u00e1kladn\u00e9 spr\u00e1vanie Postgresu.<\/p>\n

    <\/p>\n

    Mnoh\u00ed si myslia, \u017ee ak v datab\u00e1ze zablokujete 100 riadkov, budete musie\u0165 vytvori\u0165 100 vstupov blokovania. Ak by som blokoval naraz 1 000 riadkov, potom by som potreboval 1 000 tak\u00fdchto po\u017eiadaviek. A ak potrebujem zablokova\u0165 mili\u00f3n alebo miliardu. Ale ak budeme robi\u0165 veci takto, nebude to ve\u013emi efekt\u00edvne. Ak ste pou\u017e\u00edvali syst\u00e9m, ktor\u00fd vytv\u00e1ra vstupy blokovania pre ka\u017ed\u00fd jednotliv\u00fd riadok, uvid\u00edte, \u017ee je to zlo\u017eit\u00e9. Preto\u017ee mus\u00edte ur\u010di\u0165 tabu\u013eku blokovania, ktor\u00e1 sa m\u00f4\u017ee zaplni\u0165, ale Postgres to nerob\u00ed. <\/p>\n

    <\/p>\n

    A na tomto slajde je ve\u013emi d\u00f4le\u017eit\u00e9, \u017ee tu je jasne demon\u0161trovan\u00e9, \u017ee existuje \u010fal\u0161\u00ed syst\u00e9m, ktor\u00fd pracuje v r\u00e1mci MVCC, ktor\u00fd blokuje jednotliv\u00e9 riadky. Tak\u017ee, ke\u010f zablokujete miliardy riadkov, Postgres nevytv\u00e1ra miliardu samostatn\u00fdch pr\u00edkazov na blokovanie. A to ve\u013emi dobre ovplyv\u0148uje v\u00fdkon. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A \u010do aktualiz\u00e1cie? Moment\u00e1lne aktualizujem rad, a m\u00f4\u017eete si v\u0161imn\u00fa\u0165, \u017ee vykonal dve r\u00f4zne oper\u00e1cie. S\u00fa\u010dasne zablokoval tabu\u013eku, ale taktie\u017e zablokoval index. A musel zablokova\u0165 index, preto\u017ee na tejto tabu\u013eke s\u00fa unik\u00e1tne obmedzenia. Chceme sa uisti\u0165, \u017ee nikto ho nem\u00e1 mo\u017enos\u0165 meni\u0165, tak\u017ee ho zablokujeme.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A \u010do sa stane, ak chcem aktualizova\u0165 dva rady? Vid\u00edme, \u017ee sa spr\u00e1va rovnako. Rob\u00edme dvakr\u00e1t viac aktualiz\u00e1ci\u00ed, ale presne rovnak\u00fd po\u010det zablokovan\u00fdch riadkov. <\/p>\n

    <\/p>\n

    Ak v\u00e1s zauj\u00edma, ako to Postgres rob\u00ed, mus\u00edte si vypo\u010du\u0165 moje predn\u00e1\u0161ky o MVCC, aby ste sa dozvedeli, ako Postgres intern\u00e9 ozna\u010duje tie rady, ktor\u00e9 men\u00ed. Postgres m\u00e1 sp\u00f4sob, ako to rob\u00ed, ale nerob\u00ed to na \u00farovni blokovania tabuliek, rob\u00ed to na ni\u017e\u0161ej a efekt\u00edvnej\u0161ej \u00farovni.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A \u010do ak chcem nie\u010do odstr\u00e1ni\u0165? Ak odstr\u00e1nim napr\u00edklad jeden rad a st\u00e1le m\u00e1m svoje dva vstupn\u00e9 blok\u00e1dy, a aj keby som chcel odstr\u00e1ni\u0165 v\u0161etky, st\u00e1le tam bud\u00fa.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A povedzme, \u017ee chcem vlo\u017ei\u0165 1 000 riadkov, a potom ich bu\u010f odstr\u00e1ni\u0165, alebo prida\u0165 \u010fal\u0161\u00edch 1 000 riadkov, individu\u00e1lne riadky, ktor\u00e9 prid\u00e1vam alebo men\u00edm, sa tu nezap\u00ed\u0161u. Zap\u00ed\u0161u sa na ni\u017e\u0161ej \u00farovni v r\u00e1mci samotn\u00e9ho radu. A po\u010das mojej predn\u00e1\u0161ky o MVCC som o tom hovoril podrobne. Ale je ve\u013emi d\u00f4le\u017eit\u00e9, ke\u010f analyzujete blok\u00e1cie, uisti\u0165 sa, \u017ee m\u00e1te blok\u00e1ciu na \u00farovni tabu\u013eky a \u017ee tu nevid\u00edte, ako sa zapisuj\u00fa jednotliv\u00e9 rady. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A \u010do explicitn\u00e1 blok\u00e1cia?<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Ak kliknem na \u201aaktualizova\u0165\u2018, m\u00e1m dva zablokovan\u00e9 rady. A ak ich v\u0161etky ozna\u010d\u00edm a kliknem na \u201aaktualizova\u0165 v\u0161ade\u2018, st\u00e1le mi zostan\u00fa dve z\u00e1znamy blok\u00e1cie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Nevytv\u00e1rame samostatn\u00e9 z\u00e1znamy pre ka\u017ed\u00fd jednotliv\u00fd rad. Preto\u017ee by to zn\u00ed\u017eilo v\u00fdkon, mohlo by ich by\u0165 pr\u00edli\u0161 ve\u013ea. A mohli by sme sa ocitn\u00fa\u0165 v nepr\u00edjemnej situ\u00e1cii. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A to ist\u00e9, ak rob\u00edme zdie\u013ean\u00e9, m\u00f4\u017eeme robi\u0165 pre v\u0161etk\u00fdch 30-kr\u00e1t. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Obnovujeme na\u0161u tabu\u013eku, v\u0161etko odstra\u0148ujeme a potom znova vklad\u00e1me jeden rad.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    In PostgreSQL, another type of behavior you observe is well-known and desirable\u2014namely, that you can conduct updates or selects simultaneously. The selects do not block updates, and vice versa. We are saying that a reader does not block a writer while a writer does not block a reader. <\/p>\n

    <\/p>\n

    I will show you an example of this. I will make a selection now. We will then perform an INSERT. You will later see\u2014694. You will see the transaction ID that performed this insertion. This is how it works. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    And if I look at my backend ID now, it has become\u2014695. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    And I can see that 695 appears in my table. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    If I conduct an update here like this, I encounter a different case. In this situation, 695 has an exclusive lock, while the update behaves similarly, but there is no conflict between them, which is rather unusual. <\/p>\n

    <\/p>\n

    You can notice that at the top is a ShareLock and at the bottom is an ExclusiveLock. Both transactions have been executed.<\/p>\n

    <\/p>\n

    You need to listen to my MVCC presentation to understand how this happens. But this illustrates that you can do this simultaneously, i.e., perform SELECT and UPDATE at the same time.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Let's reset and perform another operation once again. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    If you try to run two updates simultaneously on the same row, it will get locked. Remember, I said that a reader does not block a writer, while a writer blocks a reader, but one writer can block another writer. This means we cannot have two people updating the same row at the same time. One must wait until the other finishes. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    To illustrate this, I will look at the Lockdemo table. We will examine one row. Regarding transaction 698.<\/p>\n

    <\/p>\n

    We updated it to 2. 699 is the first update. It has either completed successfully or is in a pending transaction awaiting confirmation or cancellation. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Pozrite sa na in\u00e9 \u2013 2\/51 \u2013 to je n\u00e1\u0161 prv\u00fd transak\u010dn\u00fd dopyt, na\u0161a prv\u00e1 rel\u00e1cia. 3\/112 \u2013 to je druh\u00fd dopyt, ktor\u00fd sa objavil hore a ktor\u00fd zmenil t\u00fato hodnotu na 3. A ak si v\u0161imnete, horn\u00fd dopyt zablokoval s\u00e1m seba, \u010do bolo 699. Ale 3\/112 neposkytli zablokovanie. V st\u013apci Lock_mode je nap\u00edsan\u00e9, \u017ee \u010dak\u00e1. \u010cak\u00e1 na 699. A ak sa pozriete, kde je 699, je vy\u0161\u0161ie. A \u010do urobila prv\u00e1 rel\u00e1cia? Vytvorila exkluz\u00edvne zablokovanie na svojom vlastnom transak\u010dnom ID. Takto to rob\u00ed Postgres. Zablokuje svoje vlastn\u00e9 transak\u010dn\u00e9 ID. A ak chcete \u010daka\u0165, k\u00fdm niekto potvrd\u00ed alebo zru\u0161\u00ed, mus\u00edte \u010daka\u0165, k\u00fdm existuje \u010dakaj\u00faca transakcia. A preto m\u00f4\u017eeme vidie\u0165 zvl\u00e1\u0161tny riadok. <\/p>\n

    <\/p>\n

    Pozrime sa e\u0161te raz. V\u013eavo vid\u00edme na\u0161e spracovate\u013esk\u00e9 ID. V druhom st\u013apci vid\u00edme na\u0161u virtu\u00e1lnu ID transakcie, a v tre\u0165om vid\u00edme lock_type. \u010co to znamen\u00e1? V podstate hovor\u00ed, \u017ee zablokuje transak\u010dn\u00e9 ID. Ale v\u0161imnite si, \u017ee vo v\u0161etk\u00fdch radoch dole je nap\u00edsan\u00e9 relation. A preto m\u00e1te dva druhy zablokovania v tabu\u013eke. Existuje zablokovanie relation. A tie\u017e existuje zablokovanie transactionid, kde sa zablokujete sami, to je presne to, \u010do sa deje v prvom riadku alebo \u00faplne dole, kde transationid, kde \u010dak\u00e1me, aby 699 dokon\u010dil svoju oper\u00e1ciu. <\/p>\n

    <\/p>\n

    Sledujem, \u010do sa tu deje. A tu sa s\u00fa\u010dasne dej\u00fa dve veci. Pozer\u00e1te sa na zablokovanie pod\u013ea transak\u010dn\u00e9ho ID v prvom riadku, ktor\u00e9 sa blokuje samo. A blokuje samo seba, aby don\u00fatilo \u013eud\u00ed \u010daka\u0165. <\/p>\n

    <\/p>\n

    Ak sa pozriete na 6. riadok, tak tam je rovnak\u00fd z\u00e1znam ako prv\u00fd. A preto je transakcia 699 zablokovan\u00e1. 700 sa tie\u017e samoblokuje. A potom v dolnom riadku uvid\u00edte, \u017ee \u010dak\u00e1me, k\u00fdm 699 dokon\u010d\u00ed svoju oper\u00e1ciu.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A v lock_type, tuple vid\u00edte \u010d\u00edsla. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    M\u00f4\u017eete vidie\u0165, \u017ee to je 0\/10. A to je \u010d\u00edslo str\u00e1nky, a tie\u017e offset tohto konkr\u00e9tneho riadu. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A vid\u00edte, \u017ee sa to st\u00e1va 0\/11, ke\u010f aktualizujeme. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Ale v skuto\u010dnosti \u2013 je to 0\/10, preto\u017ee sa o\u010dak\u00e1va t\u00e1to oper\u00e1cia. M\u00f4\u017eeme sa pozrie\u0165, \u010do je ten riadok, ktor\u00fd \u010dak\u00e1m na potvrdenie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Ke\u010f sme to potvrdili a klikli na commit, a ke\u010f sa aktualiz\u00e1cia skon\u010dila, toto je to, \u010do znovu dostaneme. Transakcia 700 je jedin\u00e1 blok\u00e1da, na ktor\u00fa nikto ne\u010dak\u00e1, preto\u017ee bola zakomitovan\u00e1. Iba \u010dak\u00e1, k\u00fdm sa transakcia dokon\u010d\u00ed. Hne\u010f ako 699 skon\u010d\u00ed, u\u017e na ni\u010d ne\u010dak\u00e1me. A teraz transakcia 700 hovor\u00ed, \u017ee v\u0161etko je v poriadku, \u017ee v\u0161etky blok\u00e1cie, ktor\u00e9 potrebuje, m\u00e1 vo v\u0161etk\u00fdch povolen\u00fdch tabu\u013ek\u00e1ch. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A aby sme to v\u0161etko e\u0161te viac skomplikovali, vytv\u00e1rame \u010fal\u0161\u00ed poh\u013ead, ktor\u00fd tentoraz poskytne hierarchiu. Neo\u010dak\u00e1vam, \u017ee tento dotaz pochop\u00edte. Ale to n\u00e1m d\u00e1 jasnej\u0161\u00ed poh\u013ead na to, \u010do sa deje. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Toto je rekurz\u00edvny poh\u013ead, ktor\u00fd m\u00e1 tie\u017e \u010fal\u0161iu sekciu. A potom op\u00e4\u0165 vracia v\u0161etko dokopy. Po\u010fme to vyu\u017ei\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \u010co ak urob\u00edme tri s\u00fabe\u017en\u00e9 aktualiz\u00e1cie a povieme, \u017ee rad je teraz rovn\u00fd trom. A zmen\u00edme 3 na 4. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A teraz vid\u00edme 4. A transak\u010dn\u00fd ID 702. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A potom zmen\u00edm 4 na 5. A 5 na 6, a 6 na 7. A zapisujem do radu \u013eud\u00ed, ktor\u00ed bud\u00fa \u010daka\u0165 na to, aby t\u00e1to jedna transakcia skon\u010dila.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A v\u0161etko je zrozumite\u013en\u00e9. Ak\u00fd je prv\u00fd rad? Je to 702. To je transak\u010dn\u00fd ID, ktor\u00e9 p\u00f4vodne nastavilo t\u00fato hodnotu. A \u010do m\u00e1m nap\u00edsan\u00e9 v st\u013apci Granted? M\u00e1m tam pozn\u00e1mky f<\/code>. To s\u00fa moje aktualiz\u00e1cie, ktor\u00e9 (5, 6, 7) nem\u00f4\u017eu by\u0165 schv\u00e1len\u00e9, preto\u017ee \u010dak\u00e1me, k\u00fdm sa transak\u010dn\u00fd ID 702 dokon\u010d\u00ed. Tam m\u00e1me blok\u00e1ciu transak\u010dn\u00e9ho ID. A m\u00e1me 5 transak\u010dn\u00fdch blok\u00e1ci\u00ed ID.<\/p>\n

    <\/p>\n

    A ak sa pozriete na 704, na 705, tam e\u0161te ni\u010d nie je nap\u00edsan\u00e9, preto\u017ee e\u0161te nevedia, \u010do sa deje. Iba p\u00ed\u0161u, \u017ee nemaj\u00fa tu\u0161enie, \u010do sa deje. A proste zaspia, preto\u017ee \u010dakaj\u00fa, k\u00fdm niekto skon\u010d\u00ed a prebudia ich, ke\u010f sa objav\u00ed pr\u00edle\u017eitos\u0165 zmeni\u0165 rad. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Toto je, ako to vyzer\u00e1. Je jasn\u00e9, \u017ee v\u0161etci \u010dakaj\u00fa na 12. riadok. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Toto je to, \u010do sme videli tu. Tu je 0\/12. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee, ke\u010f je prv\u00e1 transakcia schv\u00e1len\u00e1, m\u00f4\u017eete tu vidie\u0165, ako funguje hierarchia. A teraz je v\u0161etko jasn\u00e9. V\u0161etci s\u00fa \u010dist\u00fd. A v skuto\u010dnosti st\u00e1le \u010dakaj\u00fa. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu sa deje. 702 sa ukl\u00e1da. Teraz 703 dost\u00e1va t\u00fato zamknut\u00fa polo\u017eku a potom 704 za\u010d\u00edna \u010daka\u0165, k\u00fdm sa 703 ulo\u017e\u00ed. A 705 tie\u017e \u010dak\u00e1. A ke\u010f sa to cel\u00e9 skon\u010d\u00ed, tak si to v\u0161etko vy\u010distia. Chcel by som pouk\u00e1za\u0165 na to, \u017ee v\u0161etci stoj\u00ed v rade. A je to ve\u013emi podobn\u00e9 situ\u00e1cii s dopravnou z\u00e1pchou, ke\u010f v\u0161etci \u010dakaj\u00fa na prv\u00e9 auto. Prv\u00e9 auto sa zastavilo a v\u0161etci sa zoradia do dlh\u00e9ho radu. Potom sa pohne, potom m\u00f4\u017ee \u00eds\u0165 \u010fal\u0161ie auto dopredu a z\u00edska\u0165 svoju uzamknut\u00fa polo\u017eku a tak \u010falej. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A ak v\u00e1m to nepripadalo dostato\u010dne zlo\u017eit\u00e9, teraz si povieme o deadlockoch. Neviem, kto z v\u00e1s sa s nimi stretol. Je to dos\u0165 be\u017en\u00fd probl\u00e9m v datab\u00e1zov\u00fdch syst\u00e9moch. Deadlocky s\u00fa situ\u00e1cie, ke\u010f jedna rel\u00e1cia \u010dak\u00e1 na to, aby nie\u010do vykonala in\u00e1 rel\u00e1cia. A v tejto chv\u00edli druh\u00e1 rel\u00e1cia \u010dak\u00e1, aby prv\u00e1 rel\u00e1cia vykonala nie\u010do. <\/p>\n

    <\/p>\n

    A napr\u00edklad, ak Ivan povie: \u201eDaj mi nie\u010do\u201c, a ja poviem: \u201eNie, d\u00e1m ti to len vtedy, ak mi d\u00e1\u0161 nie\u010do in\u00e9\u201c. A on povie: \u201eNie, ned\u00e1m ti to, ak mi to ned\u00e1\u0161\u201c. A dost\u00e1vame sa do situ\u00e1cie m\u0155tvej blok\u00e1cie. Som si ist\u00fd, \u017ee Ivan to neurob\u00ed, ale rozumiete podstate, \u017ee dvaja \u013eudia chc\u00fa nie\u010do z\u00edska\u0165 a nie s\u00fa ochotn\u00ed to da\u0165, pok\u00fdm im druh\u00fd \u010dlovek ned\u00e1 to, \u010do chc\u00fa. A tu nie je rie\u0161enie. <\/p>\n

    <\/p>\n

    A v podstate, va\u0161a datab\u00e1za to mus\u00ed odhali\u0165. A potom je potrebn\u00e9 odstr\u00e1ni\u0165 alebo zavrie\u0165 jednu z rel\u00e1ci\u00ed, preto\u017ee inak tam zostan\u00fa nav\u017edy. A vid\u00edme to v datab\u00e1zach, vid\u00edme to v opera\u010dn\u00fdch syst\u00e9moch. A v v\u0161etk\u00fdch miestach, kde m\u00e1me paraleln\u00e9 procesy, sa to m\u00f4\u017ee dia\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A teraz nastav\u00edme dve deadlocky. Nastav\u00edme 50 a 80. V prvom riadku vykon\u00e1m aktualiz\u00e1ciu z 50 na 50. Budem ma\u0165 \u010d\u00edslo transakcie 710. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A potom zmen\u00edm 80 na 81 a 50 na 51. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A takto to bude vyzera\u0165. A preto m\u00e1 710 zamknut\u00fa polo\u017eku a 711 \u010dak\u00e1 na potvrdenie. Videli sme to, ke\u010f sme aktualizovali. 710 je vlastn\u00edkom na\u0161ej polo\u017eky. A 711 \u010dak\u00e1, k\u00fdm 710 dokon\u010d\u00ed transakciu.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A dokonca je tam nap\u00edsan\u00e9 na akom konkr\u00e9tnom riadku sa n\u00e1m deje deadlock. A tu to za\u010d\u00edna by\u0165 zvl\u00e1\u0161tne.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Teraz aktualizujeme 80 na 80. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A tu za\u010d\u00ednaj\u00fa deadlocky. 710 \u010dak\u00e1 na odpove\u010f od 711, a 711 \u010dak\u00e1 na 710. A toto nesko\u010d\u00ed dobre. A nie je z toho \u017eiadne v\u00fdchodisko. A bud\u00fa si navz\u00e1jom \u010daka\u0165 na odpove\u010f. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A to len v\u0161etko za\u010dne oneskorenie. A toho sa nechceme do\u010dka\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A v Postgres s\u00fa sp\u00f4soby ako si v\u0161imn\u00fa\u0165, ke\u010f k tomu d\u00f4jde. A ke\u010f k tomu d\u00f4jde, dostanete tak\u00fato chybu. A jasne z toho vypl\u00fdva, \u017ee tak\u00fdto proces \u010dak\u00e1 na SHARE LOCK od in\u00e9ho procesu, t.j. ktor\u00fd je blokovan\u00fd procesom 711. A ten proces \u010dakal, aby mu bol poskytnut\u00fd SHARE LOCK na ur\u010dit\u00fd transak\u010dn\u00fd ID a bol blokovan\u00fd ur\u010dit\u00fdm procesom. Preto je tu situ\u00e1cia s m\u0155tvym z\u00e1pisom.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Existuje trojstrann\u00fd deadlock? Je to mo\u017en\u00e9? \u00c1no. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Vklad\u00e1me tieto \u010d\u00edsla do tabu\u013eky. Men\u00edme 40 za 40, rob\u00edme uzamknutie. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Men\u00edme 60 za 61, 80 za 81. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A potom men\u00edme 80, a potom \u2013 bum! <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A 714 teraz \u010dak\u00e1 na 715. 716 \u010dak\u00e1 na 715. A s t\u00fdm u\u017e ni\u010d neurob\u00edte.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu u\u017e nie s\u00fa dvaja \u013eudia, je tu u\u017e tri osoby. Chcem nie\u010do od teba, tento chce nie\u010do od tretej osoby, a tretia osoba chce nie\u010do odo m\u0148a. A dost\u00e1vame sa do trojstrann\u00e9ho \u010dakania, preto\u017ee v\u0161etci \u010dak\u00e1me, k\u00fdm druh\u00fd spln\u00ed to, \u010do m\u00e1 robi\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A Postgres vie, na ktorom riadku to prebieha. A preto v\u00e1m d\u00e1 nasleduj\u00facu spr\u00e1vu, ktor\u00e1 ukazuje, \u017ee m\u00e1te probl\u00e9m, kde tri vstupy vz\u00e1jomne blokuj\u00fa jeden druh\u00e9ho. A tu nie s\u00fa obmedzenia. M\u00f4\u017ee to by\u0165 situ\u00e1cia, kde 20 z\u00e1znamov navz\u00e1jom blokuje jeden druh\u00e9ho. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \u010eal\u0161\u00ed probl\u00e9m \u2013 to je serializable. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Ak ide o \u0161peci\u00e1lne serializable uzamknutie.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A vraciame sa k 719. M\u00e1 \u00faplne norm\u00e1lny v\u00fdstup. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A m\u00f4\u017eete klikn\u00fa\u0165, aby ste vykonali transakciu zo serializable.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A rozumiete, \u017ee teraz m\u00e1te in\u00fd typ uzamknutia SA \u2013 to znamen\u00e1 serializable. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A preto m\u00e1me nov\u00fd typ uzamknutia, ktor\u00fd sa naz\u00fdva SARieadLock, \u010do je s\u00e9riov\u00e9 uzamknutie a umo\u017e\u0148uje vklada\u0165 s\u00e9riov\u00e9 \u010d\u00edsla.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A tie\u017e m\u00f4\u017eete vklada\u0165 jedine\u010dn\u00e9 indexy. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    V tejto tabu\u013eke m\u00e1me jedine\u010dn\u00e9 indexy.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tak\u017ee, ak sem vlo\u017e\u00edm \u010d\u00edslo 2, preto m\u00e1m 2. Ale na vrchu vklad\u00e1m e\u0161te jedno 2. A m\u00f4\u017eete vidie\u0165, \u017ee 721 m\u00e1 v\u00fdlu\u010dn\u00e9 uzamknutie. Ale teraz 722 \u010dak\u00e1, aby 721 dokon\u010dil svoju oper\u00e1ciu, preto\u017ee nem\u00f4\u017ee vlo\u017ei\u0165 2, k\u00fdm nevie, \u010do sa stane s 721. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A ak rob\u00edme subtransaction.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Tu m\u00e1me 723.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A ak zachov\u00e1me bod a potom ho aktualizujeme, dostaneme nov\u00fd transak\u010dn\u00fd ID. Toto je \u010fal\u0161\u00ed charakter spr\u00e1vania, ktor\u00fd by ste mali vedie\u0165. Ak to vr\u00e1time, transak\u010dn\u00fd ID zanikne. 724 zanik\u00e1. Ale teraz sa objav\u00ed 725. <\/p>\n

    <\/p>\n

    A \u010do sa sna\u017e\u00edm urobi\u0165? Sna\u017e\u00edm sa v\u00e1m uk\u00e1za\u0165 pr\u00edklady nezvy\u010dajn\u00fdch z\u00e1mkov, ktor\u00e9 m\u00f4\u017eete n\u00e1js\u0165: \u010di u\u017e s\u00fa to serializable z\u00e1mky alebo SAVEPOINT \u2013 to s\u00fa r\u00f4zne druhy z\u00e1mkov, ktor\u00e9 sa zobrazia v tabu\u013eke z\u00e1mkov. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Ide o vytvorenie explicitn\u00fdch z\u00e1mkov, ktor\u00e9 maj\u00fa pg_advisory_lock. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A vid\u00edte, \u017ee typ z\u00e1mku je tu uveden\u00fd ako advisory. A tu je \u010derven\u00fdm p\u00edsmom nap\u00edsan\u00e9 \u201eadvisory\u201c. A m\u00f4\u017eete to s\u00fa\u010dasne takto zamkn\u00fa\u0165 s pg_advisory_unlock.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A na z\u00e1ver by som v\u00e1m chcel uk\u00e1za\u0165 e\u0161te jednu fascinuj\u00facu vec. Vytvor\u00edm \u010fal\u0161\u00ed typ. Ale spoj\u00edm tabu\u013eku pg_locks s tabu\u013ekou pg_stat_activity. A pre\u010do to chcem urobi\u0165? Preto\u017ee mi to umo\u017en\u00ed vidie\u0165 v\u0161etky aktu\u00e1lne rel\u00e1cie a zisti\u0165, ak\u00fdch z\u00e1mkov sa ob\u00e1vaj\u00fa. A to je dos\u0165 zauj\u00edmav\u00e9, ke\u010f spoj\u00edme tabu\u013eku z\u00e1mkov s tabu\u013ekou po\u017eiadaviek. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A tu vytv\u00e1rame pg_stat_view. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A aktualizujeme riadok na jeden. A tu vid\u00edme 724. A potom aktualizujeme n\u00e1\u0161 riadok na tri. A \u010do tu teraz vid\u00edte? To s\u00fa po\u017eiadavky, t.j. vid\u00edte cel\u00fd zoznam po\u017eiadaviek, ktor\u00e9 s\u00fa uveden\u00e9 v \u013eavom st\u013apci. A potom na pravej strane m\u00f4\u017eete vidie\u0165 z\u00e1mky a to, \u010do vytv\u00e1raj\u00fa. A to m\u00f4\u017ee by\u0165 pre v\u00e1s zrozumite\u013enej\u0161ie, aby ste nemuseli zaka\u017ed\u00fdm prech\u00e1dza\u0165 ka\u017ed\u00fa rel\u00e1ciu a pozera\u0165 sa \u2013 \u010di sa k nej pripoji\u0165 alebo nie. To sa deje za n\u00e1s. <\/p>\n

    <\/p>\n

    \u010eal\u0161ia funkcia, ktor\u00e1 je ve\u013emi u\u017eito\u010dn\u00e1 \u2013 je to pg_blocking_pids<\/code>Mo\u017eno ste o tom nikdy nepo\u010duli. \u010co to rob\u00ed? Umo\u017e\u0148uje n\u00e1m to poveda\u0165, ak\u00e9 ID procesov o\u010dak\u00e1va tento session 11740. A m\u00f4\u017eete vidie\u0165, \u017ee 11740 o\u010dak\u00e1va 724. A 724 je na vrchu. A 11306 je v\u00e1\u0161 ID proces. V podstate t\u00e1to funkcia prech\u00e1dza va\u0161ou tabulkou zamknut\u00ed. A viem, \u017ee je to trochu zlo\u017eit\u00e9, ale vy to ch\u00e1pete. V podstate t\u00e1to funkcia prech\u00e1dza touto tabulkou zamknut\u00ed a sna\u017e\u00ed sa n\u00e1js\u0165, kde je tento proces ID, ber\u00fac do \u00favahy zamknutia, ktor\u00e9 o\u010dak\u00e1va. A tie\u017e sa sna\u017e\u00ed vypo\u010d\u00edta\u0165, ktor\u00fd presne proces ID m\u00e1 ten proces, ktor\u00fd \u010dak\u00e1 zamknutie. Preto m\u00f4\u017eete spusti\u0165 t\u00fato funkciu. pg_blocking_pids<\/code>.<\/p>\n

    <\/p>\n

    A to m\u00f4\u017ee by\u0165 ve\u013emi u\u017eito\u010dn\u00e9. Pridali sme to iba s verziou 9.6, tak\u017ee t\u00e1to funkcia m\u00e1 len 5 rokov, ale je ve\u013emi a ve\u013emi u\u017eito\u010dn\u00e1. A to ist\u00e9 plat\u00ed pre druh\u00fd dotaz. Zobrazuje presne to, \u010do potrebujeme vidie\u0165. <\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    To je to, o \u010dom som chcel s vami hovori\u0165. A ako som o\u010dak\u00e1val, str\u00e1vili sme cel\u00fd n\u00e1\u0161 \u010das, preto\u017ee bolo ve\u013ea sn\u00edmok. A sn\u00edmky s\u00fa k dispoz\u00edcii na stiahnutie. Chcel by som v\u00e1m po\u010fakova\u0165, \u017ee ste tu boli. Som si ist\u00fd, \u017ee si u\u017eijete zvy\u0161ok konferencie, \u010fakujem v\u00e1m!<\/p>\n

    <\/p>\n

    Ot\u00e1zky:<\/p>\n

    <\/p>\n

    Napr\u00edklad, ak sa sna\u017e\u00edm aktualizova\u0165 riadky, a druh\u00e1 session sa sna\u017e\u00ed odstr\u00e1ni\u0165 cel\u00fa tabu\u013eku. Ako rozumiem, malo by tam by\u0165 nie\u010do ako intent lock. Existuje nie\u010do tak\u00e9 v Postgres?<\/em><\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    Vr\u00e1time sa na za\u010diatok. Mo\u017eno si pam\u00e4t\u00e1te, \u017ee ke\u010f rob\u00edte \u010doko\u013evek, napr\u00edklad ke\u010f rob\u00edte SELECT, poskytujeme AccessShareLock. A to zabra\u0148uje odstr\u00e1neniu tabu\u013eky. Preto ak chcete napr\u00edklad aktualizova\u0165 riadok v tabu\u013eke alebo odstr\u00e1ni\u0165 riadok, niekto nem\u00f4\u017ee odstr\u00e1ni\u0165 cel\u00fa tabu\u013eku s\u00fa\u010dasne s t\u00fdm, preto\u017ee dr\u017e\u00edte tento AccessShareLock nad celou tabu\u013ekou a nad riadkom. A ke\u010f ste skon\u010dili, m\u00f4\u017eu to odstr\u00e1ni\u0165. Ale pokia\u013e nie\u010do men\u00edte, nem\u00f4\u017eu to spravi\u0165.<\/p>\n

    <\/p>\n

    Po\u010fme e\u0161te raz. Po\u010fme na pr\u00edklad s odstra\u0148ovan\u00edm. A vid\u00edte, ako je na riadku exkluz\u00edvny lock nad celou tabu\u013ekou. <\/p>\n

    <\/p>\n

    Bude to vyzera\u0165 ako lock exclusive, spr\u00e1vne?<\/em><\/p>\n

    <\/p>\n

    \u00c1no, to vyzer\u00e1 takto. Rozumiem, o \u010dom hovor\u00edte. Hovor\u00edte, \u017ee ak vykon\u00e1m SELECT, dostanem ShareExclusive a potom to pretransformujem do stavu Row Exclusive, st\u00e1va sa to probl\u00e9mom? Ale prekvapuj\u00faco to nesp\u00f4sobuje probl\u00e9m. Vyzer\u00e1 to ako zv\u00fd\u0161enie stup\u0148a blokovania, ale v podstate m\u00e1m uzamknutie, ktor\u00e9 br\u00e1ni mazaniu. A teraz, ke\u010f t\u00fato z\u00e1mku posil\u0148ujem, st\u00e1le br\u00e1ni mazaniu. Tak\u017ee nejde o to, \u017ee by som sa pos\u00faval vy\u0161\u0161ie. T. j. to br\u00e1nilo tomuto u\u017e na ni\u017e\u0161ej \u00farovni, tak\u017ee ke\u010f zvy\u0161ujem jeho \u00farove\u0148, st\u00e1le to br\u00e1ni odstr\u00e1neniu tabu\u013eky. <\/p>\n

    <\/p>\n

    Rozumiem, o \u010dom hovor\u00edte. Tu nejde o situ\u00e1ciu zv\u00fd\u0161enia stup\u0148a blokovania, kde sa sna\u017e\u00edte zbavi\u0165 jednej z\u00e1mky, aby ste zaviedli silnej\u0161iu. Tu to jednoducho v\u0161ade zvy\u0161uje toto br\u00e1nenie, tak\u017ee to nesp\u00f4sobuje \u017eiadny konflikt. Ale je to dobr\u00e1 ot\u00e1zka. Ve\u013ek\u00e1 v\u010faka, \u017ee ste ju polo\u017eili!<\/p>\n

    <\/p>\n

    \u010co mus\u00edme urobi\u0165, aby sme sa vyhli situ\u00e1cii deadlocku, ke\u010f m\u00e1me ve\u013ea rel\u00e1ci\u00ed a ve\u013ek\u00fd po\u010det pou\u017e\u00edvate\u013eov?<\/em><\/p>\n

    <\/p>\n

    Postgres automaticky rozpozn\u00e1va situ\u00e1cie deadlocku. A automaticky odstr\u00e1ni jednu z rel\u00e1ci\u00ed. Jedin\u00fd sp\u00f4sob, ako sa vyhn\u00fa\u0165 situ\u00e1cii s m\u0155tvymi blok\u00e1dami, je blokova\u0165 \u013eud\u00ed v rovnakom porad\u00ed. Tak\u017ee, ke\u010f sa pozriete na va\u0161u aplik\u00e1ciu, \u010dasto je pr\u00ed\u010dina deadlockov\u2026 Predstavte si, \u017ee chcem zablokova\u0165 dve r\u00f4zne veci. Jedna aplik\u00e1cia blokuje tabu\u013eku 1, a druh\u00e1 aplik\u00e1cia blokuje 2 a potom tabu\u013eku 1. A najjednoduch\u0161\u00ed sp\u00f4sob, ako sa vyhn\u00fa\u0165 deadlockom, je pozrie\u0165 sa na va\u0161u aplik\u00e1ciu a pok\u00fasi\u0165 sa zabezpe\u010di\u0165, aby blokovanie prebiehalo v rovnakom porad\u00ed vo v\u0161etk\u00fdch aplik\u00e1ci\u00e1ch. A to obvykle odstr\u00e1ni 80 % probl\u00e9mov, preto\u017ee r\u00f4zni \u013eudia p\u00ed\u0161u tieto aplik\u00e1cie. A ak ich blokujete v rovnakom porad\u00ed, tak ne\u010del\u00edte situ\u00e1cii deadlocku.<\/p>\n

    <\/p>\n

    Ve\u013ek\u00e1 v\u010faka za va\u0161e vyst\u00fapenie! Hovorili ste o vacuum full a, ak spr\u00e1vne ch\u00e1pem, vacuum full deformuje poradie z\u00e1znamov v samostatnom \u00falo\u017eisku, tak\u017ee s\u00fa\u010dasn\u00e9 z\u00e1znamy zost\u00e1vaj\u00fa nezmenen\u00e9. A pre\u010do vacuum full vy\u017eaduje exkluz\u00edvnu z\u00e1mku a pre\u010do konfliktuje so z\u00e1pisov\u00fdmi oper\u00e1ciami?<\/em><\/p>\n

    <\/p>\n

    To je dobr\u00e1 ot\u00e1zka. D\u00f4vodom je, \u017ee pr\u00edkaz vacuum full preber\u00e1 tabu\u013eku. T\u00fdm v podstate vytv\u00e1rame nov\u00fa verziu tabu\u013eky. A tabu\u013eka bude nov\u00e1. To bude \u00faplne nov\u00e1 verzia tabu\u013eky. Probl\u00e9m je v tom, \u017ee ke\u010f to rob\u00edme, nechceme, aby to \u013eudia \u010d\u00edtali, preto\u017ee potrebujeme, aby videli nov\u00fa tabu\u013eku. Preto to s\u00favis\u00ed s predch\u00e1dzaj\u00facou ot\u00e1zkou. Keby sme mohli \u010d\u00edta\u0165 s\u00fa\u010dasne, nemohli by sme ju presun\u00fa\u0165 a nasmerova\u0165 \u013eud\u00ed na nov\u00fa tabu\u013eku. Museli by sme \u010daka\u0165, a\u017e ka\u017ed\u00fd skon\u010d\u00ed s \u010d\u00edtan\u00edm tejto tabu\u013eky, a preto je to v podstate situ\u00e1cia lock exclusive.
    \nLen hovor\u00edme, \u017ee od za\u010diatku blokujeme, preto\u017ee vieme, \u017ee na konci budeme potrebova\u0165 exkluz\u00edvnu blok\u00e1ciu, aby sme presunuli v\u0161etk\u00fdch na nov\u00fa k\u00f3piu. Preto to potenci\u00e1lne m\u00f4\u017eeme povoli\u0165. A rob\u00edme to z\u00e1rove\u0148 s indexovan\u00edm. Ale je to ove\u013ea zlo\u017eitej\u0161ie. A ve\u013emi to s\u00favis\u00ed s va\u0161ou predch\u00e1dzaj\u00facou ot\u00e1zkou o lock exclusive.<\/p>\n

    <\/p>\n

    Je mo\u017en\u00e9 prida\u0165 locking timeout v Postgres? V Oracle m\u00f4\u017eem napr\u00edklad nap\u00edsa\u0165 'select for update' a \u010daka\u0165 50 sek\u00fand na aktualiz\u00e1ciu. To bolo dobr\u00e9 pre aplik\u00e1ciu. Ale v Postgres bu\u010f mus\u00edm urobi\u0165 okam\u017eite a v\u00f4bec ne\u010daka\u0165, alebo \u010daka\u0165 do ur\u010dit\u00e9ho okamihu.<\/em> <\/p>\n

    <\/p>\n

    \u00c1no, m\u00f4\u017eete si vybra\u0165 timeout pre va\u0161e z\u00e1mky, pre va\u0161e locks. M\u00f4\u017eete tie\u017e vyda\u0165 pr\u00edkaz no way, ktor\u00fd bude... ak okam\u017eite nez\u00edskate z\u00e1mok. Tak\u017ee bu\u010f lock timeout, alebo nie\u010do in\u00e9, \u010do v\u00e1m to umo\u017en\u00ed. To sa nerob\u00ed na syntaktickej \u00farovni. Rob\u00ed sa to ako premenn\u00e1 na serveri. Niekedy to nie je mo\u017en\u00e9 pou\u017ei\u0165.<\/p>\n

    <\/p>\n

    M\u00f4\u017eete otvori\u0165 75. sn\u00edmku?<\/em> <\/p>\n

    <\/p>\n

    \u00c1no.<\/p>\n

    <\/p>\n

    \"Odomknutie<\/p>\n

    <\/p>\n

    A moja ot\u00e1zka je nasledovn\u00e1. Pre\u010do oba procesy aktualiz\u00e1cie \u010dakaj\u00fa na 703?<\/em><\/p>\n

    <\/p>\n

    A to je skvel\u00e1 ot\u00e1zka. Nech\u00e1pem, pre\u010do to Postgres rob\u00ed. Ale ke\u010f bol 703 vytvoren\u00fd, o\u010dak\u00e1val 702. A ke\u010f sa objavia 704 a 705, zd\u00e1 sa, \u017ee nevedia, na \u010do \u010dakaj\u00fa, preto\u017ee tam e\u0161te ni\u010d nie je. A Postgres to rob\u00ed takto: ke\u010f nem\u00f4\u017eete z\u00edska\u0165 z\u00e1mok, nap\u00ed\u0161e \u201eAk\u00fd m\u00e1 zmysel v\u00e1s spracov\u00e1va\u0165?\u201c, preto\u017ee aj tak \u010dak\u00e1te na niekoho. Tak\u017ee ho nech\u00e1me visie\u0165 vo vzduchu, v\u00f4bec to neaktualizuje. Ale \u010do sa tu stalo? Hne\u010f ako 702 dokon\u010dil proces a 703 z\u00edskal svoj z\u00e1mok, syst\u00e9m sa vr\u00e1til sp\u00e4\u0165. A povedal, \u017ee teraz m\u00e1me dvoch \u013eud\u00ed, ktor\u00ed s\u00fa v \u010dakacej l\u00ednii. A potom po\u010fme ich spolu aktualizova\u0165. A uk\u00e1\u017eeme, \u017ee obaja \u010dakaj\u00fa. <\/p>\n

    <\/p>\n

    Neviem, pre\u010do to Postgres rob\u00ed. Ale je tu probl\u00e9m, ktor\u00fd sa naz\u00fdva f\u2026. Mysl\u00edm, \u017ee to nie je term\u00edn v sloven\u010dine. To je, ke\u010f v\u0161etci \u010dakaj\u00fa na jeden z\u00e1mok, aj ke\u010f je 20 in\u0161tanci\u00ed, ktor\u00e9 \u010dakaj\u00fa na z\u00e1mok. A zrazu sa v\u0161etci prebudia naraz. A v\u0161etci sa pok\u00fa\u0161aj\u00fa reagova\u0165. Ale syst\u00e9m rob\u00ed tak, \u017ee v\u0161etci \u010dakaj\u00fa na 703. Preto\u017ee v\u0161etci \u010dakaj\u00fa a hne\u010f ich zorad\u00edme do frontu. A ak sa objav\u00ed ak\u00e1ko\u013evek in\u00e1 nov\u00e1 po\u017eiadavka, ktor\u00e1 bola vytvoren\u00e1 po tomto, napr\u00edklad 707, tak tam znova bude pr\u00e1zdnota. <\/p>\n

    <\/p>\n

    A m\u00e1m pocit, \u017ee to sa rob\u00ed, aby sme mohli poveda\u0165, \u017ee v tomto \u0161t\u00e1diu 702 \u010dak\u00e1 na 703, a v\u0161etci t\u00ed, ktor\u00ed pr\u00eddu po \u0148om, nebud\u00fa ma\u0165 \u017eiadny z\u00e1znam v tomto poli. Ale hne\u010f ako prv\u00fd \u010dakaj\u00faci od\u00edde a v\u0161etci, ktor\u00ed \u010dakali v tom okamihu pred aktualiz\u00e1ciou, z\u00edskaj\u00fa ten ist\u00fd identifik\u00e1tor. A preto si mysl\u00edm, \u017ee to je uroben\u00e9, aby sme mohli spracov\u00e1va\u0165 v poriadku, aby boli spr\u00e1vne zoraden\u00ed. <\/p>\n

    <\/p>\n

    V\u017edy som sa na to pozeral ako na dos\u0165 zvl\u00e1\u0161tny fenom\u00e9n. Preto\u017ee tu, napr\u00edklad, ich v\u00f4bec nevymen\u00favame. Ale mysl\u00edm, \u017ee zaka\u017ed\u00fdm, ke\u010f d\u00e1me nov\u00fd z\u00e1mok, sa pozer\u00e1me na v\u0161etk\u00fdch t\u00fdch, ktor\u00ed s\u00fa v procese \u010dakania. Potom ich v\u0161etk\u00fdch zorad\u00edme do frontu. A potom ak\u00fdko\u013evek nov\u00fd, ktor\u00fd pr\u00edde, sa dostane do frontu a\u017e ke\u010f \u010fal\u0161\u00ed \u010dlovek dokon\u010dil spracovanie. Ve\u013emi dobr\u00e1 ot\u00e1zka. \u010eakujem v\u00e1m ve\u013emi pekne za ot\u00e1zku!<\/p>\n

    <\/p>\n

    Mysl\u00edm, \u017ee je ove\u013ea logickej\u0161ie, ke\u010f 705 \u010dak\u00e1 na 704.<\/em> <\/p>\n

    <\/p>\n

    Probl\u00e9m je v tom, \u017ee technicky m\u00f4\u017eete prebudi\u0165 bu\u010f jeden, alebo druh\u00fd. A preto preb\u00fadime ten alebo in\u00fd. \u010co sa v\u0161ak deje v syst\u00e9me? Vid\u00edte, ako 703 zablokoval svoj vlastn\u00fd transak\u010dn\u00fd ID na vrchu. Takto funguje Postgres. A 703 je zablokovan\u00fd svoj\u00edm vlastn\u00fdm transak\u010dn\u00fdm ID, tak\u017ee ak niekto \u010dak\u00e1, \u010dak\u00e1 na 703. V podstate, 703 sa ukon\u010duje. A a\u017e po jeho ukon\u010den\u00ed sa nejak\u00fd z procesov preb\u00fadza. A nevieme, ktor\u00fd proces to bude. Potom postupne spracov\u00e1vame v\u0161etko. Ale nie je jasn\u00e9, ktor\u00fd proces sa preb\u00fadza prv\u00fd, preto\u017ee to m\u00f4\u017ee by\u0165 ktor\u00fdko\u013evek z t\u00fdchto procesov. V podstate sme mali pl\u00e1nova\u010da, ktor\u00fd povedal, \u017ee teraz m\u00f4\u017eeme prebudi\u0165 ktor\u00fdko\u013evek z t\u00fdchto procesov. Jednoducho si n\u00e1hodne vyberieme jeden. Preto obaja musia by\u0165 ozna\u010den\u00ed, preto\u017ee m\u00f4\u017eeme prebudi\u0165 ktor\u00fdko\u013evek z nich. <\/p>\n

    <\/p>\n

    A probl\u00e9m je v tom, \u017ee m\u00e1me CP-nekone\u010dno. A preto je dos\u0165 pravdepodobn\u00e9, \u017ee m\u00f4\u017eeme prebudi\u0165 neskor\u0161\u00ed. A ak, napr\u00edklad, preb\u00fadzame neskor\u0161\u00ed, budeme \u010daka\u0165 na niekoho, kto pr\u00e1ve z\u00edskal z\u00e1mok, a preto neur\u010dujeme, kto presne bude prebuden\u00fd prv\u00fd. Vytv\u00e1rame jednoducho tak\u00fa situ\u00e1ciu a syst\u00e9m ich preb\u00fadza v n\u00e1hodnom porad\u00ed. <\/p>\n

    <\/p>\n

    Existuje \u010dl\u00e1nky o locks Egora Rogova<\/a><\/noindex>. Pozrite sa, s\u00fa tie\u017e zauj\u00edmav\u00e9 a u\u017eito\u010dn\u00e9. T\u00e9ma je samozrejme stra\u0161ne zlo\u017eit\u00e1. Ve\u013emi pekne \u010fakujem, Bruce!<\/em><\/p>\n

    Zdroj: 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\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\udd47Odomknutie Postgres Lock Manager. Bruce Momjian | ProHoster","description":"Prezent\u00e1cia Brucea Momjiana \"Odomknutie Postgres Lock Managera\" z roku 2020.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/87954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=87954"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/87954\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/87955"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=87954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=87954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=87954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}