{"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\/en\/blog\/administrirovanie\/k-chemu-mozhet-privesti-oslablenie-urovnya-izolyaczii-tranzakczij-v-bazah-dannyh","title":{"rendered":"What can happen if the transaction isolation level is weakened in databases","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hello everyone. This is Vladislav Rodin. Currently, I am the head of the \u2018High Load Architect\u2019 course at OTUS, and I also teach courses dedicated to software architecture.
\n<\/i>
\nIn addition to teaching, as you may have noticed, I write original content for the OTUS blog on Habr, and today's article is dedicated to the launch of the course \u2018PostgreSQL\u2019<\/a><\/noindex>, which is currently open for enrollment.<\/i><\/b><\/p>\n

\"What
\n<\/p>\n

Introduction<\/h2>\n

\nIn Last time<\/a><\/noindex> we discussed the fact that transactions in databases address two tasks: ensuring fault tolerance and data access in a concurrent environment. To fully accomplish these tasks, a transaction must exhibit ACID properties. Today we will discuss the letter I (isolation)<\/b> in this abbreviation.
\n<\/a><\/noindex><\/p>\n

Isolation<\/h2>\n

\nIsolation addresses the issue of data access in a concurrent environment, effectively providing protection against race conditions. Ideally, isolation means serialization, which is a property that ensures the result of executing transactions in parallel is the same as if they were executed sequentially. The main challenge of this property is that it is technically difficult to guarantee and consequently impacts system performance significantly. This is why isolation is often relaxed, accepting the risks of certain anomalies, which will be discussed below. The potential for various anomalies characterizes the level of transaction isolation.<\/p>\n

The most well-known anomalies are: dirty read, non-repeatable read, phantom read, but in fact, there are 5 more: dirty write, cursor lost update, lost update, read skew, write skew.<\/p>\n

Dirty write<\/h2>\n

\nThe essence of the anomaly is that transactions can overwrite uncommitted data.<\/p>\n

\"What
\n
\nThis anomaly is dangerous not only because data may conflict after the commit of both transactions (as shown in the picture), but also because it violates atomicity: by allowing the overwriting of uncommitted data, it becomes unclear how to roll back one transaction without affecting the other. <\/p>\n

The anomaly is quite easily resolved: we place a write lock before starting the write operation, preventing other transactions from changing the record until the lock is released. <\/p>\n

Dirty read<\/h2>\n

\nDirty read refers to the reading of uncommitted data. <\/p>\n

\"What
\n
\nProblems arise when actions or decisions need to be based on the sample.<\/p>\n

To resolve an anomaly, a read lock can be applied, but this significantly impacts performance. It is much simpler to state that for a transaction rollback, the original state of the data (before the write began) must be preserved in the system. Why not read from there? It\u2019s relatively inexpensive, which is why most databases disable dirty reads by default. <\/p>\n

Lost update<\/h2>\n

\nLost update means lost updates, and the translation accurately reflects the essence of the problem:<\/p>\n

\"What
\n
\nIn fact, the result of transaction T2 was canceled. This situation can be remedied through explicit or implicit record locks. This means we either simply update the record, which causes an implicit lock, or we perform select for update<\/b>, triggering a read and write lock. Note that this operation is quite dangerous: with our \"innocent\" reading, we lock out other reads. Some databases offer a safer select for share<\/b>, which allows reading the data but does not permit changes. <\/p>\n

Cursor lost update<\/h2>\n

\nFor more granular control, databases may offer other tools, such as a cursor. A cursor is a structure that contains a set of rows and allows iteration over them. declare cursor_name for select_statement<\/b>. The content of the cursor is described by the select statement. <\/p>\n

What is the purpose of a cursor? The fact is that some databases offer locks on all records selected by the select statement (read stability), or only on the record currently being pointed to by the cursor (cursor stability). With cursor stability, a short lock is applied, which helps reduce the number of locks when iterating over a large dataset. Therefore, the anomaly of lost update is highlighted separately for cursors.<\/p>\n

Non-repeatable read<\/h2>\n

\nNon-repeatable read means that during the execution of our transaction, 2 consecutive reads of the same record will yield different results because another transaction intervened between these two reads, changed our data, and was committed. <\/p>\n

\"What
\n
\nWhy is this a problem at all? Imagine that the goal of transaction T2 in the picture is to select all products with a price lower than 150 currency units. Someone else updated the price to 200 currency units. Thus, the set filter did not work.<\/p>\n

These data anomalies cease to occur when adding two-phase locking or using the MVCC mechanism, which I would like to discuss separately. <\/p>\n

Phantom read<\/h2>\n

\nA phantom read refers to reading data that has been added by another transaction. <\/p>\n

\"What
\n
\nFor example, you can observe an incorrect selection of the cheapest product when this anomaly occurs. <\/p>\n

Eliminating phantom reads is already quite challenging. Ordinary locking is not sufficient because we cannot lock something that does not yet exist. 2PL systems use predicate locking, while MVCC systems have the transaction scheduler cancel transactions that may be violated by an insertion. Both mechanisms are fairly heavyweight.<\/p>\n

Read skew<\/h2>\n

\nRead skew occurs when we are working with multiple tables whose content must change consistently. <\/p>\n

Suppose we have tables representing posts and their metadata:<\/p>\n

\"What
\n
\nOne transaction reads from the tables, another modifies them:<\/p>\n

\"What
\n
\nAs a result of executing transaction T1, the post has title = Good, while updated_by = T2, which represents some inconsistency. <\/p>\n

In fact, this is a non-repeatable read, but across multiple tables.<\/p>\n

To fix this, T1 can lock all the rows it will read, preventing transaction T2 from changing the information. In the case of MVCC, transaction T2 will be canceled. Protecting against this anomaly can become important if we use cursors. <\/p>\n

Write skew<\/h2>\n

\nThis anomaly can also be easily explained through an example: suppose that at least one doctor must be on duty in our system, but both doctors decided to cancel their duty:<\/p>\n

\"What
\n
\n\"What
\n
\nThe anomaly led to the situation where neither doctor will be on duty. Why did this happen? Because the transaction checked a condition that may be violated by another transaction, and due to isolation, we did not see this change.<\/p>\n

This is the same as a non-repeatable read. As an alternative, select statements can apply locks on these records.<\/p>\n

Write skew and read skew are combinations of the previous anomalies. We can consider write skew, which is essentially a phantom read. Let\u2019s examine a table that contains employee names, their salaries, and the projects they are working on:<\/p>\n

\"What
\n
\n\"What
\n
\nAs a result, we have the following picture: each manager thought that their change would not lead to a budget overrun, so they made staffing changes that collectively resulted in overspending. <\/p>\n

The cause of the issue is exactly the same as in phantom reading. <\/p>\n

Conclusions<\/h2>\n

\nReducing the transaction isolation level in the database is a compromise between security and performance, and the choice of this level should be approached based on the potential risks to the business when certain anomalies arise.<\/p>\n

\nLearn more about the course.
\n<\/a><\/noindex><\/p>\n

Source: 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\udd47What can result from reducing the transaction isolation levels in databases | ProHoster","description":"Hello everyone. This is Vladislav Rodin.","canonical_url":"https:\/\/prohoster.info\/en\/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":"en_US","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\/en\/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\/en\/wp-json\/wp\/v2\/posts\/83337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=83337"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/83337\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/83338"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=83337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=83337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=83337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}