Summarize the four isolation levels of the SQL92 standard

                                                                                                               

Definition

• Read uncommitted (read uncommitted content)

At the Read uncommitted level , modifications in a transaction are visible to other transactions even if they are not committed. Transactions can read uncommitted data, which is also called a dirty read. This level can cause many problems. In terms of performance, Read uncommitted is not much better than other levels, but it lacks many of the benefits of other levels. Unless there are very necessary reasons, it is rarely used in actual applications.

• Read committed (Read committed content)

The default isolation level of most database systems is Read committed (but MySQL is not). Read committed satisfies the simple definition of isolation mentioned earlier: when a transaction starts, only modifications made by committed transactions can be seen. In other words, any modifications made by a transaction from the beginning to the time it is committed are not visible to other transactions. This level is sometimes called nonrepeatable read, because executing the same query twice may result in different results.

• Repeatable read (repeatable read)

Repeatable read solves the dirty read problem. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. However, in theory, the repeatable read isolation level still cannot solve another phantom read (Phantom read) problem. The so-called phantom read means that when a transaction reads records in a certain range, another transaction inserts a new record in the range. When the previous transaction reads the records in the range again, it will Produce phantom rows. InnoDB and XtraDB storage engines solve the phantom read problem through multi-version concurrency control (MVCC).

Repeatable read is the default transaction isolation level of Mysql. InnoDB mainly obtains high concurrency by using MVVC and uses a strategy called next-key-locking to avoid phantom reads.

• Serializable

Serializable is the highest isolation level. It avoids the phantom read problem mentioned earlier by forcing transactions to be serialized. To put it simply, Serializable will add a lock to every row of data read, so it may cause a lot of timeout and lock acquisition problems. This isolation level is rarely used in actual applications. This level should be considered only when it is very necessary to ensure data consistency and the absence of concurrency is acceptable.

Isolation Level

ANSI SQL92 P68-69

Level (Isolation Level)	Dirty read	Non-repeatable read	Phantom
Read uncommitted (Read uncommitted content)	✓	✓	✓
Read committed (Read committed content )	×	✓	✓
Repeatable read	×	×	✓
Serializable	×	×	×

Recommended study: "mysql video tutorial" ##

The above is the detailed content of Summarize the four isolation levels of the SQL92 standard. For more information, please follow other related articles on the PHP Chinese website!