The transaction isolation level is a value that determines the level at which inconsistent data is allowed in a transaction, that is, the degree of isolation of one transaction from another. A higher level of isolation improves data accuracy, but it may decrease the number of concurrent transactions. On the other hand, a lower isolation level allows for more concurrent transactions, but reduces the accuracy of the data.

Parallel Access Issues with Transactions

The following problems are possible when executing transactions in parallel:

• lost update (eng. lost update ) - if you simultaneously change one data block with different transactions, one of the changes is lost;
• “Dirty” reading (eng. Dirty read ) - reading the data added or modified by the transaction, which is subsequently not confirmed (rolled back);
• non-repeatable read (eng. non-repeatable read ) - when re-reading in a single transaction, the previously read data is changed;
• phantom read (eng. phantom reads ) - one transaction several times selects multiple rows according to the same criteria during its execution. Another transaction in the intervals between these selections adds or deletes rows or changes the columns of some rows used in the sampling criteria of the first transaction, and ends successfully. The result is that the same samples in the first transaction produce different sets of rows.

Consider the situations in which the occurrence of these problems.

Lost update

A situation where, when simultaneously changing a single data block with different transactions, one of the changes is lost.

Suppose there are two transactions executed simultaneously:

In both transactions, the value of the f2 field changes, upon their completion the field value should be increased by 45. In fact, the following sequence of actions may occur:

1. Both transactions simultaneously read the current state of the field. Exact physical simultaneity is not necessary here, it is enough that the second-order read operation is executed before another transaction records its result.
2. Both transactions calculate the new field value, adding, respectively, 20 and 25 to the previously read value.
3. Transactions attempt to write the result of the calculation back to the f2 field. Since it is impossible to physically simultaneously perform two records, in reality one of the write operations will be performed earlier, the other later. In this case, the second write operation will overwrite the result of the first.

As a result, the value of the f2 field after the completion of both transactions may increase not by 45, but by 20 or 25, that is, one of the data-changing transactions will “disappear”.

"Dirty" reading

Reading data added or modified by a transaction that is not subsequently confirmed (rolled back).

Suppose there are two transactions opened by various applications in which the following SQL statements are executed:

In transaction 1, the value of the f2 field is changed, and then in transaction 2, the value of this field is selected. After this, transaction 1 is rolled back. As a result, the value obtained by the second transaction will differ from the value stored in the database.

Non-repeatable reading

The situation when, when re-reading in one transaction, the previously read data is changed.

Suppose there are two transactions opened by various applications in which the following SQL statements are executed:

In transaction 2, the value of the f2 field is selected, then in transaction 1, the value of the f2 field is changed. If you try again to select a value from the f2 field in transaction 2, a different result will be obtained. This situation is especially unacceptable when data is read in order to partially modify it and write back to the database.

Phantom read

The situation when, when re-reading in the same transaction, the same sample gives different sets of rows.

Suppose there are two transactions opened by various applications in which the following SQL statements are executed:

In transaction 2, a SQL statement is executed using all the values ​​of the f2 field. Then, in transaction 1, a new line is inserted, causing the repeated execution of the SQL statement in transaction 2 to produce a different result. This situation is called phantom reading. It differs from non-repeating reading in that the result of repeated access to data has changed not because of the change / deletion of this data itself, but because of the appearance of new (phantom) data.

Isolation levels

“ Transaction isolation level ” means the degree of protection provided by the internal DBMS mechanisms (that is, not requiring special programming) against all or some of the above-mentioned data inconsistencies that arise during the parallel execution of transactions. The SQL-92 standard defines a scale of four isolation levels: Read uncommitted, Read committed, Repeatable read, Serializable. The first one is the weakest, the last one is the strongest, each subsequent one includes all the previous ones.

Read uncommitted (read uncommitted data)

The lowest (zero) isolation level. It guarantees only the absence of lost updates ^[1] . If several parallel transactions try to change the same row of the table, then in the final version the row will have the value defined by the entire set of successfully completed transactions. In this case, it is possible to read not only logically inconsistent data, but also data whose changes have not yet been recorded.

A typical way to implement this level of isolation is to block data for the duration of a change command, which ensures that the change commands for the same lines that are run in parallel are actually executed sequentially and none of the changes are lost. Read-only transactions are never blocked at this isolation level.

Read committed (read fixed data)

Most industrial DBMS, in particular, Microsoft SQL Server, PostgreSQL and Oracle, use this level by default. At this level, protection against a rough, “dirty” reading is provided, however, in the course of one transaction, the other can be successfully completed and the changes made by it are fixed. As a result, the first transaction will work with a different data set.

The implementation of the completed reading can be based on one of two approaches: blocking or versioning.

Block readable and changeable data.

The fact is that the reading transaction blocks the readable data in a shared mode, as a result of which a parallel transaction trying to change this data is suspended, and the writing transaction locks the modified data for read transactions running at the level read committed or higher to its completion, thus hindering dirty reading.

Save multiple versions of parallel variable lines.

Each time a line is changed, the DBMS creates a new version of this line, with which the transaction that changed the data continues to work, while any other “reading” transaction returns the last committed version. The advantage of this approach is that it provides greater speed, as it prevents blocking. However, it requires, in comparison with the first, a significantly larger expenditure of RAM, which is spent on storing versions of strings. In addition, when parallel data is modified by several transactions, a situation may arise where several parallel transactions will make inconsistent changes to the same data (since there are no locks, nothing will prevent it). Then the transaction that is committed first will save its changes to the main database, and the remaining parallel transactions will be impossible to fix (as this will lead to the loss of the first transaction update). The only thing that the DBMS can do in such a situation is to roll back the remaining transactions and give the error message "The record has already been changed."

The specific implementation method is chosen by the DBMS developers, and in some cases can be customized. So, by default, MS SQL uses locks, but (in version 2005 and higher) when setting the READ_COMMITTED_SNAPSHOT parameter of the database, it switches to a versioning strategy, Oracle initially works only according to the versioned version. Informix, you can prevent conflicts between read and write transactions by setting the USELASTCOMMITTED configuration parameter (starting with version 11.1), and the reading transaction will receive the latest confirmed data ^[2]

Repeatable read

The level at which the reading transaction "does not see" the changes in the data that it had previously read. However, no other transaction can change the data read by the current transaction until it is completed.

The locks in dividing mode are applied to all data read by any transaction instruction and are kept until completion. This prevents other transactions from changing rows that were read by an incomplete transaction. However, other transactions may insert new rows matching the search terms of the instructions contained in the current transaction. When the instruction is restarted, the current transaction will extract new lines, which will lead to phantom reading. Considering that the dividing locks are maintained until the completion of the transaction, and not removed at the end of each instruction, the degree of concurrency is lower than with the isolation level READ COMMITTED. Therefore, using data and higher transaction levels unnecessarily is usually not recommended.

Serializable

The highest level of isolation; transactions are completely isolated from each other, each is performed as if there are no parallel transactions. Only at this level are parallel transactions not subject to the effect of "phantom reading."

Support transaction isolation in real DBMS

Transactional DBMSs do not always support all four levels, and they can also introduce additional ones. Various nuances in providing isolation are also possible.

Thus, Oracle basically does not support the zero level, since its implementation of transactions excludes “dirty reads”, and formally does not allow setting the level of Repeatable read, that is, it supports only Read committed (by default) and Serializable. At the same time, at the level of individual commands, it actually guarantees repeatability of reading (if the SELECT command in the first transaction selects a set of rows from the database, and at this time the parallel second transaction changes some of these rows, then the resulting set obtained by the first transaction will be contain unchanged rows, as if there was no second transaction). Oracle also supports so-called READ-ONLY transactions that are Serializable, but cannot change the data themselves.

Microsoft SQL Server supports all four standard transaction isolation levels, and additionally the SNAPSHOT level, located between Repeatable read and Serialized. A transaction operating at this level sees only those changes in the data that were recorded before it was launched, as well as changes made by it itself, that is, it behaves as if it received a snapshot of the database data when it starts up and works with it.

Behavior at different levels of isolation

"+" - prevents, "-" - does not prevent.

MySQL: transaction isolation levels

 CREATE TABLE test (id INT, value VARCHAR(255)) ENGINE=InnoDB; 

 START TRANSACTION; INSERT INTO test(id, value) VALUES (1, 'test'), (2, 'test 2'); SELECT * FROM test; COMMIT; SELECT * FROM test; 

 SHOW VARIABLES LIKE '%tx_isolation%'; 

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
 

  SELECT @@ global.tx_isolation;
 SELECT @@ tx_isolation;