The difference between phantom read and non-repeatable read

The difference between phantom reading and non-repeatable reading

1) "Non-repeatable reading" refers to reading the same data multiple times within a transaction data. 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 set the isolation level with set tran isolation level repeatable read, 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 he is hallucinating.

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.

Still in the above example, transaction A needs to read the data in table T twice. Although setting repeatable read can prevent transaction B from modifying the data, transaction B can insert new data into table T. .

How to prevent this problem, we can consider setting the highest transaction isolation level set tran isolation level serializable. As a result, transaction B can only wait obediently for transaction A to be submitted before inserting new data into table T, thus avoiding phantom reads!

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