Transaction isolation levels

2.1 Read uncommitted

The "transaction isolation level" refers to the degree of protection provided by the internal mechanisms of the DBMS (that is, not requiring special programming) from all or some of the above types of data inconsistencies that occur during parallel execution of transactions. The SQL-92 standard defines a scale of four isolation levels:

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

The first of them is the weakest, the last is the strongest, each subsequent one includes all the previous ones.

The lowest (first) isolation level. If several parallel transactions attempt to modify the same table row, then the final row will have a value determined 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 isolation level is to lock data while the change command is being executed, which ensures that modification commands on the same rows run in parallel are actually executed sequentially and none of the changes are lost. Read-only transactions never block under this isolation level.

2.2 Read committed

Most industrial DBMS, in particular Microsoft SQL Server, PostgreSQL and Oracle, use this level by default. At this level, protection against draft, “dirty” reading is provided, however, during the operation of one transaction, another 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 a complete read can be based on one of two approaches: blocking or versioning.

Blocking readable and mutable data.

It consists in the fact that the writing transaction blocks mutable data for reading transactions operating at the read committed level or higher until it completes, thus preventing "dirty" reading, and the data locked by the reading transaction is released immediately after the operation is completed SELECT( thus, a "non-repeatable read" situation can occur at a given isolation level).

Saving multiple versions of rows that change in parallel.

Each time a row is changed, the DBMS creates a new version of this row, 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 is faster because it prevents blocking. However, it requires, in comparison with the first one, a significantly greater consumption of RAM, which is spent on storing row versions.

In addition, when multiple transactions modify data in parallel, a situation can arise where multiple parallel transactions make inconsistent changes to the same data (since there are no locks, nothing will prevent this from happening). Then the transaction that commits first will save its changes in the main database, and the remaining parallel transactions will be impossible to commit (as this will lead to the loss of the update of the first transaction). The only thing that the DBMS can do in such a situation is to roll back the rest of the transactions and issue an error message “The record has already been changed”.

A specific implementation method is chosen by the DBMS developers, and in some cases it can be customized. So, by default, MS SQL uses locks, but (in version 2005 and higher) when setting the READ_COMMITTED_SNAPSHOTdatabase parameter, it switches to the versioning strategy, Oracle initially works only according to the versioned scheme. In Informix, you can prevent conflicts between read and write transactions by setting a configuration option USELASTCOMMITTED(as of version 11.1) that causes the read transaction to receive the latest committed data.

2.3 Repeatable read

The level at which a reading transaction "does not see" changes to the data it has previously read. At the same time, no other transaction can change the data read by the current transaction until it ends.

Locks in shared mode are applied to all data read by any instruction in a transaction and are held until the transaction completes. This prevents other transactions from modifying rows that were read by the pending transaction. However, other transactions may insert newlines that match the search conditions for instructions contained in the current transaction. When the statement is restarted by the current transaction, new rows will be fetched, resulting in a phantom read.

Given that shared locks are held until the end of the transaction, rather than being released at the end of each statement, the degree of concurrency is lower than isolation level READ COMMITTED. Therefore, it is generally not recommended to use this and higher transaction levels unnecessarily.

2.4 Serializable

The highest level of isolation; transactions are completely isolated from each other, each one is executed as if there were no parallel transactions. It is only at this level that concurrent transactions are not subject to the "phantom read" effect.

2.5 Support for transaction isolation in real DBMS

Transactional DBMS do not always support all four levels, and may also introduce additional ones. There are also various nuances in providing insulation.

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

And Microsoft SQL Server supports all four standard transaction isolation levels, and additionally, the SNAPSHOT level, at which the transaction sees the data state that was committed before it was launched, as well as the changes made by itself, that is, it behaves as if it received at startup, a snapshot of the DB data and works with it. The difference from Serialized is that no locks are used, but as a result, committing changes may not be possible if a concurrent transaction has changed the same data before; in this case, the second transaction, when attempting to execute, COMMITwill raise an error message and be cancelled.