使用 BizTalk 2013r2 通过 WCF-SQL 存储过程进行 UPSERT

发布于 2025-01-09 21:11:12 字数 1007 浏览 2 评论 0原文

我目前正在尝试将规范模式写入 SQL 数据库中的多个相关表，但在评估记录在更新/插入之前是否存在时，我遇到了重复键 ID 冲突。

BizTalk 每 5 分钟从学生管理系统接收更改记录，将它们映射到存储过程，然后调用该过程，将更改写入主数据库中的 5 个表。

我相信这是因为我在存储过程中使用了不正确的设计模式。

当前设计：

IF EXISTS (Select student_id FROM student_modules WHERE student_id @student_id and module_id = @module_id)

-- THEN UPDATE THE RECORD

ELSE

-- INSERT THE RECORD

从逻辑上讲，这是有道理的，但由于 BizTalk 同时接收具有完全相同的学生和模块 ID 的 2 个更改记录，然后尝试为每个记录调用存储过程。 SQL 然后会出现恐慌，因为当它评估第一条消息中的逻辑时，它会尝试执行 INSERT，同时评估第二条消息中的相同逻辑 - 并告诉我我正在尝试插入重复键。我尝试使用在下面的链接（下面的设计）中找到的 UPSERT 模式，但这似乎完全锁定了 Student_modules 表。

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;

https://sqlperformance.com/2020/09/locking/upsert-anti- ？

我缺少一种更清晰的方法吗

原文

I'm currently trying to write a canonical schema to multiple related tables within a SQL DB, but I'm experience DUPLICATE KEY ID conflicts when it's evaluating whether the record exists prior to UPDATING/INSERTING.

BizTalk receives change records from the student management system every 5 minutes, maps them to a stored procedure and then calls that procedure which writes the changes to 5 tables in our master database.

I believe this is because I'm using an incorrect design pattern in the stored procedure.

Current Design:

IF EXISTS (Select student_id FROM student_modules WHERE student_id @student_id and module_id = @module_id)

-- THEN UPDATE THE RECORD

ELSE

-- INSERT THE RECORD

Logically this makes sense, but as BizTalk receives 2 change records with the exact same student and module ID at the same time, and then attempts to call the stored procedure for each record.
SQL then panics, because whilst it's evaluating the logic in the first message, it tries to execute the INSERT whilst evaluating the same logic in the second message - and tells me I'm trying to insert a DUPLICATE KEY.
I've tried using an UPSERT pattern that i found at the below link (design below), but that seems to lock the student_modules table completely.

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;

https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

Is there a cleaner approach to this that I'm missing?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

习ぎ惯性依靠 2025-01-16 21:11:12

您可以使用 MERGE Transact-SQL 命令

INSERT tbl_A (col, col2)  
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

您还需要考虑更改您的业务流程，以便它订阅同一学生 ID 的进一步更新（单例类型模式），或者将您的发送端口设置为有序传送，以防止尝试同时更新同一条记录。

You could use the MERGE Transact-SQL command

INSERT tbl_A (col, col2)  
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

You will also want to consider either changing your Orchestration so that it subscribes to further updates for the same student ID (a singleton type pattern) or to set your send port to ordered delivery, to prevent trying to update the same record at the same time.

回复收藏 0 原文

~没有更多了~