MS SQL临时表更新失败 [英] MS SQL temporal table update failure
问题描述
我找不到任何解释可以解释为什么在调用执行插入或更新的SP时,取决于在临时表上是否已经存在记录的原因
I cant find anything to explain why when calling a SP that does a insert or update depending on if record already exists on a temporal table i get
系统版本表上的数据修改失败'MYDB.dbo.TemporalExample',因为交易时间早于受影响的记录的期限开始时间.
Data modification failed on system-versioned table 'MYDB.dbo.TemporalExample' because transaction time was earlier than period start time for affected records.
什么意思?它似乎只是在某些时间发生,我想知道是否是因为我运行多线程代码和azure sql只是不喜欢在临时表上相互连接到同一张表?我正在使用实体框架(最新版本),但我怀疑这是问题所在
what dose that mean ? It only seems to happen some of the time , i wonder if its because im running multi threaded code and azure sql just doesn't like mutual connections to the same table when its a temporal one ? Im going though entity framework (latest version) but i doubt that's the issue
我的宝贝就是这个
create PROCEDURE mysp
@ID bigint,
@a FLOAT,
@b NVARCHAR(10),
@c DECIMAL(19, 4)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
BEGIN TRY
IF EXISTS ( SELECT TOP 1
Id
FROM
my_Temporal_Table WITH (NOLOCK)
WHERE
id = @ID
AND a = @a
AND b = @b)
BEGIN
UPDATE
my_Temporal_Table
SET
Id = @ID,
a = @a,
b = @b
c = @c
DateModified = GETUTCDATE()
WHERE
Id = @Id
END
ELSE
BEGIN
INSERT INTO
my_Temporal_Table
(Id, a, b, c, DateModified)
VALUES
(@ID, @a, @b, @c , GETUTCDATE())
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
END CATCH
更新我的临时表创建脚本:
Update my temporal table creation script :
CREATE TABLE [Temporal](
[TemporalId] [bigint] IDENTITY(1,1) NOT NULL,
[Payment] [decimal](19, 4) NOT NULL,
[DateModified] [datetime2](7) NOT NULL,
[SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [TemporalId] PRIMARY KEY CLUSTERED ([TemporalId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)WITH(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Car2].[TemporalHistory] )
)
有人可以解释为什么我可能会看到此问题,这意味着什么,更重要的是我如何解决它?
can someone explain why i might see this issue, what it means and more importantly how i can fix it ?
谢谢
推荐答案
所以我解决了……似乎临时表在线程逻辑中不能很好地发挥作用.我怀疑这是因为im并行对表进行了多个并发更新;链接的历史记录表的更新滞后,以至于时间链接会导致失败.使我的代码成为单线程解决了该问题.时态表受制于几乎是一种竞赛条件,这似乎很奇怪?我知道它不是我的代码,因为同一代码对其他表也能正常工作.因此,我想我必须坚持使用单线程逻辑,直到MS修复它
So i worked it out ...it seems that temporal tables don't play well with threading logic. I suspect that because im doing multiple concurrent updates to the table in parallel; the history table that's linked is lagging in its update enough that the time linkage causes a failure. Making my code single threaded fixed the issue. It seems odd that temporal table would be subject to what seems to be almost a race condition ? I know its not my code as the same code works fine for other tables. So I guess I will have to stick to single threaded logic until MS fix it
这篇关于MS SQL临时表更新失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!