I have an app with the following three tables
- Email (emailNumber, Address)
- Recipients (reportNumber, emailNumber, lastChangeTime, status)
- 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?
5
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.