3

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.

Improve this question
1
  • Is your application multi-threaded? How are the date/time values that are stored in the table generated?
    – Aaron Bertrand
    Commented Jan 22, 2013 at 14:55

2 Answers 2

Reset to default
3

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 https://stackoverflow.com/questions/917773/do-i-really-need-to-use-set-xact-abort-on/919279#919279 for more

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

Improve this answer
1
  • wow, thx for you answer.. seems this is my solution.. but i'll try it later.. thx bro.. :)
    – XMozart
    Commented Jan 23, 2013 at 4:00
2

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.

Improve this answer
2
  • It is 3ish milliseconds. The output from GETDATE() will end in 0, 3 or 7.
    – mrdenny
    Commented Jan 22, 2013 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
    Commented Jan 23, 2013 at 4:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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