Hello everyone. This is Vladislav Rodin. Currently, I am the head of the ‘High Load Architect’ course at OTUS, and I also teach courses dedicated to software architecture.
In 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 , which is currently open for enrollment.
Introduction
In 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) in this abbreviation.
Isolation
Isolation addresses the issue of data access in a concurrent environment, effectively providing protection against race conditions. Ideally, isolation means serialization, which is a property ensuring that the result of executing transactions in parallel is the same as if they were executed sequentially. The main problem with this property is that it is technically difficult to achieve and, as a consequence, it greatly impacts system performance. This is why isolation is often relaxed, accepting the risks of certain anomalies, which we will discuss below. The possibility of various anomalies characterizes the level of transaction isolation.
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.
Dirty write
The essence of the anomaly is that transactions can overwrite uncommitted data.
This 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.
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.
Dirty read
Dirty read refers to the reading of uncommitted data.
Problems arise when actions or decisions need to be based on the sample.
To fix the anomaly, a read lock can be placed, but this significantly impacts performance. It's much easier to state that the original state of the data (before the writing begins) must be preserved in the system for rollback purposes. Why not read from there? It's relatively inexpensive, which is why most databases disable dirty reads by default.
Lost update
Lost update means lost updates, and the translation accurately reflects the essence of the problem:
In 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, 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, which allows reading the data but does not permit changes.
Cursor lost update
For 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. The contents of a cursor are described by the select.
Why is a cursor needed? The thing is, some databases offer locks on all records selected by the select (read stability), or only on the record where the cursor currently is (cursor stability). Under cursor stability, a short lock is implemented, which reduces the number of locks in cases where we iterate over a large dataset. Therefore, the lost update anomaly is highlighted separately for cursors.
Non-repeatable read
Non-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.
Why 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.
These data anomalies cease to occur when adding two-phase locking or using the MVCC mechanism, which I would like to discuss separately.
Phantom read
A phantom read refers to reading data that has been added by another transaction.
For example, you can observe an incorrect selection of the cheapest product when this anomaly occurs.
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.
Read skew
Read skew occurs when we are working with multiple tables whose content must change consistently.
Suppose we have tables representing posts and their metadata:
One transaction reads from the tables, another modifies them:
As a result of executing transaction T1, the post has title = Good, while updated_by = T2, which represents some inconsistency.
In fact, this is a non-repeatable read, but across multiple tables.
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.
Write skew
This 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:
The 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.
This is the same non-repeatable read. As an option, selects can place locks on these records.
Write skew and read skew are combinations of the previous anomalies. We can consider write skew, which is essentially a phantom read. Let’s take a look at a table that includes employee names, their salaries, and the projects they are working on:
As 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.
The cause of the issue is exactly the same as in phantom reading.
Conclusions
Reducing 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.
Source: habr.com
