Would you rather use a version number (that will increment) or a timestamp to check for concurrency problems?
+1
A:
Version number. Or if I'm using timestamp, I'll make sure it is UTC - so there is no confusion with the timezone.
Rohith
2009-11-06 13:23:18
+3
A:
I'd use a version number, particularly if the resource may ever be updated more than the resolution of your timestamps (e.g. if you're storing timestamps to a resolution of seconds, if you have multiple updates within a single second, your versioning will break).
Dominic Rodger
2009-11-06 13:26:00
+3
A:
Version number is better because no matter what format of time you use, it can still be thrown off if the server's clock is wrong or becomes wrong over time.
I have to admit, I've used a time stamp in many databases because it can serve a dual purpose of concurrency checking as well as readability for when the data was last changed. However, using a version number is really the better way to go.
NYSystemsAnalyst
2009-11-06 13:26:11
Yes, idd, I already have a column ModifiedOn and was thinking of using that, but you would recommend an extra column?
Lieven Cardoen
2009-11-06 13:42:06
Yes. As much as I hate having a lot of columns in a table, it is the more bulletproof solution.
NYSystemsAnalyst
2009-11-06 20:44:53
Well, I'm a bit confused because of Jim's answer. If TimeStamp changes automatically when modifying the row, then that's quite usefull. What do you mean by 'it can still be thrown off'? thx
Lieven Cardoen
2009-11-07 12:45:39
The timestamp datatype Jim is referring to is somewhat of a misnomer. SQL Server has a datatype called timestamp. However, it has nothing to do with actual time -- it is really a self-incrementing version number. Each time a row is updated, the timestamp number is automatically changed. In SQL 2008, timestamp has been replaced by rowversion (http://msdn.microsoft.com/en-us/library/ms182776.aspx), which makes much more sense.
NYSystemsAnalyst
2009-11-09 14:08:16
I think you'll find I had mentioned that "Despite its description it doesn't really have anything to do the date and time"
Jim
2009-11-11 16:05:29
+1
A:
Lieven,
I know you didn't specify SQL Server, but IF you are talking about SQL Server then the datatype TimeStamp would be the best method to use. Despite its description it doesn't really have anything to do the date and time. It's actually just a binary number which changes every time the row has been modified. Thus, if any amendments are made to the row then you kno the timestamp column will change. This has the advantage over version numbers because you, the programmer, do not have to "maintain" the version number. Actual Date/Time timestamps need to be used more carefully as another poster referred to earlier - time differences etc.
Jim
2009-11-06 13:59:45
And to which type do I need to map timestamp in C#? Because before an update or delete I want to check for concurrency problems.(I am working with sql server indeed. Flex-WebORB-.NET-SQLServer). We're sending dto's to Flex. In that dto I'll need to put the timestamp. Whenever the dto returns to be saved, I'll need to chack if the timestamp in the dto corresponds to timestamp in database. Thx.
Lieven Cardoen
2009-11-06 14:30:09
Here's some chat about it:http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/0ad05e31-c3b2-44fb-99f5-32e80754e37a/However, have you considered allowing the server to handle versions? If you're using optimistic locking then it would be your sql insert that would detect a problem and throw the exception.
Jim
2009-11-06 14:40:15
I was going to do that, letting the sql insert detect the problem. But you do need to send the timestamp to the client or not? If you're inserting you need to compare the timestamp in db with the timestamp in the object. Or am I missing something here?
Lieven Cardoen
2009-11-06 14:48:10
I'm confused now because of NYSystemAnalyst's answer. Can something go wrong with this TimeStamp?
Lieven Cardoen
2009-11-07 12:46:46
It looks to me like he's talking about the actual date and time rather than the timestamp datatype. sounds like Timestamp data type is the way forward for you.
Jim
2009-11-09 13:32:18
A:
If you are on Windows, I recommend to use a globally unique identifier (GUID) as the version identifier.
A timestamp (even if UTC) can make problems if the clock is set by the user. An incrementing number (if hold in memory) can make problems if the server application is restarted, or if it overflows (if it's only a 16-bit or 32-bit integer).
Stefan Schultze
2009-11-06 14:03:50