Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have an app with the following three tables

  1. Email (emailNumber, Address)
  2. Recipients (reportNumber, emailNumber, lastChangeTime, status)
  3. Report (reportNumber, reportName)

I have a C# application that uses inline queries for data selection.

I have a select query that selects all reports and their Recipients. Recipients are selected as comma separacted string.

During updating, I need to check concurrency. Currently I am using MAX(lastChangeTime) for each reportNumber. This is selected as maxTime. Before update, it checks that the

lastChangeTime <= maxTime. --//It works fine

One of my co-developers asked why not use GETDATE() as “maxTime” rather than using a MAX operation. That is also working. Here what we are checking is the records are not updated after the record selection time.

Is there any pitfalls in using GETDATE() for this purpose?

share|improve this question
    
Wouldn't it be better to use transactions? –  svick Dec 2 '12 at 10:27
    
@svick. It's not about transaction - it's about concurrency –  Lijo Dec 2 '12 at 10:29
    
If the reports are generated (updated) periodically, can we put version number on the reports? Each time a report gets updated its version number will increment by one. The Report table will become (reportNumber, reportName, reportVersion) and Recipients become (reportNumber, emailNumber, lastReportVersionRequested = reportVersion) This might be marginally better than comparing by datetime (because it has fewer digits to compare with, I guess). –  rwong Dec 2 '12 at 10:38
    
Why do you need to check for concurrency? –  miraculixx Dec 2 '12 at 13:07
    
@miraculixx While updating the report-email mapping, I need to ensure that nobody has updated the record (after my selection) –  Lijo Dec 2 '12 at 13:52
add comment

1 Answer

If you get the data at the start of your process, it is a good solution. This is especially the case if you have multiple tables involved as you only need to track one timestamp. If you are tracking multiple timestamps, it is possible to use the wrong timestamp in a comparison.

The one shortfall, which applies in both cases, is if records are inserted at the timestamp but not included in the result set. (It is possible that the MAX value will return the same value as GetDate.) Long running insert transaction can also be a problem if the timestamp, and time of commit vary significantly.

The other issue is clock synchronization between the database server (which should be setting the timestamp in a trigger) and the host on which you issue GetDate. Use of NTP to synchronize clocks is advisable. I have seen cases where new users could not log-in for a couple of minutes due to unsynchronized clocks. It may be safer to get the date from the database in a single call.

share|improve this answer
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.