为什么我的SQL Server触发器从不同的事务中写入两条具有相同时间戳的记录? [英] Why does my SQL Server trigger write two records with the same time stamp from different transactions?

问题描述

我有一个带有整数 Status 列的SQL Server表.我的代码更新了记录的 Status (状态)，然后在几毫秒后再次将其更新为另一个值.

I have a SQL Server table with an integer Status column. My code updates the Status of a record and then, milliseconds later, updates it again to a different value.

此表具有一个触发器，每次 Status 值更改时，该触发器就会将记录插入到单独的 History 表中.

This table has a trigger which inserts a record into a separate History table every time the Status value changes.

History 表的以下各列:

1. Id uniqueidentifier :更新记录的PK值(注意:不是外键-我们不需要参照完整性)
2. Status int :已更新记录的新状态值
3. TimeUtc DateTime2(7):更新发生的时间

1. Id uniqueidentifier: the PK value of the updated record (note: NOT a foreign key - we don't need referential integrity)
2. Status int: the new status value of the updated record
3. TimeUtc DateTime2(7): the time the update occurred

一个NLog日志文件向我们显示，我们在关闭但时间不同的两个不同的数据库 UPDATE 中进行了调用.但是，当我们随后在数据库中查找时，两个历史记录的 TimeUtc 值与微秒相同:

An NLog log file shows us that we make the two separate database UPDATE calls at close, but different times. But when we look in the database afterwards, the TimeUtc value for the two history records are identical to the microsecond:

Id: CD83...  Status: 4  TimeUtc: 2020-3-20 16:14:26.6952631
Id: CD83...  Status: 5  TimeUtc: 2020-3-20 16:14:26.6952631

为什么会这样?为了使这种情况变得更加奇怪，有时，当我们运行并发代码时，几乎在同一时间将更新两个单独的记录，并且所有四个历史记录将具有相同的时间戳.

Why is this happening? To make this even more bizarre, sometimes, when we run concurrent code, two separate records will be updated at nearly the same time and all four history records will have the same timestamp.

Id: CD83...  Status: 4  TimeUtc: 2020-3-20 16:14:26.6952631
Id: CD83...  Status: 5  TimeUtc: 2020-3-20 16:14:26.6952631
Id: 06EA...  Status: 4  TimeUtc: 2020-3-20 16:14:26.6952631
Id: 06EA...  Status: 5  TimeUtc: 2020-3-20 16:14:26.6952631

我们正在使用ServiceStack OrmLite来更新值，文档告诉我，每个调用都是一个单独的事务，(我认为)这会产生两个单独的触发事件，但这也许是错误的.看起来SQL Server正在保存触发事件并立即触发所有事件.ServiceStack OrmLite可以在后台将调用批量处理为单个事务吗?

We are using ServiceStack OrmLite to update the values and the docs tell me that each call is a separate transaction which (I thought) would produce two separate trigger events, but perhaps that's wrong. It looks like SQL Server is saving up trigger events and firing them all at once. Could ServiceStack OrmLite be batching the calls under the hood into a single transaction?

这是触发器定义.让我知道是否需要更多详细信息:

Here is the trigger definition. Let me know if more details are needed:

CREATE TRIGGER [dbo].[ChangeStatus] 
ON [dbo].[TableWithStatus]
AFTER INSERT, UPDATE    
AS 
BEGIN
    SET NOCOUNT ON;

    -- On insert, or on update where status changed
    INSERT INTO History
        SELECT i.[Id], i.[Status], SYSUTCDATETIME()
        FROM Inserted i 
        LEFT JOIN Deleted d ON i.Id = d.Id
        WHERE i.[Status] IS NOT NULL 
          AND (d.[Status] IS NULL OR i.[Status] <> d.[Status])
END
GO

附录:我用第四个字段(varchar)更新了历史记录"表，并更新了触发器，以将@@ SPID和CURRENT_TRANSACTION_ID()写入由冒号分隔的该字段.这是我得到的:

ADDENDUM: I updated the History table with a fourth field (varchar) and update the Trigger to write @@SPID and CURRENT_TRANSACTION_ID() to this field separated by a colon. Here's what I got:

每次更新都是在同一会话和一个单独的事务中执行的，这两者都是我所期望的，但是仍然无法解释为什么记录共享相同的时间戳.

Every update was performed in the same session and a separate transaction, both of which are what I would have expected, but then it still does not explain why the records share the same time stamp.

推荐答案

感谢您粘性位和 lptr .似乎操作系统是罪魁祸首.

Thank you to sticky bit and lptr. It does appear that the operating system is the culprit.

根据

SQL Server通过使用GetSystemTimeAsFileTime()Windows API获取日期和时间值.准确性取决于运行SQL Server实例的Windows的计算机硬件和版本.该API的精度固定为100纳秒.可以使用Windows API GetSystemTimeAdjustment()确定准确性.

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

这篇冗长而复杂的文章在解释原因方面做得更好.我不能说我理解所有这些内容，但是最重要的是，SYSUTCDATETIME()使用了一个OS工具，该工具读取的时钟值更新得不够频繁，无法获得我们期望的精度.上面的文章是指一台典型的机器每秒更新其时钟64次，甚至没有毫秒精度.这篇文章确实指出，有一些方法可以获得更准确的结果，尤其是在Windows的较新版本中，但是我无法轻松地从SQL查询访问这些方法.

This long and complex article, however, does a better job at explaining why. I can't say I understand all of it, but the bottom line appears to be that SYSUTCDATETIME() uses an OS tool that reads a clock value which is not updated often enough to get the precision we were expecting. The article above refers to a typical machine updating its clock 64 times a second, which is not even millisecond accuracy. The article does indicate that there are ways to get more accurate results, especially with newer versions of Windows, but I cannot easily access those methods from a SQL query.

只要我们了解发生的原因，我们就可以接受结果.

As long as we understand why it is happening we can live with the results.

这篇关于为什么我的SQL Server触发器从不同的事务中写入两条具有相同时间戳的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！