How to handle in an effective way duplicate key insertions w/out throwing an exception

Question

My case scenario passes parameter to a procedure, that only does an insert. But two threads might try to pass the same value.

How to handle this situation w/out throwing an exception and with least amount of locks?

My performance requirement is at least 10k inserts per second.

EDIT: Column is unique. Timestamp might be altered (adjusted) before insertion.

@Matten I would rather correct the duplicate keys (possibly increment). What do you mean by failure? — Falcon, Sep 12 '11 at 10:02
@heximal My scenario is a duplicate key problem - so it is a unique column. — Falcon, Sep 12 '11 at 10:03
See the link [in this answer](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there/3408196#3408196) — Martin Smith, Sep 12 '11 at 10:05
When you want someone to be notified about your comment, address them with the `@` symbol before the nick, like *@Martin* (like you can see others do). Using that symbol has for some time been part of the notifying system here. — Andriy M, Sep 12 '11 at 14:15

score 5 · Accepted Answer · edited Sep 12 '11 at 14:18

5

Create index on table with ignore duplicate key option. It will not insert duplicate row and also won't through any error.

E.g.

create unique index i1 on #tmp(id) with ignore_dup_key

insert into #tmp values(1,"A")
2> go
(1 row affected)


1> insert into #tmp values(1,"A")
2> go
Duplicate key was ignored.
(0 rows affected)

edited Sep 12 '11 at 14:18

Andriy M

76,112
17
94
154

answered Sep 12 '11 at 11:05

Gopal Sanodiya

204
2
3

score 3 · Answer 2 · edited May 23 '17 at 12:12

As @Martin Smith has pointed out there is good solution - even though it is contrary to my question, it has good reasoning behind that seems well supported. The original answer you can find here posted by @gbn.

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH

Seriously, this is quickest and the most concurrent without locks, especially at high volumes. What if the UPDLOCK is escalated and the whole table is locked?

The original text by Paul Nielsen you can find here. - lesson 4.

If anyone has a similar problem to mine it is good to have a look at it.

Chris Bednarski · Answer 3 · 2011-09-12T10:11:30.503

2

Try the MERGE statement

From MSDN . A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement.

Regarding your performance concerns, there is also a page on Optimizing MERGE Statement Performance

edited Sep 12 '11 at 10:11

answered Sep 12 '11 at 10:05

Chris Bednarski

3,364
25
33

In case of two same values I want one of them to be altered (or whatever, but not overridden) - does Merge statement do that? – Falcon Sep 12 '11 at 10:52
@Falcon: Yes, you can pretty much do arbitrary things once you find if a record exists or not. – Chris Bednarski Sep 12 '11 at 22:47

How to handle in an effective way duplicate key insertions w/out throwing an exception

3 Answers3