Four isolation levels of database transactions

In the database operation, in order to effectively guarantee the correctness of the concurrent read data, the transaction isolation level is proposed.

There are 4 isolation levels for database transactions, from low to high to read uncommitted (unauthorized read, read UNCOMMITTED), Read committed (authorization read, read commit), REPEATABLE read (repeatable read), Serializable (serialization), these four levels can solve the problems of dirty reading, non-repeatable reading, and Phantom reading one after the other.

The question is raised:
The transaction isolation level is raised because the following scenarios may occur during the execution of a transaction:

1, update lost
Two transactions update a row of data at the same time, and an update to the data by one transaction overwrites the update of the data by another transaction. This is because the system does not perform any lock operations, so concurrent transactions are not isolated.
2. Dirty Reading
One transaction reads the result of a data operation that is not committed by another transaction.
3. Non-repeatable read (non-repeatable Reads): One transaction repeats two times for the same row of data, but it gets different results.
The following conditions are included:
(1) virtual read: After a transaction T1 read a data, the transaction T2 modifies it, and when the transaction T1 reads the data again, it gets a different value than the previous one.
(2) Phantom Reading (Phantom Reads): The transaction is in the process of two queries, the results of the second query contains data that did not appear in the first query or missing data from the first query (this does not require two queries of the same SQL statement). This is due to the fact that another transaction was inserted into the data during the two queries.

Here's a look at the differences between these types of transaction isolation levels and the issues that may arise:

READ UNCOMMITTED (unauthorized read, READ UNCOMMITTED):

If one transaction has already started writing data, the other transaction does not allow simultaneous writes, but allows other transactions to read the row data. This isolation level can be achieved through an "exclusive write lock".

Avoid updates being lost, but dirty reads may occur. That is, transaction B reads data that is not committed by transaction A.

Read Committed (authorized read, read commit):

Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

This isolation level avoids dirty reads, but may not be repeatable. Transaction a reads the data in advance, transaction B immediately updates the data, commits the transaction, and when transaction a reads the data again, the data has changed.

REPEATABLE READ (Repeatable Read):

Transactions that read data prohibit write transactions (but allow read transactions), and write transactions prohibit any other transactions.

Non-repeatable reads and dirty reads are avoided, but Phantom reads can sometimes occur. This can be achieved through "shared read lock" and "exclusive write lock".

Serializable (serialization):

Provides strict transaction isolation. It requires the transaction to serialize execution, and the transaction can be executed one after the other, but not concurrently. If transaction serialization is not possible only through row-level locks, other mechanisms must be ensured that the newly inserted data is not accessed by the transaction that just performed the query operation.

Serialization is the highest transaction isolation level, at the expense of the highest cost, low performance, and is rarely used, at which time transactions are executed in order not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

The higher the isolation level, the greater the integrity and consistency of the data, but also the greater the impact on concurrency performance. For most applications, it is preferable to set the isolation level of the database system to read Committed. It avoids dirty reads and has better concurrency performance. Although it causes concurrency problems such as non-repeatable reads, Phantom reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.

The default level for most databases is read committed, such as SQL Server, Oracle.
The default isolation level for MySQL is repeatable read.

Original link: 49283575

Four isolation levels of database transactions