Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: Missing records after COMMIT TRAN

    Hi

    Can you think about any reason for why when using a transaction after the COMMIT TRAN the inserted new record is not in the table and there is a gap in the identity????

    I'm using SQL 2000 SP3, there are no triggers are on the table and it happanes only under heavy load.

    Thanks,
    Inon.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “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
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    The process or the environment are irrelevant, although the question is general the problem is very specific, people may have encountered the same symptom with a totally different situation.
    I want to hear all the reasons that you can think of to when a transaction is committed and the record that was inserted in the transaction is missing.
    One example is when a trigger is responsible to delete the record or prevent it from being insert like an INSTEAD OF trigger, but there are no triggers on the table, so what else can cause it??

    Thanks,
    Inon.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    a bug in your code.
    “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.

  5. #5
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Thrasymachus
    a bug in your code.
    ok... thanks!! what else?

    (First of all you can see that I wrote "UNDER HEAVY LOAD", meaning it's working fine most of the time and only under heavy load (of the same process) SOME of the records are missing, second, A bug would cause a rollback, and I will also add that @@IDENTITY returns the correct new identity of the record which is SOMETIMES missing and cause the gap in the table)

    Inon.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    have you ran a trace yet and stepped through it yet?
    “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.

  7. #7
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Thrasymachus
    have you ran a trace yet and stepped through it yet?
    No, the thing is, it's not my code, I'm trying to help someone, he already tried some debug prints and debug queries (inserting some information in the middle of the code), it seems that it's going the way it should, remember that it's working most of the time, what makes me believe it's not the code, but I could be wrong, I know that when it's a question of the computer or the human, the human is usually the blame.

    I don't see how can a trace assist? Please advise.

    Addition, the server is rather slow and with only 512 MB of RAM, the HD has about 3 GB of free space, but anyway I would expect to get some error or some message… anything, but no, all is going well yet records are missing and a gap is formed.

    Thanks,
    Inon.
    Last edited by Inoni; 03-01-06 at 11:01.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    a properly setup profiler trace can you show you many things. sometimes things do not quite fire off like you expect them to. For this I would probably use the default columns and and use only the T-SQL and Stored procedure event classes and and all of the events that end with :completed.

    It takes a little reading and some practice to interpert what you are looking at but Profiler helps me diagnose stuff everyday.
    “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.

  9. #9
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    You’re right, I do use trace sometimes, but I hoped that maybe someone who has already experienced this problem could pinpoint the cause and save me some time.

    Addition, BTW I have a solution for this, I just do things a bit differently, but its still curios me, strange...

    Thanks,
    Inon.

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    OK ... here's a few.

    1. The row was deleted by a subsequent transaction

    2. Even though the commit was issued, the transaction was rolled back (more specifically where a two-phase commit is required.)

    3. The commit was issued, but the transaction timed out before completion (web based).

    4. It fell off into the bit bucket.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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