locked
difference between Non-repeatable read and Phantom read RRS feed

  • Question

  • Can anyone tell me the difference between Non-repeatable read and Phantom read ?

    I am completely mixed up and I have to know the difference as soon as possible .

    From BOL: Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction

    Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time

     

    Are Inconsistent analysis and Phantom reads the same? when do you get different data from   the same query   that run more than once?

    Isn't it when   another transaction has performed a delete or Insert action   which made the first transaction nonrepeatable read?

    in case so, the definition of the phantom and inconsistent analysis(nonrepetable read) is the same except for update operation?

    Or the definition for phantom read must be changed and delete action should be removed.since in REPEATABLE READ isolation level

    which phantom read problem existed, delete is not allowed but Other transactions can insert new rows that match the search conditions of statements issued by the current transaction that make me think phantom reads occur when only insert action   is performed against a row that belongs to a range of rows being read by a transaction .

     

    any help would be greatly appreciated

     

    Wednesday, February 10, 2010 8:20 PM

Answers

  • Nonrepeatabel read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.

    Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 11, 2010 8:29 AM
  • Yousef,

    << So the definition for Phantom which are stated in
    Microsoft Documentations  must be changed.>>

    Possibly.The problem is the same as always when it comes to terminology; Who has the true definition? Iwould probably pick up the ol' Transaction PRocessing Concepts and Techiques by Gray and Reuter and call that the "truth". But perhaps ANSI SQL defines these phenomena? I'm on the road now, so I don't have access to the books. Wikipedia? Sure, we can look, but would we trust thast definition? So, unless we can agree on who owns the truth, we can only speculate. What we *can* agree on is that each of the traditional isolation level builds on the earlier level. I.e., level 3 (serializable), builds on 2 (repeatable read); according to ANSI SQL (as I can remember). This means that we cannot have any phenomena in a higher level that aren't allowed in a lower level. So, since we can't delete read rows in repeatable read, then we can't in serializable either. Which renaders the question more academix than read- combined with difficulties on agreeing who has the truth makes it a bit difficult to come to consencus...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, February 15, 2010 10:34 PM

All replies

  • Nonrepeatabel read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.

    Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, February 11, 2010 8:29 AM
  • Thank you so much Tibor.I do agree on what you said .So the definition for Phantom which are stated in
    Microsoft Documentations  must be changed.I was teaching my students about concurrency problems  based on what  the BOL  was told and i figured out something is wrong about phantom read.

    here is a excerpt from  http://technet.microsoft.com/en-us/library/ms171845.aspx
    Phantom reads.
    Phantom reads occur when an insert or a delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the subsequent read, because of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the subsequent read of the transaction shows a row that did not exist in the original read.


    and the following is from http://msdn.microsoft.com/en-us/library/aa213029(SQL.80).aspx
    Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction

    also I want to know if the following table is correct or not .It is such a pity that Microsoft did not mention anything about Lost Update in this link http://msdn.microsoft.com/en-us/library/ms378149.aspx
     

    Dirty Read

    Lost Update

    Unrepeatable

    Read

    Phantom Records

    Read uncommitted

    yes

    yes

    yes

    yes

    Read committed

    No

    yes

    yes

    yes

    Repeatable read

    No

    no

    no

    yes

    Serializable

    No

    no

    no

    no

    I really have no idea about snapshot as i am new to this isolation level but I think it should be like Serializable.
    Thursday, February 11, 2010 9:00 PM
  • I really have no idea about snapshot as i am new to this isolation level but I think it should be like Serializable.

    Affirmative. Snapshot Isolation Level similarly to Serializable: 4 "no"-s.


    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Edited by Kalman Toth Friday, November 10, 2017 3:39 PM
    Monday, February 15, 2010 8:28 PM
  • Yousef,

    << So the definition for Phantom which are stated in
    Microsoft Documentations  must be changed.>>

    Possibly.The problem is the same as always when it comes to terminology; Who has the true definition? Iwould probably pick up the ol' Transaction PRocessing Concepts and Techiques by Gray and Reuter and call that the "truth". But perhaps ANSI SQL defines these phenomena? I'm on the road now, so I don't have access to the books. Wikipedia? Sure, we can look, but would we trust thast definition? So, unless we can agree on who owns the truth, we can only speculate. What we *can* agree on is that each of the traditional isolation level builds on the earlier level. I.e., level 3 (serializable), builds on 2 (repeatable read); according to ANSI SQL (as I can remember). This means that we cannot have any phenomena in a higher level that aren't allowed in a lower level. So, since we can't delete read rows in repeatable read, then we can't in serializable either. Which renaders the question more academix than read- combined with difficulties on agreeing who has the truth makes it a bit difficult to come to consencus...
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, February 15, 2010 10:34 PM
  • Thank you so much Tibor.I referred this issue to microsoft and they have promised to update it in a future version of SQL Server Books On Line.
    Thursday, February 25, 2010 6:33 PM
  • The above data is correct.and Lost Update problem occurs only on Read uncommited and read Commited Isolation levels.

    eg.

    T1-reads row1 which is 20.

    T2-reads row1 which is 20. the same.

    T1.updates row1 to 30.

    T2. is still watching 20.and updates this value to 40.

               at this time the row1 value becomes 40 and T1's update is lost.

    if the isolation level were serializable .T2 reads the value 30 and updates it..

    you can try this on sql server 2005 and possible method for solving  this problem is by using Optimistic Concurrency method.       tnx

    Monday, August 23, 2010 7:35 AM
  • you can try this on sql server 2005 and possible method for solving  this problem is by using Optimistic Concurrency method.       tnx


    Following is an article on Optimistic Concurrency method:

    http://www.sqlusa.com/articles2005/rowversion/

    It is important to note that business processes in the enterprise should be setup to avoid or at least minimize concurrency conflicts in the database.

    Article: Database Concurrency Conflicts in the Real World


    Kalman Toth SQL SERVER & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016




    • Edited by Kalman Toth Friday, November 10, 2017 3:39 PM
    Monday, August 23, 2010 8:06 AM
  • Nonrepeatabel read: If somebody performed UPDATE or DELETE of any of the rows you read earlier.

    Phantom: If anybody INSERTed a row within the range you had for an earlier query (i.e., you see new rows).
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

    Perhaps it's a stupid question, Why this? Please explain it in details.
    • Proposed as answer by Shaamil Friday, March 25, 2011 9:29 AM
    Thursday, September 30, 2010 4:17 AM
  • Thanks for your explanation. But in your example you did not explain how lost update occurs in READ COMMITTED ISOLATION level. Can you please explain with locking. 
    Monday, October 30, 2017 2:22 PM