What is the difference between non-repeatable reads and phantom reads?

Difference: The focus of non-repeatable reading is modification; under the same conditions, the values ​​read for the first and second times are different. The focus of phantom reading is adding or deleting; under the same conditions, the number of records read out for the first and second times is different. From a control perspective, non-repeatable reading only needs to lock records that meet the conditions, while phantom reading needs to lock records that meet the conditions and similar records.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Refined explanation:

The focus of non-repeatable reading is to modify:

Same conditions , the data you have read, when you read it again, you find that the value is different

The focus of phantom reading is to add or delete

The same conditions, No. 1 The number of records read out twice and the second time are different

Of course, from the overall results, it seems that the results of the two reads are inconsistent.

But if you From a control point of view, the difference between the two is relatively large

• For the former, only records that meet the conditions need to be locked

• For the former The latter requires locking records that meet the conditions and those that are similar

Details:

1) "Not possible "Repeated reading" refers to reading the same data multiple times within a transaction. Before this transaction ends, another transaction also accesses the same data. Then, between the two reads of data in the first transaction, due to the modification of the second transaction, the data read twice by the first transaction may be different. In this way, the data read twice within a transaction is different, so it is called non-repeatable read. For example, an editor reads the same document twice, but between reads the author rewrites the document. When the editor reads the document a second time, the document has changed. Raw reads are not repeatable. This problem can be avoided if editors can only read the document after the author has finished writing.

To avoid this situation, you can usually use set tran isolation level repeatable read to set the isolation level, so that transaction A When reading the data in table T twice, if transaction B attempts to change the data in table T (the details are when transaction A reads the data), it will be blocked until transaction A commits! This ensures the consistency of the data read twice by transaction A.

2) Phantom reading refers to a phenomenon that occurs when transactions are 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 inserts a row of new data into the table. Then, in the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if a hallucination has occurred. For example, an editor changes a document submitted by an author, but when production merges their 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 until the editors and production department have finished working on the original document.

Related free learning recommendations: mysql video tutorial

The above is the detailed content of What is the difference between non-repeatable reads and phantom reads?. For more information, please follow other related articles on the PHP Chinese website!