Results 1 to 4 of 4

Thread: Phanton Reads

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Phanton Reads

    So there's a legacy application that performs a heckofalotta updates to a table (they hadn't heard of set based operations apparently). Finally a stored procedure is executed that, in one part, counts the number of rows meeting certain conditions, herein lies the problem.

    Apparently, this sproc is occasionally returning incorrect results due to uncommitted transactions causing some headaches.

    So, first suggestion was to rewrite the existing code in a single transaction, but this is apparently not an option because of the way the app is built

    My second suggestion was to set the transaction isolation level to read uncommitted, but if any of the transactions did not commit then we'll still get incorrect results (tell me if I'm wrong here, this is just my understanding of the isolation).

    What are the other options here? The sproc must only be allowed read when all the previous transactions have completed.

    Apologies if this doesn't make huge amounts of sense; I haven't actually seen this occur - just deciphering what another developer has told me!

    Roll on Margaritaville
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i think we need more details about what you are doing.

    it seems a little unreasonable to expect the kind of exactly-right-0-latency-and-able-to-predict-the-future result you are looking for.

    how important is this number to the logic of the application?

    it really sounds like your users or business rule owners need the options explained to them and they need to decide what the "truth" is at any given moment.
    “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
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    After posting that's what I started to ask myself: how do you know when the things have completed when they're disconnected.

    The number is, of course, critical to certain aspects of the system.

    I think the only way to achieve what they want is to rewrite the updates and sproc in to a single transaction - making the final read with snapshot isolation.

    If that's the only way, then can you simply do it like this
    Code:
    BEGIN TRAN
        update 1
        update 2
        update 3
    COMMIT TRAN
    Or will the snapshot isolation itself have to be outside these transactions:
    Code:
    BEGIN TRAN
        BEGIN TRAN
            update 1
            update 2
            update 3
        COMMIT TRAN
        
        retrieve numbers with snapshot isolation
    COMIT TRAN
    Apologies, I have the dumb today.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well according to the forum, blindman was the last reply to this thread, but his post is nowhere to be seen

    Oh and, yes, this is a bump.
    George
    Home | Blog

Posting Permissions

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