Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: how to avoid phantom inserts?

    How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I need you to explain what is happening a little better.

    Are you using SELECT with NOLOCK and getting bad results?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Phantom Inserts ??? Is that in anyway related to ghost records ? I am interested in knowing more.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2004
    Posts
    15
    Sean,

    We are just deciding how to handle the situation when one transaction selects a set of rows, then another transaction inserts rows that meet the same criteria, when the first transaction re-executes the query, a different set results. My question is, how to handle this?

    Thanks.

  6. #6
    Join Date
    Sep 2004
    Posts
    15
    Brett,

    Read your blog entry. I am not referring to "ghost records/ghost exorciser" here. But it is good to know about them. Any thoughts on basic phantom inserts?

    Thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    got code, DDL, sample data, and expected results?

    [yoda style] answer me this? [/yoda style]

    Is the first transaction doing something other than selecting data?

    If not I do not see the issue here other than you want to select data that does not yet exist.

    If not you have an age old problem. I would strongly discourage attempting any kind of explicit table lock. For the second scenario, I need to see code.

    I do not think read uncommitted solves this completely.
    Last edited by Thrasymachus; 09-22-05 at 12:02.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Sep 2004
    Posts
    15
    I do not have code or sample data. But here is how I will describe the scenario:

    The first transaction starts reading names starting with "A" from EMP table. At this time there is only one record "Adam" starting with "A" and hence the query returns 1.

    Meanwhile a second transaction adds a new employee named "Ashton" to EMP table.

    Now back in the first transaction it re-runs the same query to select names starting with "A" in EMP table. This time it gets 2 rows instead of 1 row.

    This phantom insert (or phantom read) is allowed in SQL Server since it's deafult isolation level is "Read Committed". According to the book the only way to avoid this is to change the isolation level to "Serializable" and then use "Key-Range Locking". My question is.. is there another strategy out there to avoid the phantom read without changing the isolation level to "serializable" and using "Key-Range Locking"? Infact one can use key range locking only if index scan is used.

    I hope I am able to answer atleast part of your question.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What are you saying?

    That your sproc did a read and you only want to affect the one row?

    Save the data to a table variable and perofrm the modifications with a join.

    If not, you gotta provide us with some code examples..

    Is this homework?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    PS

    You want to use correct terminology..there is no such thing as "phantom rows (not records btw)"

    You in a database man....start ackin like it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh, like WE all ack like database mans. Reference the Yak Corral, please...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by unmesh
    The first transaction starts reading names starting with "A" from EMP table. At this time there is only one record "Adam" starting with "A" and hence the query returns 1.
    ...this sounds suspiciously like you are using a CURSOR (oohh..scary), and that may be the real source of your problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Unimesh I'd have to ask Why

    ".......in the first transaction it re-runs the same query......."

    If your Logic requires you to only deal with previously commited data / If your Logic cannot deal with newly commited data

    There's possibly a good reason for it but....... are you sure it has to ?

    Maybe your Logic/processing is too Serial (unfortunately an all 2 common mistake)

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  14. #14
    Join Date
    Sep 2004
    Posts
    15
    Quote Originally Posted by Brett Kaiser
    PS

    You want to use correct terminology..there is no such thing as "phantom rows (not records btw)"

    You in a database man....start ackin like it....
    Not sure what you mean. I have used "phantom insert" or "phantom read" to describe the scenario and both are correct terminology. I never used "phantom rows"!

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, my bad.

    There is no such thing as those either.

    Everything happens for a reason.

    Is the blind dude correct?

    Are you using a cursor?

    Can you post the sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •