MySQL InnoDB four transaction levels and dirty read, non-repeated read, phantom read

MySQL InnoDB four transaction levels and dirty read, non-repeated read, phantom read

MySQL InnoDB transaction isolation level has four levels, the default is "repeatable read" (REPEATABLE READ).

1). Uncommitted read (READUNCOMMITTED). Another transaction modified the data, but it has not yet been committed, and the SELECT in this transaction will read these uncommitted data (dirty reads) (the lowest isolation level, high concurrency performance).

2). Submit to read (READCOMMITTED). This transaction reads the latest data (after other transactions commit). The problem is that in the same transaction, the same SELECT two times before and after will read different results (not repeat reading). There will be non-repeatable read, phantom read problems (lock the line being read)

3). Repeatable read (REPEATABLEREAD). In the same transaction, the result of the SELECT is the state at the point in time when the transaction started. Therefore, the results read by the same SELECT operation will be consistent. However, there will be phantom reading (explained later). There will be a phantom read (lock all rows read).

4). Serialization (SERIALIZABLE). Read operations will implicitly acquire shared locks, which can guarantee mutual exclusion (lock table) between different transactions.

The four levels are gradually enhanced, and each level solves a problem.

1). Dirty read. Another transaction modified the data, but it has not yet been committed, and the SELECT in this transaction will read these uncommitted data.

2). Do not repeat reading. After solving the dirty read, you will encounter that during the execution of the same transaction, another transaction submits new data, so the results of the data read twice in this transaction will be inconsistent.

3). Phantom reading. It solves the problem of non-repetitive reading and ensures that in the same transaction, the result of the query is the state (consistency) at the beginning of the transaction. However, if another transaction submits new data at the same time, when the transaction is updated again, these new data will be "surprised" to be discovered. It seems that the data read before is the illusion of "ghost".

specifically:

1). Dirty read

First distinguish between dirty pages and dirty data

Dirty pages are pages that have been modified in the buffer pool of the memory and have not been flushed to the hard disk in time, but have been written to the redo log. It is normal to read and modify the page of the buffer pool, which can improve efficiency, and flush can be synchronized. Dirty data means that the transaction has modified the row record in the buffer pool, but it has not yet been committed! ! ! , If you read the uncommitted row data in the buffer pool at this time, it is called dirty read, which violates the isolation of the transaction. Dirty read refers to when a transaction is accessing data, and the data is modified, and this modification has not been submitted to the database , at this time, another transaction also accesses the data, and then uses the data.

2). Non-repeatable reading

Refers to reading the same data multiple times within a transaction. Before this transaction is over, another transaction also accesses the same data. Then, between the two read data in the first transaction, the second transaction has been committed due to the modification of the second transaction. Then the data read twice in the first transaction may be different. In this way, the data read twice in a transaction is different, so it is called non-repeatable read. For example, an editor reads the same document twice, but between the two reads, the author rewrites the document. When the editor reads the document for the second time, the document has been changed. The original read cannot be repeated. This problem can be avoided if the editor can read the document only after the author has finished writing

3). Phantom reading:

Refers to a phenomenon that occurs when the transaction is not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification is to insert a new row of data into the table. Then, the user who operates the first transaction in the future will find that there are still unmodified data rows in the table, as if an illusion occurred. For example, an editor changes the document submitted by the author, but when the production department merges its changes into the master copy of the document, it is discovered that the author has added new unedited material to the document. This problem can be avoided if no one can add new material to the document before the editor and the production department finish processing the original document.

Reference: https://cloud.tencent.com/developer/article/1055523 MySQL InnoDB four transaction levels with dirty reads, non-repetitive reads, and phantom reads-Cloud + Community-Tencent Cloud