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.http://www.dbforums.com/showthread.php?t=1196943|||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.|||a bug in your code.|||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.|||have you ran a trace yet and stepped through it yet?|||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.|||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.|||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.|||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. ;)