Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a BIG problem here. I'm creating a program that can be looping if the connection to the server is broken. But when the program is running for a few months, I found a bug on SQL server 2000.

I found some data that had more than one transaction with the same date and time.

Is it possible that sql2k server has a transaction with the same date and time in one table?

PS : I use getdate to fill that field.

Hope there's explanation and solution for my problem.

share|improve this question
    
Is your application multi-threaded? How are the date/time values that are stored in the table generated? –  Aaron Bertrand Jan 22 '13 at 14:55
add comment

migrated from stackoverflow.com Jan 22 '13 at 12:32

This question came from our site for professional and enthusiast programmers.

2 Answers

How long does it take your code to perform a transaction? Assume that the resolution of GETDATE is one millisecond. If a transaction takes less than a millisecond, then you should expect to have more than one transaction marked with the same time. If you cause take more than a millisecond most of time, then you are playing the odds that you won't get two sub- millisecond transactions back to back. The longer your code runs (months), the more likely that you will get two sub- millisecond transactions in a row.

BUT, it is worse than that. The resolution of GETDATE is not one millisecond. It is three milliseconds, IIRC. This makes it more likely that you will get transactions with the same time.

Relying on Date time values for keys is unreliable. It will often break when moving to faster hardware or after improving algorithm ice efficiency in some way, like adding an index. If you are using that value as part of a (natural) key, you should look into using a surrogate key.

share|improve this answer
    
It is 3ish milliseconds. The output from GETDATE() will end in 0, 3 or 7. –  mrdenny Jan 22 '13 at 22:17
    
well, i thought sql can't have data with the same datetime.. so i use the datetime values for key,, @_@.. and thx for the information.. :) –  XMozart Jan 23 '13 at 4:03
add comment

It won't be a bug: it's a flaw in your logic.

If you're looping, then it's possible that you are opening transactions on top of another transactions. That is, @@TRANCOUNT keeps incrementing. You may be incorrectly looping on say, a client timeout, that doesn't actually disconnect.

In your loop, in client code, you should test for an open transaction and rollback. Or use SET XACT_ABORT ON to force full rollbacks. See Do I really need to use "SET XACT_ABORT ON"? for more

It's hard to be more precise at this stage.

share|improve this answer
    
wow, thx for you answer.. seems this is my solution.. but i'll try it later.. thx bro.. :) –  XMozart Jan 23 '13 at 4:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.