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?
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.