0

I wrote code to upsert records to the database. The database is Azure DB, S4 tier.

Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many updates.

The code works successfully most of the times, but sometimes it throws 2627 exception (that handled inside of the CATCH statement). Cannot figure out so far what could be the issue.

I can replace it with MERGE statement, but I tbh don't favor it much, and also want to figure out what's happening with this try and catch.

SET nocount ON;
IF NOT EXISTS(SELECT * FROM MyTable WHERE ID = @ID)
BEGIN TRY
INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END TRY
BEGIN CATCH
IF @@Error <> 2627 ----Violation of PRIMARY KEY constraint
THROW;
END CATCH

UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()

  • 1
    My theories are 1) add XACT_ABORT ON 2) Add ; before THROW I could add both and release to prod, but then I wont know what was the issue. And I can't release too often either. – Natalia Jul 10 at 23:17
  • 1
    Better pattern IMHO is update where key = @key / if @@rowcount = 0 insert. – Aaron Bertrand Jul 11 at 1:38
  • Is it possible that more than one error is being raised? Could the order of the errors be affecting the @@ERROR value? – George.Palacios Jul 11 at 10:33
  • According tho the syntax you have written, I think you want to ignore primary key violation for that particular script. Am I right? – Rajesh Ranjan Jul 11 at 10:38
  • Rajesh, yes. I expect multiple events at the same time for the same record coming, that's why I dont want blindly rely on IF NOT EXISTS condition. There are many ways to rewrite the code to get rid of the issue, but I am curious what exactly the issue is and why try and catch failing (sometimes), but often enough for us to see the exception in prod. – Natalia Jul 11 at 14:07
0

Not sure if this is the issue, but you should use ERROR_NUMBER() not @@error in your catch block.

In any case something like this should work reliably, without PK voliations:

SET nocount ON;
begin transaction
IF NOT EXISTS(SELECT * FROM MyTable with (updlock, holdlock) WHERE ID = @ID)
begin
  INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END 
else
begin
  UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()
end
commit transaction
  • Thank you for your answer. I tried to find a difference between those two and I couldn't. Why don't you like @Error? – Natalia Jul 10 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.