MS SQL日期时间精度问题
我遇到过这样的情况：两个人可能从两台不同的计算机处理同一个订单（存储在 MS SQL 数据库中）。为了防止在先保存订单副本，然后稍后第二个保存副本并覆盖第一个副本的情况下发生数据丢失，我添加了针对 lastSaved 的检查保存之前的字段（日期时间）。
代码大致如下所示：
private bool orderIsChangedByOtherUser(Order localOrderCopy)
{
// Look up fresh version of the order from the DB
Order databaseOrder = orderService.GetByOrderId(localOrderCopy.Id);
if (databaseOrder != null &&
databaseOrder.LastSaved > localOrderCopy.LastSaved)
{
return true;
}
else
{
return false;
}
}
这在大多数情况下都有效，但我发现了一个小错误。
如果orderIsChangedByOtherUser返回false，则本地副本的lastSaved将更新为当前时间，然后保存到数据库。本地副本和数据库中的 lastSaved 值现在应该相同。但是，如果再次运行 orderIsChangedByOtherUser，有时会返回 true，即使没有其他用户对数据库进行更改。
在 Visual Studio 中调试时，databaseOrder.LastSaved 和 localOrderCopy.LastSaved 似乎具有相同的值，但仔细观察时，它们有时会相差几毫秒。
我发现这篇文章，其中有关于毫秒精度的简短通知SQL 中的日期时间：
另一个问题是 SQL Server 存储 DATETIME 的精度为 3.33 毫秒（0. 00333 秒）。
对于这个问题，我能想到的解决方案是比较两个日期时间，如果它们的差异小于 10 毫秒，则认为它们相等。
那么我的问题是：有没有更好/更安全的方法来比较 MS SQL 中的两个日期时间值，看看它们是否完全相同？
I have a situation where two persons might work on the same order (stored in an MS SQL database) from two different computers. To prevent data loss in the case where one would save his copy of the order first, and then a little later the second would save his copy and overwrite the first, I've added a check against the lastSaved field (datetime) before saving.
The code looks roughly like this:
private bool orderIsChangedByOtherUser(Order localOrderCopy)
{
// Look up fresh version of the order from the DB
Order databaseOrder = orderService.GetByOrderId(localOrderCopy.Id);
if (databaseOrder != null &&
databaseOrder.LastSaved > localOrderCopy.LastSaved)
{
return true;
}
else
{
return false;
}
}
This works for most of the time, but I have found one small bug.
If orderIsChangedByOtherUser returns false, the local copy will have its lastSaved updated to the current time and then be persisted to the database. The value of lastSaved in the local copy and the DB should now be the same. However, if orderIsChangedByOtherUser is run again, it sometimes returns true even though no other user has made changes to the DB.
When debugging in Visual Studio, databaseOrder.LastSaved and localOrderCopy.LastSaved appear to have the same value, but when looking closer they some times differ by a few milliseconds.
I found this article with a short notice on the millisecond precision for datetime in SQL:
Another problem is that SQL Server
stores DATETIME with a precision of
3.33 milliseconds (0. 00333 seconds).
The solution I could think of for this problem, is to compare the two datetimes and consider them equal if they differ by less than say 10 milliseconds.
My question to you is then: are there any better/safer ways to compare two datetime values in MS SQL to see if they are exactly the same?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我知道你说过你不能改变类型，但如果这只是为了保持兼容性&如果您使用 2008，您可以将
lastSaved字段更改为DATETIME2（与DATETIME完全兼容）并使用SYSDATETIME() 两者都有更高的精度。I know you said you can't change the type, but if this is only to maintain compatibility & your using 2008 you could change the
lastSavedfield toDATETIME2(which is fully compatible withDATETIME) and useSYSDATETIME()both of which have much greater precision.您可以将整数修订字段添加到订单表中。每次用户保存订单时，您都会将修订版本增加一。然后可以轻松检查是否有人更改了订单，或者想要保存订单的用户是否使用了最新版本。
You could add an integer revision field to your order table. Every time a user saves the order you increase the revision by one. Then its easy to check if somebody has altered the order or if the user who wants to save the order are on the latest revision.
当您使用 SQL 2005 之前的版本时，准确性问题始终存在，永远不会比 1/300 秒或 3.33 毫秒更准确。
不管缺乏准确性，您都在编写一个有缺陷的竞争条件，其中两个用户仍然可以快速连续地写入数据库，但都被认为是成功的。只要检查和后续写入在同一 3-4 毫秒内发生，准确性的缺乏就会增加发生这种情况的可能性。
任何在写入后进行检查的尝试都会遇到此问题，您要么必须接受乐观锁定的后果，将锁定更改为悲观锁定，要么实施某种形式的信号量类型策略来正确处理锁定。
Whilst you are within SQL 2005 and before the accuracy issue will always be there, never more accurate than 1/300th of a second, or 3.33ms.
Regardless of the lack of accuracy you are programming a flawed race condition where both users can still write to the database in quick sucession but both be considered sucessful. The lack of accuracy increases the chances of it occuring, as long as the check and subsequent writes occured within the same 3-4 ms.
Any attempt to check followed by a write suffers this problem, and you either have to accept the consequences of optimistic locking, change the locking to pessemistic or implement some form of semaphore type strategy to handle the locking properly.
您可以使用时间戳字段而不是日期时间字段来检查上次编辑日期吗？ （在 SQL 2008 中，现在是 RowVersion）
You could use a timestamp field to check last edit date rather than a datetime field? (In SQL 2008 this is now RowVersion)
您必须确保您的时间精度一致 - 这主要是通过在 C# 端使用适当的逻辑来实际降低 DateTime 对象中本机的精度来实现的 - 基本上使您拥有例如始终以秒为单位的时间戳，不低于所有层。
如果你做得正确，所有层的时间戳将立即具有可比性。
YOu have to make sure your time precisions line up - this is mostly doable by having the proper logic on the C# side to actually reduce the precision under that which is native in the DateTime object - basically make suer you have for example timestamps always in Seconds, not lower, across all layers.
If you do that properly, the timestamp across all layers will be immediately comparable.
还有另一种方法可以做到这一点。
不要从本地计算机传入“上次保存的”，而是修改 UPDATE 存储过程。
指定 LastSaved = getdate()
然后返回 LastSaved 的值（以及任何其他结果，例如 ID），并使用该结果更新客户端上的 LastSaved 时间。
这样做有两个明显的优点。
一是保留您的日期时间准确性。
另一个是日期和时间现在与服务器一致，而不是遭受任何网络延迟和本地时钟漂移问题。
我们通常使用自动生成的 SP 进行 CRUD 操作，并且表通常运行“Created/LastUpdated”和“CreatedBy/LastUpdatedBy”对，其中日期在服务器上设置，并传入“By”值，如果 NULL 则为设置为系统用户
There is another way to do this potentially.
Instead of passing in the "Last Saved" from the local machine, modify the UPDATE stored procedure.
Assign LastSaved = getdate()
Then return the value of LastSaved (and any other results, such as IDs), and update the LastSaved time at the client with that result.
There are two obvious advantages to this.
One is that your DateTime accuracy is preserved.
The other is that the date and time are now consistent with the server, rather than suffering from any network latency and local clock drift issues.
We usually use auto generated SPs for CRUD operations, and the tables usually run "Created/LastUpdated" and "CreatedBy/LastUpdatedBy" pairs where the dates are set on the server, and the "By" values are passed in, and if NULL are set to System_User
检查 SQL 2014 的兼容性版本 - SQL 2016 中的更改将实体框架中的 DateTime 转换为 DateTime2(7)。
请参阅MSN。
尝试使用此 SQL 来测试 YourTable 和 YourDateTime 列：
Check the Compatibility Version to SQL 2014 - Change in SQL 2016 converts DateTime to DateTime2(7) in Entity Framework.
see MSN.
Try this SQL to Test on YourTable and YourDateTime column: