{"id":83337,"date":"2020-05-30T07:42:48","date_gmt":"2020-05-30T05:42:48","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh"},"modified":"2020-05-30T07:42:48","modified_gmt":"2020-05-30T05:42:48","slug":"k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh","status":"publish","type":"post","link":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh","title":{"rendered":"Ce consecin\u021be poate avea sl\u0103birea nivelului de izolare a tranzac\u021biilor \u00een bazele de date","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Bun\u0103 ziua tuturor. Vladislav Rodin la datorie. \u00cen prezent, sunt conduc\u0103torul cursului \u201eArhitect de \u00eenalt\u0103 \u00eenc\u0103rcare\u201d la OTUS \u0219i predau de asemenea cursuri dedicate arhitecturii software-ului.
\n<\/i>
\nPe l\u00e2ng\u0103 predare, dup\u0103 cum a\u021bi observat, m\u0103 ocup de scrierea de materiale originale pentru blogul OTUS pe Habr \u0219i vreau s\u0103 dedic articolul de ast\u0103zi lans\u0103rii cursului \u00abPostgreSQL\u00bb<\/a><\/noindex>, pentru care \u00eenscrierile sunt deschise chiar acum.<\/i><\/b><\/p>\n

\"Ce
\n<\/p>\n

Introducere<\/h2>\n

\n\u00cen ultima dat\u0103<\/a><\/noindex> Am discutat despre cum tranzac\u021biile \u00een bazele de date servesc pentru a rezolva dou\u0103 sarcini: asigurarea rezilien\u021bei \u0219i accesul la date \u00eentr-un mediu concuren\u021bial. Pentru a \u00eendeplini aceste sarcini complet, o tranzac\u021bie trebuie s\u0103 aib\u0103 propriet\u0103\u021bile ACID. Ast\u0103zi vom vorbi \u00een detaliu despre litera I (izolare)<\/b> din aceast\u0103 abreviere.
\n<\/a><\/noindex><\/p>\n

Izolare<\/h2>\n

\nIzolarea rezolv\u0103 problema accesului la date \u00eentr-un mediu concurent, oferind \u00een esen\u021b\u0103 protec\u021bie \u00eempotriva condi\u021biilor de competi\u021bie. \u00cen ideal, izolarea \u00eenseamn\u0103 serializare, adic\u0103 o proprietate care asigur\u0103 c\u0103 rezultatul execu\u021biei tranzac\u021biilor paralele este acela\u0219i cu cel care ar fi fost ob\u021binut dac\u0103 acestea ar fi fost executate secven\u021bial. Problema principal\u0103 a acestei propriet\u0103\u021bi este c\u0103 este foarte greu de realizat tehnic \u0219i, ca urmare, afecteaz\u0103 semnificativ performan\u021ba sistemului. De aceea, izolarea este adesea sl\u0103bit\u0103, asum\u00e2nd riscurile apari\u021biei unor anomalii, despre care se va discuta mai jos. Capacitatea de a genera diverse anomalii caracterizeaz\u0103 nivelul de izolare al tranzac\u021biilor.<\/p>\n

Cele mai cunoscute anomalii sunt: lectura murdar\u0103, lectura non-repetabil\u0103, lectura fantom\u0103, dar, de fapt, exist\u0103 \u00eenc\u0103 5: scrierea murdar\u0103, actualizarea pierdut\u0103 a cursorului, actualizarea pierdut\u0103, citirea skew, scrierea skew.<\/p>\n

Scrierea murdar\u0103<\/h2>\n

\nEsenta anomaliilor const\u0103 \u00een faptul c\u0103 tranzac\u021biile pot suprascrie date necomise.<\/p>\n

\"Ce
\n
\nAceast\u0103 anomalie este periculoas\u0103 nu doar pentru c\u0103 datele pot intra \u00een conflict dup\u0103 confirmarea ambelor tranzac\u021bii (a\u0219a cum se vede \u00een imagine), ci \u0219i pentru c\u0103 afecteaz\u0103 atomicitatea: deoarece vom permite suprascrierea datelor necomise, nu este clar cum s\u0103 revenim asupra unei tranzac\u021bii f\u0103r\u0103 a afecta alta. <\/p>\n

Anomalia se repar\u0103 destul de simplu: punem un blocaj pe scriere \u00eenainte de a \u00eencepe scrierea, interzic\u00e2nd altor tranzac\u021bii s\u0103 schimbe \u00eenregistrarea p\u00e2n\u0103 c\u00e2nd blocajul este ridicat. <\/p>\n

Lectura murdar\u0103<\/h2>\n

\nLectura murdar\u0103 \u00eenseamn\u0103 citirea unor date necomise. <\/p>\n

\"Ce
\n
\nProblemele apar atunci c\u00e2nd este necesar s\u0103 se realizeze ac\u021biuni sau s\u0103 se ia decizii pe baza unui e\u0219antion.<\/p>\n

Pentru a corecta anomalia, se poate aplica o blocare pe citire, dar acest lucru va afecta semnificativ performan\u021ba. Este mult mai simplu s\u0103 spunem c\u0103 pentru rollback-ul tranzac\u021biei, starea ini\u021bial\u0103 a datelor (\u00eenainte de \u00eenceperea scrierii) trebuie p\u0103strat\u0103 \u00een sistem. De ce s\u0103 nu citim din asta? Este destul de ieftin, a\u0219a c\u0103 majoritatea bazelor de date elimin\u0103 citirea murdar\u0103 prin defalcare. <\/p>\n

Actualizare pierdut\u0103<\/h2>\n

\nActualizare pierdut\u0103 \u00eenseamn\u0103 actualiz\u0103ri pierdute, iar traducerea reflect\u0103 destul de bine esen\u021ba problemei:<\/p>\n

\"Ce
\n
\nPractically, the result of transaction T2 has been canceled. This situation is corrected by explicit or implicit record locks. That is, we either simply update the record, which causes an implicit lock, or we perform select for update<\/b>, causing both read and write locks to occur. Note that this operation is quite dangerous: by our 'innocent' reading, we block other readings. Some databases offer a safer select for share<\/b>, allowing data to be read but not modified. <\/p>\n

Actualizarea pierdut\u0103 a cursorului<\/h2>\n

\nPentru un control mai detaliat, bazele de date pot oferi alte instrumente, cum ar fi cursorii. Un cursor este o structur\u0103 care con\u021bine un set de r\u00e2nduri \u0219i permite iterarea prin ele. declare cursor_name for select_statement<\/b>. Con\u021binutul cursorului este descris de select. <\/p>\n

De ce avem nevoie de cursor? Este vorba despre faptul c\u0103 unele baze de date ofer\u0103 blocare pe toate \u00eenregistr\u0103rile selectate prin select (stabilitate la citire), sau doar pe acea \u00eenregistrare pe care se afl\u0103 \u00een acest moment cursorul (stabilitate a cursorului). La stabilitatea cursorului se realizeaz\u0103 o blocare pe termen scurt, ceea ce permite reducerea num\u0103rului de bloc\u0103ri atunci c\u00e2nd iter\u0103m printr-un set mare de date. De aceea, anomalia actualiz\u0103rii pierdute este eviden\u021biat\u0103 pentru cursor separat.<\/p>\n

Citire non-repetabil\u0103<\/h2>\n

\nCitirea non-repetabil\u0103 const\u0103 \u00een faptul c\u0103, \u00een timpul execut\u0103rii tranzac\u021biei noastre, 2 citiri succesive ale acelea\u0219i \u00eenregistr\u0103ri vor duce la ob\u021binerea unor rezultate diferite, deoarece o alt\u0103 tranzac\u021bie a intervenit \u00eentre aceste dou\u0103 citiri, a modificat datele noastre \u0219i a fost confirmat\u0103. <\/p>\n

\"Ce
\n
\nDe ce este aceasta o problem\u0103? Imagina\u021bi-v\u0103 c\u0103 scopul tranzac\u021biei T2 din imagine este de a selecta toate produsele a c\u0103ror pre\u021b este mai mic de 150 u.e. Cineva a actualizat pre\u021bul la 200 u.e. Astfel, filtrul stabilit nu a func\u021bionat.<\/p>\n

Aceste anomalii \u00eenceteaz\u0103 s\u0103 apar\u0103 prin ad\u0103ugarea de bloc\u0103ri \u00een dou\u0103 etape sau prin utilizarea mecanismului MVCC, despre care dorim s\u0103 discut\u0103m separat. <\/p>\n

Citire fantom\u0103<\/h2>\n

\nCitirea fantom\u0103 se refer\u0103 la citirea datelor care au fost ad\u0103ugate de o alt\u0103 tranzac\u021bie. <\/p>\n

\"Ce
\n
\nCa exemplu, putem observa selec\u021bia incorect\u0103 a celui mai ieftin produs \u00een cazul apari\u021biei acestei anomalii. <\/p>\n

Eliminarea citirilor fantom\u0103 este deja destul de complicat\u0103. O simpl\u0103 blocare nu este suficient\u0103, deoarece nu putem bloca ceea ce nu exist\u0103 \u00eenc\u0103. Sistemele 2PL folosesc bloc\u0103ri predictive, \u00een timp ce sistemele MVCC re\u021bin tranzac\u021biile care pot fi afectate de inser\u021bii. At\u00e2t primul, c\u00e2t \u0219i al doilea mecanism sunt destul de greoaie.<\/p>\n

Citire distorsionat\u0103<\/h2>\n

\nCitirea distorsionat\u0103 apare atunci c\u00e2nd lucr\u0103m cu mai multe tabele, ale c\u0103ror con\u021binuturi ar trebui s\u0103 se schimbe \u00een mod coerent. <\/p>\n

S\u0103 presupunem c\u0103 avem tabele care reprezint\u0103 post\u0103ri \u0219i metainforma\u021biile acestora:<\/p>\n

\"Ce
\n
\nO tranzac\u021bie cite\u0219te din tabele, alta le modific\u0103:<\/p>\n

\"Ce
\n
\nCa rezultat al execut\u0103rii tranzac\u021biei T1, postarea cu titlul = Good, iar updated_by = T2, ceea ce reprezint\u0103 o anumit\u0103 incongruen\u021b\u0103. <\/p>\n

De fapt, aceasta este o citire non-repetabil\u0103, dar \u00een cadrul mai multor tabele.<\/p>\n

Pentru a remedia situa\u021bia, T1 poate impune bloc\u0103ri asupra tuturor r\u00e2ndurilor pe care le va citi, ceea ce nu va permite tranzac\u021biei T2 s\u0103 modifice informa\u021bia. \u00cen cazul MVCC, tranzac\u021bia T2 va fi anulat\u0103. Protec\u021bia \u00eempotriva acestei anomalii poate deveni important\u0103 dac\u0103 folosim cursori. <\/p>\n

Scriere distorsionat\u0103<\/h2>\n

\nAceast\u0103 anomalie este de asemenea mai u\u0219or de explicat prin exemplu: s\u0103 presupunem c\u0103 \u00een sistemul nostru trebuie s\u0103 fie cel pu\u021bin un doctor de gard\u0103, dar ambii doctori au decis s\u0103-\u0219i anuleze serviciul:<\/p>\n

\"Ce
\n
\n\"Ce
\n
\nAnomalia a dus la faptul c\u0103 niciunul dintre doctori nu va veni la serviciu. De ce s-a \u00eent\u00e2mplat asta? Pentru c\u0103 tranzac\u021bia a verificat o condi\u021bie care ar putea fi afectat\u0103 de o alt\u0103 tranzac\u021bie, iar din cauza izol\u0103rii nu am v\u0103zut aceast\u0103 modificare.<\/p>\n

Aceasta este aceea\u0219i citire non-repetabil\u0103. Ca op\u021biune, selec\u021biile pot aplica bloc\u0103ri pe aceste \u00eenregistr\u0103ri.<\/p>\n

Scrierea deranjat\u0103 \u0219i citirea deranjat\u0103 sunt combina\u021bii ale anomaliilor anterioare. Putem analiza scrierea deranjat\u0103, care este \u00een esen\u021b\u0103 o citire fantom\u0103. S\u0103 examin\u0103m un tabel \u00een care se reg\u0103sesc numele angaja\u021bilor, salariile acestora \u0219i proiectul pe care lucreaz\u0103:<\/p>\n

\"Ce
\n
\n\"Ce
\n
\n\u00cen cele din urm\u0103, ob\u021binem urm\u0103toarea imagine: fiecare manager credea c\u0103 modificarea sa nu va duce la dep\u0103\u0219irea bugetului, a\u0219a c\u0103 au f\u0103cut modific\u0103ri de personal, care au dus la un surplus total. <\/p>\n

Cauza problemei este exact aceea\u0219i ca \u0219i \u00een cazul citirii fantome. <\/p>\n

Conclusions<\/h2>\n

\nSl\u0103birea nivelului de izolare a tranzac\u021biilor \u00een baza de date este un compromis \u00eentre securitate \u0219i performan\u021b\u0103, iar alegerea acestui nivel ar trebui s\u0103 se fac\u0103 \u00een func\u021bie de riscurile poten\u021biale pentru afacere \u00een cazul apari\u021biei anumitor anomalii.<\/p>\n

\nAfl\u0103 mai multe despre curs.
\n<\/a><\/noindex><\/p>\n

Sursa: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"

\u0412\u0441\u0435\u043c \u043f\u0440\u0438\u0432\u0435\u0442. \u041d\u0430 \u0441\u0432\u044f\u0437\u0438 \u0412\u043b\u0430\u0434\u0438\u0441\u043b\u0430\u0432 \u0420\u043e\u0434\u0438\u043d. \u0412 \u043d\u0430\u0441\u0442\u043e\u044f\u0449\u0435\u0435 \u0432\u0440\u0435\u043c\u044f \u044f \u044f\u0432\u043b\u044f\u044e\u0441\u044c \u0440\u0443\u043a\u043e\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u0435\u043c \u043a\u0443\u0440\u0441\u0430 \u00ab\u0410\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u043e\u0440 \u0432\u044b\u0441\u043e\u043a\u0438\u0445 \u043d\u0430\u0433\u0440\u0443\u0437\u043e\u043a\u00bb \u0432 OTUS, \u0430 \u0442\u0430\u043a\u0436\u0435 \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e \u043d\u0430 \u043a\u0443\u0440\u0441\u0430\u0445, \u043f\u043e\u0441\u0432\u044f\u0449\u0435\u043d\u043d\u044b\u0445 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0435 \u041f\u041e. \u041f\u043e\u043c\u0438\u043c\u043e \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u0432\u0430\u043d\u0438\u044f, \u043a\u0430\u043a \u0432\u044b \u043c\u043e\u0433\u043b\u0438 \u0437\u0430\u043c\u0435\u0442\u0438\u0442\u044c, \u044f \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043d\u0430\u043f\u0438\u0441\u0430\u043d\u0438\u0435\u043c \u0430\u0432\u0442\u043e\u0440\u0441\u043a\u043e\u0433\u043e \u043c\u0430\u0442\u0435\u0440\u0438\u0430\u043b\u0430 \u0434\u043b\u044f \u0431\u043b\u043e\u0433\u0430 OTUS \u043d\u0430 \u0445\u0430\u0431\u0440\u0435 \u0438 \u0441\u0435\u0433\u043e\u0434\u043d\u044f\u0448\u043d\u044e\u044e \u0441\u0442\u0430\u0442\u044c\u044e \u0445\u043e\u0447\u0443 \u043f\u0440\u0438\u0443\u0440\u043e\u0447\u0438\u0442\u044c \u043a \u0437\u0430\u043f\u0443\u0441\u043a\u0443 \u043a\u0443\u0440\u0441\u0430 \u00abPostgreSQL\u00bb, \u043d\u0430 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043f\u0440\u044f\u043c\u043e \u0441\u0435\u0439\u0447\u0430\u0441 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":83338,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-83337","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Ce consecin\u021be poate avea sl\u0103birea nivelului de izolare a tranzac\u021biilor \u00een bazele de date | ProHoster","description":"Bun\u0103 tuturor. Sunt Vladislav Rodin.","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","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\udd47\u041a \u0447\u0435\u043c\u0443 \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0438\u0432\u0435\u0441\u0442\u0438 \u043e\u0441\u043b\u0430\u0431\u043b\u0435\u043d\u0438\u0435 \u0443\u0440\u043e\u0432\u043d\u044f \u0438\u0437\u043e\u043b\u044f\u0446\u0438\u0438 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0439 \u0432 \u0431\u0430\u0437\u0430\u0445 \u0434\u0430\u043d\u043d\u044b\u0445 | ProHoster","og:description":"\u0412\u0441\u0435\u043c \u043f\u0440\u0438\u0432\u0435\u0442. \u041d\u0430 \u0441\u0432\u044f\u0437\u0438 \u0412\u043b\u0430\u0434\u0438\u0441\u043b\u0430\u0432 \u0420\u043e\u0434\u0438\u043d.","og:url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh","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-05-30T05:42:48+00:00","article:modified_time":"2020-05-30T05:42:48+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"83337","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 15:20:28","updated":"2022-09-28 14:57:58","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/83337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=83337"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/83337\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/83338"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=83337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=83337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=83337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}