Using TRY / CATCH to perform INSERT / UPDATE

Question

I have this pattern in a number of stored procedures

-- Table1
[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL
[count] INT NULL

-- 'data' is unique, with a unique index on 'data' in 'Table1'
BEGIN TRY 
    INSERT INTO Table1 (data, count) SELECT @data,1;
END TRY
BEGIN CATCH
    UPDATE Table1 SET count = count + 1 WHERE data = @data;
END CATCH

I've been slammed before for using this pattern

You should never have exception "catching" in your normal logic flow. (Thus why it is called an "exception"..it should be exceptional (rare). Put a exists check around your INSERT. "if not exists (select null from Data where data = @data) begin /* insert here */ END

However, I can't see a way around it in this instance. Consider the following alternative approaches.

INSERT INTO Table1 (data,count) 
SELECT @data,1 WHERE NOT EXISTS 
    (SELECT 1 FROM Table1 WHERE data = @data)

If I do this, it means every insert is unique, but I can't 'catch' an update condition.

DECLARE @id INT;  
SET @id = (SELECT id FROM Table1 WHERE data = @data)

IF(@id IS NULL)
    INSERT INTO Table1 (data, count) SELECT @data,1;
ELSE 
    UPDATE Table1 SET count = count + 1 WHERE data = @data;

If I do this, I have a race condition between the check and the insert, so I could have duplicates inserted.

BEGIN TRANSACTION
   DECLARE @id INT;  
   SET @id = (SELECT id FROM Table1 WHERE data = @data)

   IF(@id IS NULL)
       INSERT INTO Table1 (data, count) SELECT @data,1;
   ELSE 
       UPDATE Table1 SET count = count + 1 WHERE data = @data;
END TRANSACTION

If I wrap this in a TRANSACTION it adds more overhead. I know TRY/CATCH also brings overhead but I think TRANSACTION adds more - anyone know?.

People keep telling me that using TRY/CATCH in normal app logic is BAD, but won't tell me why

Note: I'm running SQL Server 2005 on at least one box, so I can't use MERGE

You just killed the first answer that came to my mind i.e. Merge. :) — Abhay Chauhan, Aug 04 '15 at 11:17

Backs · Accepted Answer · 2015-08-04T15:15:40.203

1

Try to update and if it's failed - to insert new.

BEGIN TRANSACTION
    UPDATE t
    SET
            t.count = t.count + 1
    FROM Table1 t
    WHERE t.data = @data

    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Table1
        (data, count)
        VALUES
        (@data, 1)
    END
COMMIT TRANSACTION

edited Aug 04 '15 at 15:15

answered Aug 04 '15 at 11:14

Backs

24,430
5
58
85

As per OP: Note: I'm running SQL Server 2005 on at least one box, so I can't use `MERGE`. – Felix Pamittan Aug 04 '15 at 11:16
@FelixPamittan terrible :( you can make a workaround like this http://stackoverflow.com/questions/12621241/can-i-use-the-merge-statement-in-sql-server-2005 – Backs Aug 04 '15 at 11:39
`MERGE` also has a bunch of unresolved issues, so I'm hesitant to use it. read more here: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – roryok Aug 04 '15 at 11:41
@roryok I edited my answer, I hope it'll help you. It seems like an answer of Dan, but I think, exists cheking is not necessary – Backs Aug 04 '15 at 15:18
@Backs is a transaction faster than a try/catch? – roryok Aug 04 '15 at 15:30
@roryok of cause, generating exception is expensive operation. try to execute both varinats 50000 times. my variant takes about 2 second, try-catch variant takes abount 9 seconds – Backs Aug 04 '15 at 15:49
@roryok you are wellcome! – Backs Aug 05 '15 at 11:16

score 0 · Answer 2 · answered Aug 04 '15 at 12:10

The explicit transaction is the cost of doing business with a conditional INSERT/UPDATE in order to address concurrency. The example below uses locking hints to a avoid race condition with this code.

BEGIN TRANSACTION;

INSERT  INTO Table1
        ( data
        , count
        )
        SELECT  @data
              , 1
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   Table1 WITH ( UPDLOCK, HOLDLOCK )
                             WHERE  data = @data );

IF @@ROWCOUNT = 0
    UPDATE  Table1
    SET     count = count + 1
    WHERE   data = @data;

COMMIT;

If the more common path is the UPDATE, try that first followed by the conditional INSERT.

does this perform better than the `TRY / CATCH` approach? – roryok Aug 04 '15 at 14:25 — roryok, Aug 04 '15 at 14:25

Using TRY / CATCH to perform INSERT / UPDATE

2 Answers2

Linked