locked
Non repeatable read and Phantom read RRS feed

  • Question

  • I am aware of these phenomenons we call non repeatable reads and phantom reads.
    Except I dont understand why they are considered a problem.

    Phantom read: retrieve select query and get a result of lets say 2 rows, then someone else does an insert and after that you do another select statement and you get a result of 3 rows.
    So what's wrong with this and why would we want it any other way, if someone does an insert, why is it considered a problem to be actually up to date with our select statements rather than out of date?

    Same story with non repeatable reads, i just dont see the issue with them, can anyone explain?

    Regards

    Sunday, April 15, 2012 12:45 PM

Answers

  • >>But a phantom read requires 2 seperate select queries right? In Chirag's scenario only 1 >>query takes place?

    I gave you a simple example 

    It does not need to SELECT   

    from BOL

    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.

    • Marked as answer by amber zhang Monday, April 23, 2012 8:51 AM
    Wednesday, April 18, 2012 12:35 AM

All replies

  • I would not considered both Phanton Reads and Non Repetable Reads as problematic in *all situations* however in certain scenario you want to avoid the problem they cause (phantom read or non repeatable read) and you should be aware of implications of those and avoid it using proper isolation level. For e.g.

    Phantom Reads

    consider a scenario, in which company you work for is giving out bonus to employees 
    You are writing a query to figure out share of bonus per each employee (who contributed to the profit.) Now if you are allowed
    to insert a new employee while qurey is figuring out bonus share you will get less bonus. In such scenario you want to 
    avoid phanton read, no new employees can be added while query is working on figuring out bonus to the employees.
    • Edited by Chirag Shah Sunday, April 15, 2012 2:19 PM
    • Proposed as answer by amber zhang Monday, April 16, 2012 2:25 AM
    Sunday, April 15, 2012 2:04 PM
  • To add to Chirag's response, whether phantom reads are an issue depends on the task at hand.  Typically, one uses the default READ_COMMITTED isolation level unless there is a reason to do otherwise.  More restrictive isolation levels like REPEATABLE_READ or SERIALIZABLE can be used when needed but at the cost of less concurrency.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by amber zhang Monday, April 16, 2012 2:25 AM
    Sunday, April 15, 2012 2:22 PM
  • But a phantom read requires 2 seperate select queries right? In Chirag's scenario only 1 query takes place?
    Tuesday, April 17, 2012 8:30 PM
  • >>But a phantom read requires 2 seperate select queries right? In Chirag's scenario only 1 >>query takes place?

    I gave you a simple example 

    It does not need to SELECT   

    from BOL

    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.

    • Marked as answer by amber zhang Monday, April 23, 2012 8:51 AM
    Wednesday, April 18, 2012 12:35 AM