The essential difference between phantom reading and unrepeatable reading

What is the essential difference between phantom reading and unrepeatable reading? This problem may be encountered in our daily work. Through this question, I hope you can gain more. If you don't look at the answer, I don't know if you are sure to answer this question. Let's try it.

1) "non-repeatable read" refers to reading the same data multiple times within a transaction. Before the transaction finishes, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read by the first transaction may not be the same because of the modification of the second transaction. This happens that the data read twice in a transaction is different, so it is said to be unrepeatable.

For example, an editor reads the same document twice, but the author rewrites the document between reads. When the editor reads the document for the second time, the document has changed. The original read cannot be repeated. This problem can be avoided if editors can read the document only after the author has finished writing it.

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

2) Phantom reading 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 data rows in the table. At the same time, the second transaction also modifies the data in the table, which inserts a row of new data into the table.

In that case, later, the user who operates the first transaction will find that there are still rows of data in the table that have not been modified, as if there were hallucinations.

For example, an editor changes a document submitted by the author, but when the production department merges its changes into the main copy of the document, it finds that the author has added unedited new material to the document. This problem can be avoided if no one can add new material to the document until the editors and production departments have finished working on the original document.

In the above example, transaction A reads the data in table T twice. Although setting repeatable read prevents 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 obediently wait for transaction A to commit in order to insert new data into Table T, thus avoiding illusory reading!

The difference between phantom reading and unrepeatable reading is shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you like this article, you might as well share it for more people to see.