Dirty read (dirty read) unrepeatable read (phantom problem) Parsing

Dirty read (dirty read) unrepeatable read (phantom problem) Parsing
1. Dirty read

First, distinguish between dirty pages and dirty data

Dirty pages are modified pages in the memory buffer pool. They are not flushed to the hard disk in time, but are already written to the redo log. It is normal to read and modify the page of the buffer pool, which can improve efficiency and synchronize with flush.

Dirty data indicates that the transaction has modified the row record in the buffer pool, but it has not been committed !!!, If uncommitted row data is read from the buffer pool, it is called dirty read, which violates the isolation of transactions.

Dirty read means that when a transaction is accessing data and modifying the data has not been committed to the database, another transaction also accesses the data, then the data is used.

2. Repeated read is not allowed.

A transaction reads the same data multiple times. When the transaction is not completed, another transaction also accesses the same data. Therefore, the second transaction has been committed due to the modification of the second transaction between the two reads in the first transaction. The data read by the first transaction may be different. In this way, the data read twice in a transaction is different, so it is called non-repeated read. For example, an editor reads the same document twice, but the author overwrites the document between the two reads. The document has been changed when the editor reads the document for the second time. The original reads cannot be repeated. This problem can be avoided if the editor can read the document only after the author has completed the compilation.

3. Phantom read:

It refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion. For example, an editor changes the document submitted by the author, but when the production department merges the changes into the primary copy of the document, the author has added unedited new materials to this document. This issue can be avoided if no one can add new materials to the document before the editors and production departments process the original document.

Database isolation mechanism

The Isolation attribute supports five transaction settings as follows:

L DEFAULT uses the isolation level set by the database (DEFAULT), which is determined by the DBA's DEFAULT settings. MySQL is repeatable_read by DEFAULT.

L READ_UNCOMMITTED will show dirty reads, non-repeated reads, and Phantom reads (lowest isolation level, high concurrency performance)

L READ_COMMITTED: unrepeatable read and phantom read (locking the row being read)

L REPEATABLE_READ will trigger phantom read (Lock all rows read)

L SERIALIZABLE ensures that all situations will not occur (Lock table)

Repeatable reading focuses on modification:
In the same condition, the data you read is read again and the value is different.
Phantom read focuses on adding or deleting
Under the same condition, the number of records read for 1st and 2nd times is different.

	Dirty read	Non-repeated read	Phantom read
Serializable	No	No	No
REPEATABLE READ	No	No	Yes
READ COMMITTED	No	Yes	Yes
Read Uncommitted	Yes	Yes	Yes