0

I've been reading about possible solutions on upsert. In particular, this thread here, where the solution for Upsert went along the lines of:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) 
END
COMMIT TRANSACTION;

However, with ISOLATION LEVEL SERIALIZABLE, I'll be introducing a lock at the table level, correct? Which is not ideal, as there will potentially be a lot of operations over that table using the Upsert.

I think we introduce the ISOLATION LEVEL SERIALIZABLE in the first place to prevent the issue where two concurrent transactions would be trying to update the same record non yet existent record and one of them would then fail INSERTing it. However, I was thinking of doing the following to solve that problem without the lock:

BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  BEGIN TRY
    INSERT dbo.table(PK, ...) 
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() = 2627
    BEGIN
      UPDATE dbo.table SET ... WHERE PK = @PK;
    END
  END CATCH
END
COMMIT TRANSACTION;

In this case, if the INSERT would fail, it would mean that in the mean time some other concurrent transaction inserted a row. Then, we would do the Update again.

However, I'm concerned if ERROR Handling is actually more expensive than using SERIALIZABLE. What do you guys think?

Thanks!

Bruno Gonçalves
  • 1
  • 2
  • 2
    Instead of trying to reinvent the wheel, why don't you use a single MERGE instead of UODATE/INSERT? – dnoeth Aug 09 '20 at 09:24
  • @dnoeth . . . One of the answers in the referenced question is by Aaron Bertrand and he specifically warns against `merge`. As for your question, the error handling is probably much faster than `serializable`, because it would presumably happen very rarely, but `serializable` would affect every operation. – Gordon Linoff Aug 09 '20 at 11:39

0 Answers0