{"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\/cs\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","title":{"rendered":"Odemknut\u00ed spr\u00e1vce z\u00e1mk\u016f Postgres. Bruce Momjian","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

V\u00fdznam zpr\u00e1vy Bruce Momjiana z roku 2020 \"Odemknut\u00ed spr\u00e1vce z\u00e1mk\u016f Postgres\".<\/p>\n

<\/p>\n

\"Odemknut\u00ed<\/p>\n

<\/p>\n

(Pozn\u00e1mka: V\u0161echny SQL dotazy ze sn\u00edmk\u016f si m\u016f\u017eete st\u00e1hnout na tomto odkazu: http:\/\/momjian.us\/main\/writings\/pgsql\/locking.sql<\/a><\/noindex>)<\/p>\n

<\/p>\n

Ahoj! Je skv\u011bl\u00e9 b\u00fdt tu znovu v Rusku. Omlouv\u00e1m se, \u017ee jsem nemohl p\u0159ijet minul\u00fd rok, ale letos m\u00e1me s Ivanem velk\u00e9 pl\u00e1ny. Douf\u00e1m, \u017ee tady budu mnohem \u010dast\u011bji. M\u00e1m r\u00e1d n\u00e1v\u0161t\u011bvu Ruska. Nav\u0161t\u00edv\u00edm Tyumen a Tver. Jsem velmi r\u00e1d, \u017ee budu m\u00edt p\u0159\u00edle\u017eitost b\u00fdt v t\u011bchto m\u011bstech. <\/p>\n

<\/p>\n

Jmenuji se Bruce Momjiana. Pracuji ve spole\u010dnosti EnterpriseDB a pracuji s Postgres v\u00edce ne\u017e 23 let. \u017diji v Philadelphii ve Spojen\u00fdch st\u00e1tech. Cestuji asi 90 dn\u00ed v roce a nav\u0161t\u011bvuji p\u0159ibli\u017en\u011b 40 konferenc\u00ed. M\u016fj webov\u00e1 str\u00e1nka<\/a><\/noindex>, kter\u00e1 obsahuje sn\u00edmky, kter\u00e9 v\u00e1m nyn\u00ed uk\u00e1\u017eu. Po konferenci si je m\u016f\u017eete st\u00e1hnout z m\u00e9 osobn\u00ed str\u00e1nky. Obsahuje tak\u00e9 kolem 30 prezentac\u00ed, videa a velk\u00e9 mno\u017estv\u00ed p\u0159\u00edsp\u011bvk\u016f v blogu, v\u00edce ne\u017e 500. Je to pom\u011brn\u011b bohat\u00fd zdroj, a pokud v\u00e1s tento materi\u00e1l zaj\u00edm\u00e1, zvu v\u00e1s, abyste ho vyu\u017eili. <\/p>\n

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

D\u0159\u00edve jsem byl u\u010ditelem, profesorem, ne\u017e jsem za\u010dal pracovat s Postgres. A jsem velmi r\u00e1d, \u017ee v\u00e1m mohu nyn\u00ed \u0159\u00edct to, co v\u00e1m chci \u0159\u00edct. Je to jedna z m\u00fdch nejzaj\u00edmav\u011bj\u0161\u00edch prezentac\u00ed. Tato prezentace obsahuje 110 sn\u00edmk\u016f. Za\u010dneme s jednoduch\u00fdmi v\u011bcmi a na konci se prezentace stane st\u00e1le slo\u017eit\u011bj\u0161\u00ed a slo\u017eit\u011bj\u0161\u00ed.<\/p>\n

<\/p>\n

\"Odemknut\u00ed<\/p>\n

<\/p>\n

Je to pom\u011brn\u011b nep\u0159\u00edjemn\u00e9 t\u00e9ma. Z\u00e1mky nejsou p\u0159\u00edli\u0161 popul\u00e1rn\u00ed t\u00e9ma. Chceme, aby to zmizelo. Je to jako chodit k zuba\u0159i. <\/p>\n

<\/p>\n

\"Odemknut\u00ed<\/p>\n

<\/p>\n

    \n
  1. Z\u00e1mky jsou probl\u00e9m pro mnoho lid\u00ed, kte\u0159\u00ed pracuj\u00ed s datab\u00e1zemi a maj\u00ed v\u00edce proces\u016f b\u011b\u017e\u00edc\u00edch sou\u010dasn\u011b. Pot\u0159ebuj\u00ed z\u00e1mky. Tedy dnes v\u00e1m d\u00e1m z\u00e1kladn\u00ed znalosti o z\u00e1mc\u00edch. <\/li>\n
  2. Identifik\u00e1tory transakc\u00ed. To je pom\u011brn\u011b nudn\u00e1 \u010d\u00e1st prezentace, ale je d\u016fle\u017eit\u00e9 ji pochopit.<\/li>\n
  3. D\u00e1le si pov\u00edme o typech z\u00e1mk\u016f. To je dost mechanick\u00e1 \u010d\u00e1st. <\/li>\n
  4. A d\u00e1le se pod\u00edv\u00e1me na n\u011bkter\u00e9 p\u0159\u00edklady z\u00e1mk\u016f. A to bude pom\u011brn\u011b obt\u00ed\u017en\u00e9 na pochopen\u00ed. <\/li>\n<\/ol>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Poj\u010fme si promluvit o z\u00e1mc\u00edch.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Terminologie je u n\u00e1s pom\u011brn\u011b slo\u017eit\u00e1. Kolik z v\u00e1s v\u00ed, odkud je tento \u00faryvek? Dva lid\u00e9. Je to z hry, kter\u00e1 se jmenuje \u201eKolos\u00e1ln\u00ed dobrodru\u017estv\u00ed v jeskyni\u201c. Byla to textov\u00e1 po\u010d\u00edta\u010dov\u00e1 hra v 80. letech, mysl\u00edm. Museli jste vstoupit do jeskyn\u011b, do labyrintu a text se m\u011bnil, ale obsah byl p\u0159itom zhruba stejn\u00fd poka\u017ed\u00e9. Tak si pamatuji tuto hru. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A zde vid\u00edme n\u00e1zvy z\u00e1mk\u016f, kter\u00e9 p\u0159i\u0161ly k n\u00e1m z Oracle. Pou\u017e\u00edv\u00e1me je. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Zde vid\u00edme term\u00edny, kter\u00e9 mi p\u0159\u00edli\u0161 nevyhovuj\u00ed. Nap\u0159\u00edklad SHARE UPDATE EXCLUSIVE. D\u00e1le SHARE RAW EXCLUSIVE. \u0158eknu up\u0159\u00edmn\u011b, tato ozna\u010den\u00ed nejsou p\u0159\u00edli\u0161 srozumiteln\u00e1. Pokus\u00edme se je podrobn\u011bji prozkoumat. N\u011bkter\u00e9 obsahuj\u00ed slovo \u201eshare\u201c, co\u017e znamen\u00e1 \u2013 odd\u011blit se. N\u011bkter\u00e9 obsahuj\u00ed slovo \u201eexclusive\u201c \u2014 exkluzivn\u00ed. N\u011bkter\u00e9 obsahuj\u00ed ob\u011b tato slova. R\u00e1d bych za\u010dal t\u00edm, jak tyto z\u00e1mky funguj\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A tak\u00e9 je velmi d\u016fle\u017eit\u00e9 slovo \u201ep\u0159\u00edstup\u201c \u2014 access. A slova \u201erow\u201c \u2014 \u0159\u00e1dek. T. j. rozd\u011blen\u00ed p\u0159\u00edstupu, rozd\u011blen\u00ed \u0159\u00e1dk\u016f. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Je\u0161t\u011b jedno t\u00e9ma, kter\u00e9 je t\u0159eba pochopit v Postgresu, o kter\u00e9m, bohu\u017eel, nebudu moci mluvit ve sv\u00e9m vystoupen\u00ed, je MVCC. M\u00e1m na toto t\u00e9ma samostatnou prezentaci na sv\u00fdch webov\u00fdch str\u00e1nk\u00e1ch. A pokud si mysl\u00edte, \u017ee tato prezentace je slo\u017eit\u00e1, pak MVCC je pravd\u011bpodobn\u011b moje nejkomplexn\u011bj\u0161\u00ed. A pokud m\u00e1te z\u00e1jem, m\u016f\u017eete se na ni pod\u00edvat na webu. M\u016f\u017eete se pod\u00edvat na video. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Je\u0161t\u011b jedna v\u011bc, kterou mus\u00edme pochopit \u2013 to jsou identifik\u00e1tory transakc\u00ed. Mnoho transakc\u00ed nem\u016f\u017ee fungovat bez unik\u00e1tn\u00edch identifik\u00e1tor\u016f. A zde m\u00e1me vysv\u011btlen\u00ed, co je to transakce. V Postgresu existuj\u00ed dva syst\u00e9my \u010d\u00edslov\u00e1n\u00ed transakc\u00ed. V\u00edm, \u017ee to nen\u00ed p\u0159\u00edli\u0161 hezk\u00e9 \u0159e\u0161en\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u00e9 m\u011bjte na pam\u011bti, \u017ee slidy budou pro vn\u00edm\u00e1n\u00ed pom\u011brn\u011b slo\u017eit\u00e9, tak\u017ee na to, co je zv\u00fdrazn\u011bno \u010dervenou barvou, na to se mus\u00edte soust\u0159edit.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

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

    <\/p>\n

    Pod\u00edv\u00e1me se. \u010cervenou barvou je zv\u00fdrazn\u011bno \u010d\u00edslo transakce. Zde je zobrazena funkce SELECT pg_back. Ta vrac\u00ed mou transakci a ID t\u00e9to transakce. <\/p>\n

    <\/p>\n

    Je\u0161t\u011b jedna v\u011bc, pokud se v\u00e1m tato prezentace l\u00edb\u00ed a chcete ji spustit ve sv\u00e9 datab\u00e1zi, m\u016f\u017eete p\u0159ej\u00edt na tento odkaz, vyzna\u010den\u00fd r\u016f\u017eovou barvou, a st\u00e1hnout SQL pro tuto prezentaci. A m\u016f\u017eete ji jednodu\u0161e spustit ve va\u0161em PSQL a v\u0161e se okam\u017eit\u011b objev\u00ed na va\u0161em displeji. Nebude obsahovat barvy, ale alespo\u0148 ji budeme moci vid\u011bt.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    V tomto p\u0159\u00edpad\u011b vid\u00edme ID transakce. To je \u010d\u00edslo, kter\u00e9 jsme j\u00ed p\u0159i\u0159adili. A existuje tak\u00e9 jin\u00fd typ ID transakce v Postgresu, kter\u00e9 se naz\u00fdv\u00e1 virtu\u00e1ln\u00ed ID transakce.<\/p>\n

    <\/p>\n

    A mus\u00edme tomu rozum\u011bt. To je velmi d\u016fle\u017eit\u00e9, jinak nebudeme schopni pochopit zamyk\u00e1n\u00ed v Postgresu.<\/p>\n

    <\/p>\n

    Virtu\u00e1ln\u00ed ID transakce je ID transakce, kter\u00e9 neobsahuje trval\u00e9 hodnoty. Nap\u0159\u00edklad pokud spust\u00edm p\u0159\u00edkaz SELECT, pravd\u011bpodobn\u011b nebudu m\u011bnit datab\u00e1zi, nic nezamknu. Proto, kdy\u017e spou\u0161t\u00edme jednoduch\u00fd SELECT, t\u00e9to transakci ned\u00e1v\u00e1me trval\u00e9 ID. D\u00e1v\u00e1me j\u00ed pouze virtu\u00e1ln\u00ed ID.<\/p>\n

    <\/p>\n

    A to zvy\u0161uje v\u00fdkon Postgresu, zlep\u0161uje mo\u017enosti pro \u00fadr\u017ebu, tak\u017ee virtu\u00e1ln\u00ed ID transakce se skl\u00e1d\u00e1 ze dvou \u010d\u00edsel. Prvn\u00ed \u010d\u00edslo p\u0159ed lom\u00edtkem je ID backendu. A napravo vid\u00edme jednodu\u0161e \u010d\u00edta\u010d.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Proto, pokud spou\u0161t\u00edm dotaz, \u0159\u00edk\u00e1, \u017ee ID backend je 2. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud spou\u0161t\u00edm s\u00e9rii takov\u00fdch transakc\u00ed, vid\u00edme, \u017ee \u010d\u00edta\u010d se poka\u017ed\u00e9 zvy\u0161uje, kdy\u017e spou\u0161t\u00edm dotaz. Nap\u0159\u00edklad, kdy\u017e spou\u0161t\u00edm dotaz 2\/10, 2\/11, 2\/12 atd. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Vezm\u011bte na v\u011bdom\u00ed, \u017ee zde jsou dv\u011b sloupce. Vlevo vid\u00edme virtu\u00e1ln\u00ed ID transakce \u2013 2\/12. A napravo m\u00e1me trval\u00e9 ID transakce. A toto pole je pr\u00e1zdn\u00e9. A tato transakce nem\u011bn\u00ed datab\u00e1zi. Proto j\u00ed nep\u0159id\u011bluji trval\u00e9 ID transakce.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Jakmile spust\u00edm p\u0159\u00edkaz analyzovat (ANALYZE), stejn\u00fd dotaz mi vr\u00e1t\u00ed trval\u00e9 ID transakce. Pod\u00edvejte se, jak se to zm\u011bnilo. D\u0159\u00edve jsem nem\u011bl toto ID, nyn\u00ed se objevilo.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee, zde je dal\u0161\u00ed dotaz, dal\u0161\u00ed transakce. Virtu\u00e1ln\u00ed \u010d\u00edslo transakce \u2013 2\/13. A pokud po\u017e\u00e1d\u00e1m o trval\u00e9 ID transakce, kdy\u017e spust\u00edm dotaz, dostanu ho. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee je\u0161t\u011b jednou. M\u00e1me virtu\u00e1ln\u00ed ID transakce a trval\u00e9 ID transakce. Jen pochopte tuto v\u011bc, abyste porozum\u011bli chov\u00e1n\u00ed Postgresu.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    P\u0159ejdeme k t\u0159et\u00ed \u010d\u00e1sti. Tady si projdeme r\u016fzn\u00e9 typy z\u00e1mk\u016f v Postgresu. Nen\u00ed to p\u0159\u00edli\u0161 zaj\u00edmav\u00e9. Posledn\u00ed \u010d\u00e1st bude mnohem zaj\u00edmav\u011bj\u0161\u00ed. Ale mus\u00edme se pod\u00edvat na z\u00e1kladn\u00ed v\u011bci, jinak nepochop\u00edme, co bude d\u00e1l. <\/p>\n

    <\/p>\n

    Projdem si tuto \u010d\u00e1st, pod\u00edv\u00e1me se na ka\u017ed\u00fd typ z\u00e1mku. A uk\u00e1\u017eu v\u00e1m p\u0159\u00edklady, jak se nastavuj\u00ed, jak funguj\u00ed, a uk\u00e1\u017eu v\u00e1m n\u011bkter\u00e9 dotazy, kter\u00e9 m\u016f\u017eete pou\u017e\u00edt, abyste zjistili, jak z\u00e1mky funguj\u00ed v Postgresu.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Abychom vytvo\u0159ili dotaz a zjistili, co se d\u011bje v Postgresu, mus\u00edme prov\u00e9st dotaz na syst\u00e9mov\u00fd pohled. V tomto p\u0159\u00edpad\u011b m\u00e1me ozna\u010deno \u010derven\u011b pg_lock. Pg_lock je syst\u00e9mov\u00e1 tabulka, kter\u00e1 n\u00e1m \u0159\u00edk\u00e1, jak\u00e9 z\u00e1mky jsou nyn\u00ed pou\u017e\u00edv\u00e1ny v Postgresu.<\/p>\n

    <\/p>\n

    Nicm\u00e9n\u011b mi d\u011bl\u00e1 velk\u00e9 pot\u00ed\u017ee uk\u00e1zat v\u00e1m pg_lock samotn\u00fd, proto\u017ee je to docela slo\u017eit\u00e9. Proto jsem vytvo\u0159il pohled, kter\u00fd ukazuje pg_locks. A tak\u00e9 d\u011bl\u00e1 pro m\u011b ur\u010ditou pr\u00e1ci, kter\u00e1 mi pom\u00e1h\u00e1 l\u00e9pe rozum\u011bt. Tedy vylu\u010duje m\u00e9 z\u00e1mky, moji vlastn\u00ed relaci atd. To je prost\u011b standardn\u00ed SQL a l\u00e9pe to ukazuje, co se d\u011bje. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Dal\u0161\u00ed probl\u00e9m je, \u017ee tento pohled je velmi \u0161irok\u00fd, tak\u017ee mus\u00edm vytvo\u0159it druh\u00fd - lockview2.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed A ten mi ukazuje dal\u0161\u00ed sloupce z tabulky. A je\u0161t\u011b jeden, kter\u00fd mi ukazuje ostatn\u00ed sloupce. Je to dost slo\u017eit\u00e9, tak\u017ee jsem se sna\u017eil to p\u0159edstavit co nejjednodu\u0161eji. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee jsme vytvo\u0159ili tabulku, kter\u00e1 se jmenuje Lockdemo. A vytvo\u0159ili jsme tam jeden \u0159\u00e1dek. To je na\u0161e testovac\u00ed tabulka. A vytvo\u0159\u00edme sekce, abychom v\u00e1m uk\u00e1zali p\u0159\u00edklady z\u00e1mk\u016f. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee jeden \u0159\u00e1dek, jeden sloupec. Prvn\u00ed typ z\u00e1mku se naz\u00fdv\u00e1 ACCESS SHARE. Je to nejm\u00e9n\u011b omezuj\u00edc\u00ed z\u00e1mek. To znamen\u00e1, \u017ee t\u00e9m\u011b\u0159 nekonfliktuje s ostatn\u00edmi z\u00e1mky.<\/p>\n

    <\/p>\n

    A pokud chceme explicitn\u011b ur\u010dit zablokov\u00e1n\u00ed, spust\u00edme p\u0159\u00edkaz \u201elock table\u201c. A ten jasn\u011b zablokuje, tj. v re\u017eimu ACCESS SHARE spou\u0161t\u00edme lock table. A pokud spust\u00edm PSQL na pozad\u00ed, tak takto spou\u0161t\u00edm druhou relaci z m\u00e9 prvn\u00ed relace. Tj. co ud\u011bl\u00e1m zde? P\u0159ech\u00e1z\u00edm k jin\u00e9 relaci a \u0159\u00edk\u00e1m j\u00ed \u201euk\u00e1\u017e mi lockview pro tento dotaz\u201c. A zde m\u00e1m AccessShareLock v t\u00e9to tabulce. To je p\u0159esn\u011b to, co jsem \u017e\u00e1dal. A on \u0159\u00edk\u00e1, \u017ee zablokov\u00e1n\u00ed bylo p\u0159id\u011bleno. Velmi jednodu\u0161e. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Pokra\u010dujme, pokud se pod\u00edv\u00e1me na druh\u00fd sloupec, tak tam nic nen\u00ed. Jsou pr\u00e1zdn\u00e9. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud spust\u00edm p\u0159\u00edkaz \u201eSELECT\u201c, tak to je implicitn\u00ed (explicitn\u00ed) zp\u016fsob, jak po\u017e\u00e1dat o AccessShareLock. Proto uvoln\u00edm svou tabulku a spust\u00edm dotaz, a dotaz vr\u00e1t\u00ed n\u011bkolik \u0159\u00e1dk\u016f. A v jednom z \u0159\u00e1dk\u016f vid\u00edme AccessShareLock. Tak\u017ee SELECT vyvol\u00e1v\u00e1 AccessShareLock v tabulce. A prakticky to nekonfliktuje s ni\u010d\u00edm, proto\u017ee to je z\u00e1mek n\u00edzk\u00e9 \u00farovn\u011b. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Co kdy\u017e spust\u00edm SELECT a budu m\u00edt t\u0159i r\u016fzn\u00e9 tabulky? D\u0159\u00edve jsem spou\u0161t\u011bl pouze jednu tabulku, nyn\u00ed spou\u0161t\u00edm t\u0159i: pg_class, pg_namespace a pg_attribute.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A nyn\u00ed, kdy\u017e se d\u00edv\u00e1m na dotaz, vid\u00edm 9 AccessShareLocks ve t\u0159ech tabulk\u00e1ch. Pro\u010d? T\u0159i tabulky jsou zv\u00fdrazn\u011bny mod\u0159e: pg_attribute, pg_class, pg_namespace. Ale tak\u00e9 m\u016f\u017eete vid\u011bt, \u017ee v\u0161echny indexy, kter\u00e9 jsou definov\u00e1ny p\u0159es tyto tabulky, tak\u00e9 maj\u00ed AccessShareLock.<\/p>\n

    <\/p>\n

    A to je z\u00e1mek, kter\u00fd prakticky nekonfliktuje s jin\u00fdmi. A v\u0161e, co d\u011bl\u00e1, je, \u017ee n\u00e1m jednodu\u0161e nedovol\u00ed uvolnit tabulku, dokud ji vyb\u00edr\u00e1me. D\u00e1v\u00e1 to smysl. Tj. pokud vyb\u00edr\u00e1me tabulku, tak v tu chv\u00edli zmiz\u00ed, to by bylo \u0161patn\u00e9, tak\u017ee AccessShare je z\u00e1mek n\u00edzk\u00e9 \u00farovn\u011b, kter\u00fd n\u00e1m \u0159\u00edk\u00e1 \"neodstra\u0148ujte tuto tabulku, dokud na n\u00ed pracuji\".<\/strong>. V podstat\u011b, to je v\u0161e, co d\u011bl\u00e1. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    ROW SHARE \u2013 tento z\u00e1mek je trochu odli\u0161n\u00fd. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Poj\u010fme si vz\u00edt p\u0159\u00edklad. SELECT ROW SHARE zp\u016fsob zablokov\u00e1n\u00ed ka\u017ed\u00e9ho \u0159\u00e1dku jednotliv\u011b.<\/strong>. Tak\u017ee nikdo je nem\u016f\u017ee odstranit nebo zm\u011bnit, dokud je sledujeme. <\/p>\n

    <\/p>\n

    \"Odemknut\u00edTak\u017ee, co d\u011bl\u00e1 SHARE LOCK? Vid\u00edme, \u017ee ID transakce je 681 pro SELECT. A to je zaj\u00edmav\u00e9. Co se tady stalo? Poprv\u00e9 vid\u00edme \u010d\u00edslo v poli \u201eLock\u201c. Bereme ID transakce a to \u0159\u00edk\u00e1, \u017ee ji blokuje v exkluzivn\u00edm re\u017eimu. V\u0161echno, co d\u011bl\u00e1, \u0159\u00edk\u00e1, \u017ee m\u00e1m \u0159\u00e1dek, kter\u00fd je technicky blokov\u00e1n n\u011bkde v tabulce. Ale ne\u0159\u00edk\u00e1, kde konkr\u00e9tn\u011b. O n\u011bco pozd\u011bji se na to pod\u00edv\u00e1me podrobn\u011bji.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tady \u0159\u00edk\u00e1me, \u017ee blokace je pou\u017e\u00edv\u00e1na n\u00e1mi.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee exkluzivn\u00ed blokace explicitn\u011b \u0159\u00edk\u00e1, \u017ee je exkluzivn\u00ed. A tak\u00e9, pokud vyma\u017eete \u0159\u00e1dek v t\u00e9to tabulce, tak se to stane, jak vid\u00edte. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    SHARE EXCLUSIVE \u2013 to je del\u0161\u00ed blokace.<\/strong><\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    To (ANALYZE) je p\u0159\u00edkaz analyz\u00e1toru, kter\u00fd bude pou\u017eit.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    SHARE LOCK \u2013 m\u016f\u017eete explicitn\u011b blokovat v re\u017eimu sd\u00edlen\u00ed.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    M\u016f\u017eete tak\u00e9 vytvo\u0159it unik\u00e1tn\u00ed index. A tam m\u016f\u017eete vid\u011bt SHARE LOCK, kter\u00fd je jeho sou\u010d\u00e1st\u00ed. T\u00edm blokuje tabulku a nastavuje na ni blokaci SHARE LOCK.<\/p>\n

    <\/p>\n

    Ve v\u00fdchoz\u00edm nastaven\u00ed SHARE LOCK na tabulce znamen\u00e1, \u017ee jin\u00ed lid\u00e9 mohou \u010d\u00edst tabulku, ale nikdo ji nem\u016f\u017ee m\u011bnit. A to se stane, kdy\u017e vytvo\u0159\u00edte unik\u00e1tn\u00ed index.<\/strong><\/p>\n

    <\/p>\n

    Pokud vytvo\u0159\u00edm unik\u00e1tn\u00ed concurrently index, budu m\u00edt jin\u00fd typ blokace, proto\u017ee, jak si pamatujete, pou\u017eit\u00ed concurrently index\u016f sni\u017euje po\u017eadavek na blokaci. A pokud pou\u017eiji norm\u00e1ln\u00ed blokaci, norm\u00e1ln\u00ed index, t\u00edm zabr\u00e1n\u00edm z\u00e1pisu do indexu tabulky b\u011bhem jeho vytv\u00e1\u0159en\u00ed. Pokud pou\u017eiji concurrently index, mus\u00edm pou\u017e\u00edt jin\u00fd typ blokace. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    SHARE ROW EXCLUSIVE \u2013 op\u011bt ji lze nastavit explicitn\u011b.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Nebo m\u016f\u017eeme vytvo\u0159it pravidlo, tj. vz\u00edt n\u011bjak\u00fd konkr\u00e9tn\u00ed p\u0159\u00edpad, p\u0159i kter\u00e9m bude pou\u017eita.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    EXCLUSIVE blokace znamen\u00e1, \u017ee nikdo jin\u00fd nem\u016f\u017ee m\u011bnit tabulku. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tady vid\u00edme r\u016fzn\u00e9 typy blokac\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    ACCESS EXCLUSIVE, nap\u0159\u00edklad, to je p\u0159\u00edkaz blokace. Nap\u0159\u00edklad, pokud d\u011bl\u00e1te CLUSTER table<\/code>, to znamen\u00e1, \u017ee nikdo nem\u016f\u017ee zapisovat dovnit\u0159. A blokuje nejen samotnou tabulku, ale tak\u00e9 indexy.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    To je druh\u00e1 str\u00e1nka blokace ACCESS EXCLUSIVE, kde vid\u00edme konkr\u00e9tn\u011b, co blokuje v tabulce. Blokuje jednotliv\u00e9 \u0159\u00e1dky tabulky, co\u017e je dost zaj\u00edmav\u00e9.<\/p>\n

    <\/p>\n

    Tohle jsou v\u0161echny z\u00e1kladn\u00ed informace, kter\u00e9 jsem cht\u011bl poskytnout. Mluvili jsme o z\u00e1mc\u00edch, o ID transakc\u00edch, o virtu\u00e1ln\u00edch ID transakc\u00edch, o trval\u00fdch ID transakc\u00edch. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A te\u010f si projdeme p\u0159\u00edklady z\u00e1mk\u016f. To je nejzaj\u00edmav\u011bj\u0161\u00ed \u010d\u00e1st. Pod\u00edv\u00e1me se na velmi zaj\u00edmav\u00e9 p\u0159\u00edpady. Moj\u00ed \u00falohou v t\u00e9to prezentaci je poskytnout v\u00e1m lep\u0161\u00ed p\u0159edstavu o tom, co Postgres vlastn\u011b d\u011bl\u00e1, kdy\u017e se sna\u017e\u00ed blokovat ur\u010dit\u00e9 v\u011bci. Mysl\u00edm, \u017ee velmi dob\u0159e um\u00ed blokovat jednotliv\u00e9 \u010d\u00e1sti. <\/p>\n

    <\/p>\n

    Pod\u00edvejme se na konkr\u00e9tn\u00ed p\u0159\u00edklady.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Za\u010dneme s tabulkami a s jedn\u00edm \u0159\u00e1dkem v tabulce. Kdy\u017e n\u011bco vlo\u017e\u00edm, zobraz\u00ed se mi ExclusiveLock, ID transakce a ExclusiveLock na tabulce. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A co se stane, pokud vlo\u017e\u00edm dal\u0161\u00ed dva \u0159\u00e1dky? A nyn\u00ed m\u00e1me v na\u0161\u00ed tabulce t\u0159i \u0159\u00e1dky. Vlo\u017eil jsem jeden \u0159\u00e1dek a dostal tohle na v\u00fdstupu. A pokud vlo\u017e\u00edm dal\u0161\u00ed dva \u0159\u00e1dky, co je tu podivn\u00e9ho? Je tu podivnost, proto\u017ee jsem p\u0159idal t\u0159i \u0159\u00e1dky do t\u00e9to tabulky, ale st\u00e1le m\u00e1m dva \u0159\u00e1dky v tabulce z\u00e1mk\u016f. A to je v podstat\u011b z\u00e1kladn\u00ed chov\u00e1n\u00ed Postgres.<\/p>\n

    <\/p>\n

    Mnoz\u00ed si mysl\u00ed, \u017ee pokud v datab\u00e1zi blokujete 100 \u0159\u00e1dk\u016f, bude t\u0159eba vytvo\u0159it 100 vstup\u016f z\u00e1mk\u016f. Pokud budu blokovat najednou 1 000 \u0159\u00e1dk\u016f, pak budu pot\u0159ebovat 1 000 takov\u00fdch po\u017eadavk\u016f. A pokud pot\u0159ebuji zablokovat milion nebo miliardu. Ale pokud to ud\u011bl\u00e1me takto, nebude to fungovat dob\u0159e. Pokud jste pou\u017e\u00edvali syst\u00e9m, kter\u00fd vytv\u00e1\u0159\u00ed vstupy z\u00e1mk\u016f pro ka\u017ed\u00fd jednotliv\u00fd \u0159\u00e1dek, vid\u00edte, \u017ee je to slo\u017eit\u00e9. Proto\u017ee mus\u00edte ur\u010dit tabulku z\u00e1mk\u016f, kter\u00e1 m\u016f\u017ee p\u0159et\u00ed\u017eit, ale Postgres to ned\u011bl\u00e1. <\/p>\n

    <\/p>\n

    A na tomto sn\u00edmku je velmi d\u016fle\u017eit\u00e9, \u017ee zde je jasn\u011b demonstrov\u00e1no, \u017ee existuje dal\u0161\u00ed syst\u00e9m, kter\u00fd funguje uvnit\u0159 MVCC, kter\u00fd blokuje jednotliv\u00e9 \u0159\u00e1dky. Tak\u017ee kdy\u017e blokujete miliardy \u0159\u00e1dk\u016f, Postgres nevytv\u00e1\u0159\u00ed miliardu jednotliv\u00fdch p\u0159\u00edkaz\u016f na blokaci. A to m\u00e1 velmi pozitivn\u00ed vliv na v\u00fdkon. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A co aktualizac\u00ed? Pr\u00e1v\u011b aktualizuji \u0159adu a m\u016f\u017eete si v\u0161imnout, \u017ee okam\u017eit\u011b vykonal dv\u011b r\u016fzn\u00e9 operace. Zablokoval tabulku, ale tak\u00e9 zablokoval index. A musel zablokovat index, proto\u017ee na t\u00e9to tabulce existuj\u00ed unik\u00e1tn\u00ed omezen\u00ed. A chceme si b\u00fdt jisti, \u017ee nikdo ho nem\u011bn\u00ed, proto ho blokujeme.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A co se stane, kdy\u017e chci aktualizovat dva \u0159\u00e1dky? A vid\u00edme, \u017ee se chov\u00e1 stejn\u011b. Prov\u00e1d\u00edme dvakr\u00e1t v\u00edce aktualizac\u00ed, ale p\u0159esn\u011b stejn\u00e9 mno\u017estv\u00ed blokovan\u00fdch \u0159\u00e1dk\u016f. <\/p>\n

    <\/p>\n

    Pokud v\u00e1s zaj\u00edm\u00e1, jak to Postgres d\u011bl\u00e1, mus\u00edte si poslechnout moje p\u0159edn\u00e1\u0161ky o MVCC, abyste se dozv\u011bd\u011bli, jak Postgres intern\u011b zna\u010dkuje tyto \u0159\u00e1dky, kter\u00e9 m\u011bn\u00ed. A Postgres m\u00e1 zp\u016fsob, jak\u00fdm to d\u011bl\u00e1, ale ned\u011bl\u00e1 to na \u00farovni blokov\u00e1n\u00ed tabulek, d\u011bl\u00e1 to na ni\u017e\u0161\u00ed a efektivn\u011bj\u0161\u00ed \u00farovni.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A co kdy\u017e chci n\u011bco odstranit? Pokud odstran\u00edm nap\u0159\u00edklad jeden \u0159\u00e1dek a m\u00e1m st\u00e1le sv\u00e9 dv\u011b z\u00e1lo\u017en\u00ed blokace, a i kdy\u017e bych je v\u0161echny cht\u011bl odstranit, st\u00e1le tam budou.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud chci vlo\u017eit 1 000 \u0159\u00e1dk\u016f, a pak bu\u010f odstranit, nebo p\u0159idat 1 000 \u0159\u00e1dk\u016f, ty jednotliv\u00e9 \u0159\u00e1dky, kter\u00e9 p\u0159id\u00e1v\u00e1m nebo m\u011bn\u00edm, se zde nezapisuj\u00ed. Zapisuj\u00ed se na ni\u017e\u0161\u00ed \u00farovni uvnit\u0159 samotn\u00e9ho \u0159\u00e1dku. A b\u011bhem p\u0159edn\u00e1\u0161ky o MVCC jsem o tom mluvil podrobn\u011b. Ale je velmi d\u016fle\u017eit\u00e9, kdy\u017e analyzujete blokace, ujistit se, \u017ee m\u00e1te blokaci na \u00farovni tabulky a \u017ee zde nevid\u00edte, jak se zapisuj\u00ed jednotliv\u00e9 \u0159\u00e1dky. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A co explicitn\u00ed blokace?<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Pokud stisknu \"aktualizovat\", m\u00e1m dv\u011b blokovan\u00e9 \u0159\u00e1dky. A pokud je v\u0161echny vyberu a stisknu \"aktualizovat v\u0161e\", st\u00e1le mi z\u016fst\u00e1vaj\u00ed dv\u011b blokace. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Nevytv\u00e1\u0159\u00edme zvl\u00e1\u0161tn\u00ed z\u00e1znamy pro ka\u017ed\u00fd jednotliv\u00fd \u0159\u00e1dek. Proto\u017ee pak kles\u00e1 v\u00fdkon, m\u016f\u017ee jich b\u00fdt p\u0159\u00edli\u0161 mnoho. A m\u016f\u017eeme se dostat do nep\u0159\u00edjemn\u00e9 situace. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A to sam\u00e9, pokud d\u011bl\u00e1me shared, m\u016f\u017eeme to d\u011blat v\u0161e 30kr\u00e1t. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Obnovujeme na\u0161i tabulku, v\u0161echno ma\u017eeme, pak znovu vkl\u00e1d\u00e1me jeden \u0159\u00e1dek.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Dal\u0161\u00ed typ chov\u00e1n\u00ed, kter\u00fd vid\u00edte v Postgres, je velmi dob\u0159e zn\u00e1m\u00e9 a \u017e\u00e1douc\u00ed chov\u00e1n\u00ed \u2013 m\u016f\u017eete prov\u00e1d\u011bt update nebo select. A m\u016f\u017eete to d\u011blat sou\u010dasn\u011b. Select neblokuje update a naopak. \u0158\u00edk\u00e1me, \u017ee \u010dten\u00e1\u0159 neblokuje toho, kdo p\u00ed\u0161e, a ten, kdo p\u00ed\u0161e, neblokuje \u010dten\u00e1\u0159e. <\/p>\n

    <\/p>\n

    Uk\u00e1\u017eu v\u00e1m p\u0159\u00edklad tohoto. Te\u010f provedu v\u00fdb\u011br. Pot\u00e9 provedeme INSERT. A vy pak uvid\u00edte \u2013 694. Uvid\u00edte ID transakce, kter\u00e1 provedla tuto vlo\u017een\u00ed. A takto to funguje. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud se te\u010f pod\u00edv\u00e1m na sv\u00e9 backend ID, je to nyn\u00ed \u2013 695. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A mohu vid\u011bt, \u017ee 695 se objevuje v m\u00e9 tabulce. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud prov\u00e1d\u00edm aktualizaci takto, dost\u00e1v\u00e1m jin\u00fd p\u0159\u00edpad. V tomto p\u0159\u00edpad\u011b je 695 exkluzivn\u00ed z\u00e1mek, a u update je to stejn\u00e9 chov\u00e1n\u00ed, ale mezi nimi nevznik\u00e1 konflikt, co\u017e je dost neobvykl\u00e9. <\/p>\n

    <\/p>\n

    A m\u016f\u017eete si v\u0161imnout, \u017ee naho\u0159e \u2013 je to ShareLock, a dole \u2013 je to ExclusiveLock. A ob\u011b transakce byly \u00fasp\u011b\u0161n\u00e9.<\/p>\n

    <\/p>\n

    A je pot\u0159eba poslouchat m\u016fj projev na MVCC, abyste pochopili, jak se to d\u011bje. Ale to je ilustra\u010dn\u00ed p\u0159\u00edklad toho, co m\u016f\u017eete d\u011blat sou\u010dasn\u011b, tj. z\u00e1rove\u0148 prov\u00e1d\u011bt SELECT a UPDATE.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Poj\u010fme resetovat a ud\u011blat je\u0161t\u011b jednou jednu operaci. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Pokud se pokus\u00edte sou\u010dasn\u011b spustit dva update na stejn\u00e9m \u0159\u00e1dku, bude to zablokov\u00e1no. A pamatujte, \u0159\u00edkal jsem, \u017ee \u010dten\u00e1\u0159 neblokuje autora, a autor neblokuje \u010dten\u00e1\u0159e, ale jeden autor blokuje druh\u00e9ho autora. Tj. nem\u016f\u017eeme m\u00edt dva lidi, kte\u0159\u00ed sou\u010dasn\u011b aktualizuj\u00ed stejn\u00fd \u0159\u00e1dek. Mus\u00edme po\u010dkat, a\u017e n\u011bkdo z nich dokon\u010d\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A abych to ilustroval, pod\u00edv\u00e1m se na tabulku Lockdemo. A pod\u00edv\u00e1me se na jeden \u0159\u00e1dek. Na transakci 698.<\/p>\n

    <\/p>\n

    Aktualizovali jsme to na 2. 699 \u2013 to je prvn\u00ed aktualizace. A prob\u011bhla \u00fasp\u011b\u0161n\u011b, nebo se nach\u00e1z\u00ed v \u010dekaj\u00edc\u00ed transakci a \u010dek\u00e1 na na\u0161e potvrzen\u00ed nebo zru\u0161en\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Ale pod\u00edvejte se na n\u011bco jin\u00e9ho \u2013 2\/51 \u2013 to je na\u0161e prvn\u00ed transakce, na\u0161e prvn\u00ed relace. 3\/112 \u2013 to je druh\u00fd po\u017eadavek, kter\u00fd se objevil naho\u0159e a kter\u00fd zm\u011bnil tuto hodnotu na 3. A pokud si v\u0161imnete, tak horn\u00ed \u010d\u00e1st se zablokovala sama o sob\u011b, kter\u00e1 byla 699. Ale 3\/112 neposkytla z\u00e1mek. V sloupci Lock_mode je naps\u00e1no, \u017ee \u010dek\u00e1. \u010cek\u00e1 na 699. A pokud se pod\u00edv\u00e1te, kde je 699, je v\u00fd\u0161e. A co ud\u011blala prvn\u00ed relace? Vytvo\u0159ila exkluzivn\u00ed z\u00e1mek na sv\u00e9 vlastn\u00ed transak\u010dn\u00ed ID. Tak to Postgres d\u011bl\u00e1. Zablokuje sv\u00e9 vlastn\u00ed transak\u010dn\u00ed ID. A pokud chcete \u010dekat, a\u017e n\u011bkdo potvrd\u00ed nebo zru\u0161\u00ed, mus\u00edte \u010dekat, a\u017e bude \u010dekaj\u00edc\u00ed transakce. A proto m\u016f\u017eeme vid\u011bt zvl\u00e1\u0161tn\u00ed \u0159\u00e1dku. <\/p>\n

    <\/p>\n

    Pod\u00edvejme se znovu. Vlevo vid\u00edme na\u0161e zpracov\u00e1vac\u00ed ID. Ve druh\u00e9m sloupci vid\u00edme na\u0161e virtu\u00e1ln\u00ed ID transakce a ve t\u0159et\u00edm vid\u00edme lock_type. Co to znamen\u00e1? V podstat\u011b \u0159\u00edk\u00e1, \u017ee zablokuje transak\u010dn\u00ed ID. Ale v\u0161imn\u011bte si, \u017ee ve v\u0161ech \u0159\u00e1dc\u00edch dole je naps\u00e1no relation. A proto m\u00e1te dva typy z\u00e1mk\u016f v tabulce. Existuje z\u00e1mek relation. A tak\u00e9 existuje z\u00e1mek transactionid, kde zablokujete sami sebe, to je p\u0159esn\u011b to, co se d\u011bje v prvn\u00edm \u0159\u00e1dku nebo na sam\u00e9m dn\u011b, kde je transationid, kde \u010dek\u00e1me, aby 699 dokon\u010dil svou operaci. <\/p>\n

    <\/p>\n

    Sleduji, co se tady d\u011bje. A tady se sou\u010dasn\u011b d\u011bj\u00ed dv\u011b v\u011bci. D\u00edv\u00e1te se na z\u00e1mek podle transak\u010dn\u00edho ID v prvn\u00edm \u0159\u00e1dku, kter\u00fd se blokuje s\u00e1m. A zablokuje se s\u00e1m, aby donutil lidi \u010dekat. <\/p>\n

    <\/p>\n

    Pokud se pod\u00edv\u00e1te na 6. \u0159\u00e1dek, tak tam je stejn\u00fd z\u00e1znam jako v prvn\u00edm. A proto je transakce 699 blokov\u00e1na. 700 se tak\u00e9 blokuje sama. A pot\u00e9 ve spodn\u00edm \u0159\u00e1dku uvid\u00edte, \u017ee \u010dek\u00e1me, a\u017e 699 dokon\u010d\u00ed svou operaci.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

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

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    M\u016f\u017eete vid\u011bt, \u017ee je to 0\/10. A to je \u010d\u00edslo str\u00e1nky, a tak\u00e9 offset tohoto konkr\u00e9tn\u00edho \u0159\u00e1dku. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A vid\u00edte, \u017ee se st\u00e1v\u00e1 0\/11, kdy\u017e aktualizujeme. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Ale ve skute\u010dnosti \u2013 je to 0\/10, proto\u017ee prob\u00edh\u00e1 \u010dek\u00e1n\u00ed na tuto operaci. M\u00e1me mo\u017enost pod\u00edvat se, co je ten \u0159\u00e1dek, kter\u00fd \u010dek\u00e1m, aby potvrdil. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Jakmile jsme to potvrdili a klikli na commit, a kdy\u017e se aktualizace dokon\u010dila, dostali jsme tohle znovu. Transakce 700 je jedin\u00e9 zablokov\u00e1n\u00ed, na nikdo dal\u0161\u00ed ne\u010dek\u00e1, proto\u017ee byla ulo\u017eena. \u010cek\u00e1 pouze, a\u017e transakce skon\u010d\u00ed. Jakmile 699 skon\u010d\u00ed, u\u017e na nic ne\u010dek\u00e1me. A nyn\u00ed transakce 700 \u0159\u00edk\u00e1, \u017ee je v\u0161e v po\u0159\u00e1dku, \u017ee m\u00e1 v\u0161echna zablokov\u00e1n\u00ed, kter\u00e1 pot\u0159ebuje, ve v\u0161ech povolen\u00fdch tabulk\u00e1ch. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A abychom v\u0161e je\u0161t\u011b zkomplikovali, vytv\u00e1\u0159\u00edme dal\u0161\u00ed pohled, kter\u00fd n\u00e1m tentokr\u00e1t poskytne hierarchii. Ne\u010dek\u00e1m, \u017ee tento dotaz pochop\u00edte. Ale to n\u00e1m poskytne jasn\u011bj\u0161\u00ed pohled na to, co se d\u011bje. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Toto je rekurzivn\u00ed pohled, kter\u00fd m\u00e1 tak\u00e9 dal\u0161\u00ed \u010d\u00e1st. A pak znovu vrac\u00ed v\u0161e dohromady. Poj\u010fme to pou\u017e\u00edt. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Co kdybychom provedli t\u0159i soub\u011b\u017en\u00e9 aktualizace a \u0159ekli, \u017ee \u0159ada m\u00e1 te\u010f hodnotu t\u0159i. A zm\u011bn\u00edme 3 na 4. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A tady vid\u00edme 4. A transak\u010dn\u00ed ID 702. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pak zm\u011bn\u00edm 4 na 5. A 5 na 6, a 6 na 7. A j\u00e1 t\u0159\u00edd\u00edm frontu lid\u00ed, kte\u0159\u00ed budou \u010dekat na dokon\u010den\u00ed t\u00e9to jedn\u00e9 transakce.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A v\u0161echno je jasn\u00e9. Kter\u00fd je prvn\u00ed \u0159ada? To je 702. To je transak\u010dn\u00ed ID, kter\u00e9 p\u016fvodn\u011b nastavilo tuto hodnotu. A co m\u00e1m naps\u00e1no ve sloupci Granted? M\u00e1m tam zna\u010dky f<\/code>. To jsou moje aktualizace, kter\u00e9 (5, 6, 7) nemohou b\u00fdt schv\u00e1leny, proto\u017ee \u010dek\u00e1me, a\u017e transak\u010dn\u00ed ID 702 skon\u010d\u00ed. Tam m\u00e1me zablokov\u00e1n\u00ed transak\u010dn\u00edho ID. A m\u00e1me 5 transak\u010dn\u00edch zablokov\u00e1n\u00ed ID.<\/p>\n

    <\/p>\n

    A pokud se pod\u00edv\u00e1te na 704, na 705, tak tam je\u0161t\u011b nen\u00ed nic naps\u00e1no, proto\u017ee je\u0161t\u011b nev\u011bd\u00ed, co se d\u011bje. Jen p\u00ed\u0161\u00ed, \u017ee nemaj\u00ed pon\u011bt\u00ed, co se d\u011bje. A prost\u011b usnou, proto\u017ee \u010dekaj\u00ed, a\u017e n\u011bkdo dokon\u010d\u00ed a probud\u00ed je, kdy\u017e se objev\u00ed mo\u017enost zm\u011bnit \u0159adu. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Takto to vypad\u00e1. Je jasn\u00e9, \u017ee v\u0161ichni \u010dekaj\u00ed na 12. \u0159\u00e1dek. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    To je to, co jsme vid\u011bli zde. Tady je 0\/12. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Jakmile je prvn\u00ed transakce schv\u00e1lena, m\u016f\u017eete zde vid\u011bt, jak funguje hierarchie. A te\u010f to v\u0161echno d\u00e1v\u00e1 smysl. V\u0161ichni se vy\u010dist\u00ed. A ve skute\u010dnosti st\u00e1le \u010dekaj\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Toto se d\u011bje. 702 se zavazuje. A nyn\u00ed 703 z\u00edsk\u00e1v\u00e1 toto zablokov\u00e1n\u00ed \u0159\u00e1dku, a pak 704 za\u010d\u00edn\u00e1 \u010dekat, a\u017e se 703 zav\u00e1\u017ee. A 705 to tak\u00e9 o\u010dek\u00e1v\u00e1. A kdy\u017e se to v\u0161echno dokon\u010d\u00ed, vy\u010dist\u00ed se samy. A r\u00e1d bych upozornil na to, \u017ee se v\u0161ichni postav\u00ed do fronty. A to vypad\u00e1 velmi podobn\u011b jako situace v dopravn\u00ed z\u00e1cp\u011b, kdy\u017e v\u0161ichni \u010dekaj\u00ed na prvn\u00ed auto. Prvn\u00ed auto se zastavilo a v\u0161ichni se postavili do dlouh\u00e9 \u0159ady. Pak se pohnulo, pak m\u016f\u017ee dal\u0161\u00ed auto projet vp\u0159ed a z\u00edskat sv\u00e9 zablokov\u00e1n\u00ed, atd. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud v\u00e1m to p\u0159ipadalo nedostate\u010dn\u011b slo\u017eit\u00e9, nyn\u00ed si promluv\u00edme o deadlockech. Nev\u00edm, kdo z v\u00e1s se s nimi setkal. Je to pom\u011brn\u011b b\u011b\u017en\u00fd probl\u00e9m v datab\u00e1zov\u00fdch syst\u00e9mech. Ale deadlocky jsou situace, kdy jedna relace \u010dek\u00e1 na to, aby n\u011bco provedla jin\u00e1 relace. A v ten moment druh\u00e1 relace \u010dek\u00e1 na to, aby prvn\u00ed relace provedla n\u011bco. <\/p>\n

    <\/p>\n

    Nap\u0159\u00edklad, pokud \u0159\u00edk\u00e1 Ivan: \u201eDej mi n\u011bco\u201c, a j\u00e1 \u0159\u00edk\u00e1m: \u201eNe, d\u00e1m ti to jen tehdy, pokud mi d\u00e1\u0161 n\u011bco jin\u00e9ho\u201c. A on \u0159\u00edk\u00e1: \u201eNe, ned\u00e1m ti to, pokud mi to ned\u00e1\u0161\u201c. A dost\u00e1v\u00e1me se do situace mrtv\u00e9ho zablokov\u00e1n\u00ed. Jsem si jist\u00fd, \u017ee Ivan to neud\u011bl\u00e1, ale rozum\u00edte smyslu, \u017ee m\u00e1me dv\u011b osoby, kter\u00e9 cht\u011bj\u00ed n\u011bco z\u00edskat a nejsou ochotny to d\u00e1t, dokud jim druh\u00e1 osoba ned\u00e1 to, co cht\u011bj\u00ed. A tady nen\u00ed \u017e\u00e1dn\u00e9 \u0159e\u0161en\u00ed. <\/p>\n

    <\/p>\n

    A vlastn\u011b va\u0161e datab\u00e1ze to mus\u00ed odhalit. A pak je nutn\u00e9 odstranit nebo uzav\u0159\u00edt jednu z relac\u00ed, proto\u017ee jinak z\u016fstanou nav\u017edy. A vid\u00edme to v datab\u00e1z\u00edch, vid\u00edme to v opera\u010dn\u00edch syst\u00e9mech. A na v\u0161ech m\u00edstech, kde m\u00e1me paraleln\u00ed procesy, se to m\u016f\u017ee st\u00e1t. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A nyn\u00ed um\u00edst\u00edme dv\u011b mrtv\u00e1 zablokov\u00e1n\u00ed. Um\u00edst\u00edme 50 a 80. V prvn\u00ed \u0159ad\u011b provedu aktualizaci z 50 na 50. Bude m\u00edt \u010d\u00edslo transakce 710. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pot\u00e9 vym\u011bn\u00edm 80 za 81 a 50 za 51. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A takto to bude vypadat. A proto m\u00e1 710 zablokov\u00e1n\u00ed \u0159\u00e1dku, a 711 \u010dek\u00e1 na potvrzen\u00ed. Vid\u011bli jsme to, kdy\u017e jsme aktualizovali. 710 je vlastn\u00edkem na\u0161eho \u0159\u00e1dku. A 711 \u010dek\u00e1, a\u017e 710 dokon\u010d\u00ed transakci.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A je tam dokonce naps\u00e1no, na jak\u00e9m konkr\u00e9tn\u00edm \u0159\u00e1dku m\u00e1me mrtv\u00e1 zablokov\u00e1n\u00ed. A zde se to za\u010d\u00edn\u00e1 st\u00e1vat podivn\u00fdm.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Nyn\u00ed aktualizujeme 80 na 80. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A tady za\u010d\u00ednaj\u00ed deadlocky. 710 \u010dek\u00e1 na odpov\u011b\u010f od 711, zat\u00edmco 711 \u010dek\u00e1 na 710. A to nepovede k dobr\u00e9mu konci. A nen\u00ed z toho v\u00fdchodiska. A budou si vz\u00e1jemn\u011b \u010dekat na odpov\u011bdi. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A to v\u0161echno za\u010dne zpo\u017e\u010fovat. A to nechceme. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A v Postgres jsou zp\u016fsoby, jak zjistit, kdy k tomu doch\u00e1z\u00ed. A kdy\u017e k tomu dojde, dostanete takovou chybu. Z toho jasn\u011b vypl\u00fdv\u00e1, \u017ee n\u011bjak\u00fd proces \u010dek\u00e1 na SHARE LOCK od jin\u00e9ho procesu, t.j. kter\u00fd je blokov\u00e1n procesem 711. Ten proces \u010dekal, \u017ee dostane SHARE LOCK na n\u011bjak\u00e9m transak\u010dn\u00edm ID a byl blokov\u00e1n n\u011bjak\u00fdm jin\u00fdm procesem. Proto zde nast\u00e1v\u00e1 situace deadlocku.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Ale existuj\u00ed i trojstrann\u00e9 deadlocky? Je to mo\u017en\u00e9? Ano. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Zad\u00e1v\u00e1me tato \u010d\u00edsla do tabulky. M\u011bn\u00edme 40 na 40, d\u011bl\u00e1me z\u00e1mek. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    M\u011bn\u00edme 60 na 61, 80 na 81. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pak m\u011bn\u00edme 80, a pak \u2013 bum! <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A 714 nyn\u00ed \u010dek\u00e1 na 715. 716. \u010dek\u00e1 na 715. A s t\u00edm u\u017e nic neud\u011bl\u00e1te.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tady u\u017e nejsou dva lid\u00e9, tady jsou u\u017e t\u0159i lid\u00e9. Chci n\u011bco od tebe, ten chce n\u011bco od t\u0159et\u00ed osoby a t\u0159et\u00ed osoba chce n\u011bco ode m\u011b. Dost\u00e1v\u00e1me se do trojstrann\u00e9ho \u010dek\u00e1n\u00ed, proto\u017ee v\u0161ichni \u010dek\u00e1me, a\u017e ten druh\u00fd dokon\u010d\u00ed, co m\u00e1 ud\u011blat. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A Postgres v\u00ed, na kter\u00e9m \u0159\u00e1dku k tomu doch\u00e1z\u00ed. A proto v\u00e1m vyd\u00e1 n\u00e1sleduj\u00edc\u00ed zpr\u00e1vu, kter\u00e1 ukazuje, \u017ee m\u00e1te probl\u00e9m, kde si t\u0159i procesy vz\u00e1jemn\u011b blokuj\u00ed. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Dal\u0161\u00ed probl\u00e9m \u2013 serializovatelnost. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Pokud je speci\u00e1ln\u00ed serializovateln\u00fd z\u00e1mek.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A vrac\u00edme se k 719. M\u00e1 naprosto norm\u00e1ln\u00ed v\u00fdstup. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A m\u016f\u017eete stisknout, abyste provedli transakci z serializovateln\u00e9ho.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A uv\u011bdomujete si, \u017ee nyn\u00ed m\u00e1te jin\u00fd typ z\u00e1mku SA \u2013 to znamen\u00e1 serializovateln\u00fd. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A proto m\u00e1me nov\u00fd typ z\u00e1mku, kter\u00fd se naz\u00fdv\u00e1 SARieadLock, co\u017e je seri\u00e1lov\u00fd z\u00e1mek a umo\u017e\u0148uje zad\u00e1vat s\u00e9riov\u00e9 \u010d\u00edsla.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A tak\u00e9 m\u016f\u017eete vkl\u00e1dat unik\u00e1tn\u00ed indexy. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    V t\u00e9to tabulce m\u00e1me unik\u00e1tn\u00ed indexy.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Tak\u017ee pokud sem zad\u00e1m \u010d\u00edslo 2, m\u00e1m 2. Ale \u00fapln\u011b naho\u0159e vkl\u00e1d\u00e1m dal\u0161\u00ed 2. A m\u016f\u017eete vid\u011bt, \u017ee 721 m\u00e1 exkluzivn\u00ed z\u00e1mek. Ale nyn\u00ed 722 \u010dek\u00e1, aby 721 dokon\u010dil svou operaci, proto\u017ee nem\u016f\u017ee vlo\u017eit 2, dokud nev\u00ed, co se stane s 721. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud d\u011bl\u00e1me subtransakci.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

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

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A pokud si uchov\u00e1v\u00e1me bod a pot\u00e9 ho aktualizujeme, z\u00edsk\u00e1me nov\u00fd transak\u010dn\u00ed ID. To je dal\u0161\u00ed chov\u00e1n\u00ed, kter\u00e9 byste m\u011bli zn\u00e1t. Pokud to vr\u00e1t\u00edme, transak\u010dn\u00ed ID zmiz\u00ed. 724 zmiz\u00ed. Ale nyn\u00ed m\u00e1me 725. <\/p>\n

    <\/p>\n

    A co se zde sna\u017e\u00edm ud\u011blat? Sna\u017e\u00edm se v\u00e1m uk\u00e1zat p\u0159\u00edklady neobvykl\u00fdch z\u00e1mk\u016f, kter\u00e9 m\u016f\u017eete naj\u00edt: a\u0165 u\u017e se jedn\u00e1 o serializable z\u00e1mky nebo SAVEPOINT \u2013 to jsou r\u016fzn\u00e9 typy z\u00e1mk\u016f, kter\u00e9 se objev\u00ed v tabulce z\u00e1mk\u016f. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    To je vytv\u00e1\u0159en\u00ed explicitn\u00edch (jasn\u00fdch) z\u00e1mk\u016f, kter\u00e9 maj\u00ed pg_advisory_lock. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A vid\u00edte, \u017ee typ z\u00e1mku je zde uveden jako advisory. A tady je naps\u00e1no \u010derven\u011b \u201eadvisory\u201c. A m\u016f\u017eete takto z\u00e1rove\u0148 zablokovat s pg_advisory_unlock.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A na z\u00e1v\u011br bych v\u00e1m cht\u011bl uk\u00e1zat je\u0161t\u011b jednu zaj\u00edmavou v\u011bc. Vytvo\u0159\u00edm dal\u0161\u00ed typ. Ale spoj\u00edm tabulku pg_locks s tabulkou pg_stat_activity. A pro\u010d to chci ud\u011blat? Proto\u017ee mi to umo\u017en\u00ed pod\u00edvat se a vid\u011bt v\u0161echny aktu\u00e1ln\u00ed relace a to, jak\u00e9 z\u00e1mky o\u010dek\u00e1vaj\u00ed. A to je docela zaj\u00edmav\u00e9, kdy\u017e spoj\u00edme tabulku z\u00e1mk\u016f a tabulku dotaz\u016f. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A tady vytv\u00e1\u0159\u00edme pg_stat_view. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A aktualizujeme \u0159\u00e1dek o jeden. A zde vid\u00edme 724. A pot\u00e9 aktualizujeme n\u00e1\u0161 \u0159\u00e1dek na t\u0159i. A co vid\u00edte nyn\u00ed? To jsou dotazy, tedy vid\u00edte cel\u00fd seznam dotaz\u016f, kter\u00e9 jsou vyjmenov\u00e1ny ve lev\u00e9m sloupci. A pak na prav\u00e9 stran\u011b m\u016f\u017eete vid\u011bt z\u00e1mky a to, co vytv\u00e1\u0159ej\u00ed. A to m\u016f\u017ee b\u00fdt pro v\u00e1s srozumiteln\u011bj\u0161\u00ed, abyste nemuseli poka\u017ed\u00e9 znovu nav\u0161t\u011bvovat ka\u017edou relaci a zji\u0161\u0165ovat, zda se k n\u00ed p\u0159ipojit, nebo ne. To za n\u00e1s d\u011blaj\u00ed. <\/p>\n

    <\/p>\n

    Dal\u0161\u00ed funkce, kter\u00e1 je velmi u\u017eite\u010dn\u00e1 \u2013 to je pg_blocking_pids<\/code>Mo\u017en\u00e1 jste o n\u00ed nikdy nesly\u0161eli. Co d\u011bl\u00e1? Umo\u017e\u0148uje n\u00e1m \u0159\u00edct, kter\u00e9 ID proces\u016f o\u010dek\u00e1v\u00e1 pro tuto relaci 11740. M\u016f\u017eete vid\u011bt, \u017ee 11740 o\u010dek\u00e1v\u00e1 724. A 724 je na vrcholu. A 11306 je v\u00e1\u0161 ID proces. V podstat\u011b tato funkce proch\u00e1z\u00ed va\u0161\u00ed tabulkou z\u00e1mk\u016f. A v\u00edm, \u017ee je to trochu slo\u017eit\u00e9, ale rozum\u00edte tomu. V podstat\u011b tato funkce proch\u00e1z\u00ed touto tabulkou z\u00e1mk\u016f a pokou\u0161\u00ed se naj\u00edt, kde je tento ID proces, s ohledem na ty z\u00e1mky, kter\u00e9 o\u010dek\u00e1v\u00e1. Tak\u00e9 se pokou\u0161\u00ed vypo\u010d\u00edtat, kter\u00fd p\u0159esn\u011b proces ID m\u00e1 ten proces, kter\u00fd \u010dek\u00e1 na z\u00e1mek. Proto m\u016f\u017eete spustit tuto funkci. pg_blocking_pids<\/code>.<\/p>\n

    <\/p>\n

    A to m\u016f\u017ee b\u00fdt velmi u\u017eite\u010dn\u00e9. P\u0159idali jsme to a\u017e od verze 9.6, tak\u017ee tato funkce m\u00e1 pouh\u00fdch 5 let, ale je velmi a velmi u\u017eite\u010dn\u00e1. To sam\u00e9 plat\u00ed pro druh\u00fd dotaz. Ukazuje p\u0159esn\u011b to, co pot\u0159ebujeme vid\u011bt. <\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    To je to, o \u010dem jsem cht\u011bl s v\u00e1mi mluvit. A jak jsem o\u010dek\u00e1val, vy\u010derpali jsme cel\u00fd n\u00e1\u0161 \u010das, proto\u017ee bylo obrovsk\u00e9 mno\u017estv\u00ed slid\u016f. Slidy jsou k dispozici ke sta\u017een\u00ed. Cht\u011bl bych v\u00e1m pod\u011bkovat, \u017ee jste byli tady. Jsem si jist\u00fd, \u017ee si u\u017eijete zbytek konference, d\u011bkuji!<\/p>\n

    <\/p>\n

    Ot\u00e1zky:<\/p>\n

    <\/p>\n

    Nap\u0159\u00edklad, pokud se sna\u017e\u00edm aktualizovat \u0159\u00e1dky a druh\u00e1 relace se sna\u017e\u00ed odstranit cel\u00fd tabulku. Jak jsem pochopil, m\u011blo by tam b\u00fdt n\u011bco jako intent lock. Existuje n\u011bco takov\u00e9ho v Postgresu?<\/em><\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    Vra\u0165me se na po\u010d\u00e1tek. Mo\u017en\u00e1 si pamatujete, \u017ee kdy\u017e d\u011bl\u00e1te cokoliv, nap\u0159\u00edklad SELECT, ud\u011bl\u00edme AccessShareLock. A to br\u00e1n\u00ed odstran\u011bn\u00ed tabulky. Tak\u017ee pokud se nap\u0159\u00edklad chcete aktualizovat \u0159\u00e1dek v tabulce nebo odstranit \u0159\u00e1dek, n\u011bkdo nem\u016f\u017ee odstranit celou tabulku sou\u010dasn\u011b s t\u00edm, proto\u017ee dr\u017e\u00edte tento AccessShareLock nad celou tabulkou a nad \u0159\u00e1dkem. A jakmile dokon\u010d\u00edte, mohou to odstranit. Ale dokud n\u011bco m\u011bn\u00edte, nemohou to ud\u011blat.<\/p>\n

    <\/p>\n

    Poj\u010fme to \u0159\u00edct je\u0161t\u011b jednou. P\u0159ejd\u011bme k p\u0159\u00edkladu s odstran\u011bn\u00edm. A vid\u00edte, \u017ee na \u0159\u00e1dku je exkluzivn\u00ed z\u00e1mek nad celou tabulkou. <\/p>\n

    <\/p>\n

    Bude to vypadat jako exkluzivn\u00ed z\u00e1mek, \u017ee?<\/em><\/p>\n

    <\/p>\n

    Ano, to vypad\u00e1 takto. Ch\u00e1pu, o \u010dem mluv\u00edte. \u0158\u00edk\u00e1te, \u017ee kdy\u017e provedu SELECT, budu m\u00edt ShareExclusive a pak to p\u0159evedu do stavu Row Exclusive, je to probl\u00e9m? P\u0159ekvapiv\u011b to ale ne\u010din\u00ed \u017e\u00e1dn\u00fd probl\u00e9m. Je to jako zv\u00fd\u0161en\u00ed \u00farovn\u011b z\u00e1mku, ale v podstat\u011b m\u00e1m z\u00e1mek, kter\u00fd br\u00e1n\u00ed vymaz\u00e1n\u00ed. A te\u010f, kdy\u017e tento z\u00e1mek posiluji, st\u00e1le br\u00e1n\u00ed vymaz\u00e1n\u00ed. Tak\u017ee to nen\u00ed tak, \u017ee bych zvy\u0161oval \u00farove\u0148. Tj. br\u00e1nil tomu i na ni\u017e\u0161\u00ed \u00farovni, tak\u017ee kdy\u017e ji zvy\u0161uji, st\u00e1le to br\u00e1n\u00ed odstran\u011bn\u00ed tabulky. <\/p>\n

    <\/p>\n

    Rozum\u00edm, o \u010dem mluv\u00edte. Nejedn\u00e1 se o situaci, kdy byste se sna\u017eili zru\u0161it jeden z\u00e1mek, abyste zavedli siln\u011bj\u0161\u00ed z\u00e1mek. Tady to prost\u011b v\u0161ude zvy\u0161uje tuto prevenci, tak\u017ee to nezp\u016fsobuje \u017e\u00e1dn\u00fd konflikt. Ale to je dobr\u00e1 ot\u00e1zka. D\u011bkuji, \u017ee jste se na to zeptali!<\/p>\n

    <\/p>\n

    Co pot\u0159ebujeme ud\u011blat, abychom se vyhnuli situaci deadlocku, kdy\u017e m\u00e1me mnoho relac\u00ed, velk\u00e9 mno\u017estv\u00ed u\u017eivatel\u016f?<\/em><\/p>\n

    <\/p>\n

    Postgres automaticky detekuje situace deadlocku. A automaticky odstran\u00ed jednu z relac\u00ed. Jedin\u00fd zp\u016fsob, jak se vyhnout situac\u00edm s mrtv\u00fdmi z\u00e1mky, je zamykat lidi ve stejn\u00e9m po\u0159ad\u00ed. Tak\u017ee kdy\u017e se pod\u00edv\u00e1te na svou aplikaci, \u010dasto p\u0159\u00ed\u010dina deadlock\u016f\u2026 P\u0159edstavme si, \u017ee chci zamknout dv\u011b r\u016fzn\u00e9 v\u011bci. Jedna aplikace zamkne tabulku 1, druh\u00e1 aplikace zamkne 2 a pak tabulku 1. A nejjednodu\u0161\u0161\u00ed zp\u016fsob, jak se vyhnout deadlock\u016fm, je pod\u00edvat se na svou aplikaci a sna\u017eit se zajistit, aby zamyk\u00e1n\u00ed prob\u00edhalo ve stejn\u00e9m po\u0159ad\u00ed ve v\u0161ech aplikac\u00edch. A to obvykle odstran\u00ed 80 % probl\u00e9m\u016f, proto\u017ee r\u016fzn\u00ed lid\u00e9 tyto aplikace p\u00ed\u0161\u00ed. A pokud je zamyk\u00e1te ve stejn\u00e9m po\u0159ad\u00ed, nesetk\u00e1te se se situac\u00ed deadlocku.<\/p>\n

    <\/p>\n

    D\u011bkuji v\u00e1m za va\u0161e vystoupen\u00ed! Mluvili jste o vacuum full a pokud tomu dob\u0159e rozum\u00edm, vacuum full deformuje po\u0159ad\u00ed z\u00e1znam\u016f v jednotliv\u00fdch \u00falo\u017ei\u0161t\u00edch, tak\u017ee aktu\u00e1ln\u00ed z\u00e1znamy z\u016fst\u00e1vaj\u00ed nezm\u011bn\u011bny. Pro\u010d vacuum full vy\u017eaduje exkluzivn\u00ed z\u00e1mek a pro\u010d to konfliktuje s operacemi z\u00e1pisu?<\/em><\/p>\n

    <\/p>\n

    To je dobr\u00e1 ot\u00e1zka. D\u016fvod spo\u010d\u00edv\u00e1 v tom, \u017ee vacuum full zab\u00edr\u00e1 tabulku. V podstat\u011b vytv\u00e1\u0159\u00edme novou verzi tabulky. A tabulka bude nov\u00e1. To znamen\u00e1, \u017ee to bude zcela nov\u00e1 verze tabulky. A probl\u00e9m je v tom, \u017ee kdy\u017e to d\u011bl\u00e1me, nechceme, aby lid\u00e9 to \u010detli, proto\u017ee pot\u0159ebujeme, aby vid\u011bli novou tabulku. A proto to souvis\u00ed s p\u0159edchoz\u00ed ot\u00e1zkou. Kdybychom mohli \u010d\u00edst sou\u010dasn\u011b, nemohli bychom ji p\u0159esunout a nasm\u011brovat lidi na novou tabulku. Museli bychom \u010dekat, aby ka\u017ed\u00fd dokon\u010dil \u010dten\u00ed t\u00e9to tabulky, a proto je to v podstat\u011b situace lock exclusive.
    \nJednodu\u0161e \u0159\u00edk\u00e1me, \u017ee blokujeme \u00fapln\u011b na za\u010d\u00e1tku, proto\u017ee v\u00edme, \u017ee na konci budeme pot\u0159ebovat exkluzivn\u00ed uzam\u010den\u00ed, abychom mohli v\u0161echny p\u0159esunout na novou kopii. Tak\u017ee potenci\u00e1ln\u011b m\u016f\u017eeme to umo\u017enit. A d\u011bl\u00e1me to s paraleln\u00edm indexov\u00e1n\u00edm. Ale je to mnohem slo\u017eit\u011bj\u0161\u00ed ud\u011blat. A to se v\u00fdrazn\u011b vztahuje k va\u0161\u00ed p\u0159edchoz\u00ed ot\u00e1zce o lock exclusive.<\/p>\n

    <\/p>\n

    Je mo\u017en\u00e9 nastavit timeout pro z\u00e1mky v PostgreSQL? V Oracle mohu nap\u0159\u00edklad napsat \u201eselect for update\u201c a \u010dekat 50 sekund na aktualizaci. To bylo dobr\u00e9 pro aplikaci. Ale v PostgreSQL bu\u010f mus\u00edm ud\u011blat to ihned a v\u016fbec ne\u010dekat, nebo \u010dekat do ur\u010dit\u00e9ho \u010dasu.<\/em> <\/p>\n

    <\/p>\n

    Ano, m\u016f\u017eete zvolit timeout pro va\u0161e z\u00e1mky. M\u016f\u017eete tak\u00e9 vydat p\u0159\u00edkaz no way, kter\u00fd bude \u2026, pokud nez\u00edsk\u00e1te z\u00e1mek okam\u017eit\u011b. Tak\u017ee bu\u010f lock timeout, nebo n\u011bco jin\u00e9ho, co v\u00e1m to umo\u017en\u00ed. Ned\u011bl\u00e1 se to na syntaktick\u00e9 \u00farovni. D\u011bl\u00e1 se to jako prom\u011bnn\u00e1 na serveru. N\u011bkdy to nelze pou\u017e\u00edt.<\/p>\n

    <\/p>\n

    M\u016f\u017eete otev\u0159\u00edt 75. sn\u00edmek?<\/em> <\/p>\n

    <\/p>\n

    Ano.<\/p>\n

    <\/p>\n

    \"Odemknut\u00ed<\/p>\n

    <\/p>\n

    A moje ot\u00e1zka zn\u00ed: Pro\u010d oba procesy aktualizace \u010dekaj\u00ed na 703?<\/em><\/p>\n

    <\/p>\n

    A to je skv\u011bl\u00e1 ot\u00e1zka. Omlouv\u00e1m se, ale nech\u00e1pu, pro\u010d to Postgres d\u011bl\u00e1. Kdy\u017e byl 703 vytvo\u0159en, o\u010dek\u00e1val 702. A kdy\u017e se objev\u00ed 704 a 705, vypad\u00e1 to, \u017ee nev\u011bd\u00ed, co maj\u00ed o\u010dek\u00e1vat, proto\u017ee tam je\u0161t\u011b nic nen\u00ed. A Postgres to d\u011bl\u00e1 takto: kdy\u017e nem\u016f\u017eete z\u00edskat z\u00e1mek, nap\u00ed\u0161e \"A pro\u010d bych v\u00e1s m\u011bl zpracov\u00e1vat?\", proto\u017ee u\u017e na n\u011bkoho \u010dek\u00e1te. Tak\u017ee mu prost\u011b nech\u00e1me viset ve vzduchu, nezaktualizuje to v\u016fbec. Co se tedy zde stalo? Jakmile 702 dokon\u010dil proces a 703 z\u00edskal sv\u016fj z\u00e1mek, syst\u00e9m se vr\u00e1til zp\u011bt. A \u0159ekl, \u017ee nyn\u00ed m\u00e1me dv\u011b osoby, kter\u00e9 \u010dekaj\u00ed. A pak je oba aktualizujeme spole\u010dn\u011b. A uvedeme, \u017ee oba \u010dekaj\u00ed. <\/p>\n

    <\/p>\n

    Nev\u00edm, pro\u010d to Postgres d\u011bl\u00e1. Ale je tu probl\u00e9m, kter\u00fd se naz\u00fdv\u00e1 f\u2026 M\u00e1m pocit, \u017ee to nen\u00ed term\u00edn v \u010de\u0161tin\u011b. To je, kdy\u017e v\u0161ichni \u010dekaj\u00ed na jeden z\u00e1mek, i kdy\u017e je tu 20 instanc\u00ed, kter\u00e9 na z\u00e1mek \u010dekaj\u00ed. A najednou se v\u0161ichni probud\u00ed najednou. A v\u0161ichni se sna\u017e\u00ed reagovat. Ale syst\u00e9m to ud\u011bl\u00e1 tak, \u017ee v\u0161ichni \u010dekaj\u00ed na 703. Proto\u017ee na v\u0161echny \u010dekaj\u00ed a okam\u017eit\u011b je v\u0161echny se\u0159ad\u00edme do fronty. A pokud se objev\u00ed jak\u00fdkoli jin\u00fd nov\u00fd po\u017eadavek, kter\u00fd byl vytvo\u0159en po tom, nap\u0159\u00edklad 707, tak tam op\u011bt bude pr\u00e1zdnota. <\/p>\n

    <\/p>\n

    M\u00e1m pocit, \u017ee tohle se d\u011bl\u00e1 proto, aby bylo mo\u017en\u00e9 \u0159\u00edct, \u017ee v tomto kroku 702 \u010dek\u00e1 na 703, a v\u0161ichni, kdo p\u0159ijdou po n\u011bm, nebudou m\u00edt \u017e\u00e1dn\u00fd z\u00e1znam v tomto poli. Ale jakmile prvn\u00ed \u010dekaj\u00edc\u00ed odejde, a v\u0161ichni, kdo \u010dekali v tom okam\u017eiku p\u0159ed aktualizac\u00ed, dostanou stejn\u00fd ozna\u010dova\u010d. A proto m\u00e1m pocit, \u017ee je to ud\u011bl\u00e1no, abychom mohli zpracov\u00e1vat v po\u0159ad\u00ed, aby byly spr\u00e1vn\u011b se\u0159azeny. <\/p>\n

    <\/p>\n

    V\u017edycky jsem se na to d\u00edval jako na dost zvl\u00e1\u0161tn\u00ed fenom\u00e9n. Proto\u017ee zde nap\u0159\u00edklad je v\u016fbec nezmi\u0148ujeme. Ale m\u00e1m pocit, \u017ee poka\u017ed\u00e9, kdy\u017e d\u00e1me nov\u00fd z\u00e1mek, tak kouk\u00e1me na v\u0161echny ty, kdo jsou v \u010dekac\u00edm procesu. Potom je v\u0161echny se\u0159ad\u00edme do fronty. A potom jak\u00fdkoli nov\u00fd, kter\u00fd p\u0159ijde, se dostane do fronty a\u017e kdy\u017e ten dal\u0161\u00ed \u010dlov\u011bk dokon\u010d\u00ed zpracov\u00e1n\u00ed. Odpov\u011b\u010f je velmi dobr\u00e1. D\u011bkuji v\u00e1m mnohokr\u00e1t za ot\u00e1zku!<\/p>\n

    <\/p>\n

    Mysl\u00edm, \u017ee je mnohem logi\u010dt\u011bj\u0161\u00ed, kdy\u017e 705 \u010dek\u00e1 na 704.<\/em> <\/p>\n

    <\/p>\n

    Ale probl\u00e9m je n\u00e1sleduj\u00edc\u00ed. Technicky m\u016f\u017eete probudit bu\u010f jedno, nebo druh\u00e9. A proto probud\u00edme to nebo ono. Ale co se d\u011bje v pr\u00e1ci syst\u00e9mu? Vid\u00edte, jak 703 na sam\u00e9m vrcholu zablokoval sv\u016fj vlastn\u00ed transak\u010dn\u00ed ID. To je, jak Postgres funguje. A 703 je blokov\u00e1n sv\u00fdm vlastn\u00edm transak\u010dn\u00edm ID, tak\u017ee pokud n\u011bkdo chce po\u010dkat, bude \u010dekat na 703. A vlastn\u011b, 703 kon\u010d\u00ed. A a\u017e po jeho dokon\u010den\u00ed se n\u011bjak\u00fd z proces\u016f probud\u00ed. A nev\u00edme, kter\u00fd proces to bude. Pot\u00e9 postupn\u011b zpracov\u00e1v\u00e1me v\u0161echny. Ale nen\u00ed jasn\u00e9, kter\u00fd proces se probud\u00ed jako prvn\u00ed, proto\u017ee to m\u016f\u017ee b\u00fdt kter\u00fdkoli z t\u011bchto proces\u016f. V z\u00e1sad\u011b jsme m\u011bli pl\u00e1nova\u010d, kter\u00fd \u0159\u00edkal, \u017ee nyn\u00ed m\u016f\u017eeme probudit kter\u00fdkoli z t\u011bchto proces\u016f. Jednodu\u0161e vybereme jeden n\u00e1hodn\u011b. Proto je t\u0159eba ozna\u010dit oba, proto\u017ee m\u016f\u017eeme probudit kter\u00fdkoli z nich. <\/p>\n

    <\/p>\n

    A probl\u00e9m je v tom, \u017ee m\u00e1me CP-nekone\u010dno. A proto je velmi pravd\u011bpodobn\u00e9, \u017ee m\u016f\u017eeme probudit pozd\u011bj\u0161\u00ed. A pokud nap\u0159\u00edklad budeme probouzet pozd\u011bj\u0161\u00ed, pak budeme o\u010dek\u00e1vat toho, kdo pr\u00e1v\u011b z\u00edskal z\u00e1mek, tak\u017ee nev\u00edme, kdo bude probuzen jako prvn\u00ed. Vytv\u00e1\u0159\u00edme jednodu\u0161e takovou situaci a syst\u00e9m je bude probouzet v n\u00e1hodn\u00e9m po\u0159ad\u00ed. <\/p>\n

    <\/p>\n

    Existuje \u010dl\u00e1nky o locks Egora Rogova<\/a><\/noindex>. Pod\u00edvejte se, tak\u00e9 jsou zaj\u00edmav\u00e9 a u\u017eite\u010dn\u00e9. T\u00e9ma je samoz\u0159ejm\u011b d\u011bsiv\u011b slo\u017eit\u00e9. D\u011bkuji mnohokr\u00e1t, Bruci!<\/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\udd47Odem\u010den\u00ed spr\u00e1vce z\u00e1mk\u016f Postgresu. Bruce Momjyan | ProHoster","description":"P\u0159ehled zpr\u00e1vy Bruce Momjiana z roku 2020 \"Odemknut\u00ed spr\u00e1vce z\u00e1mk\u016f Postgres.\"","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/unlocking-the-postgres-lock-manager-bryus-momzhian","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/87954","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=87954"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/87954\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/87955"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=87954"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=87954"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=87954"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}