2

I have a requirement to insert a row into a table if a row does not already exist, and I need to do this using a stored procedure. I therefore need to select to check if the row already exists, followed by an insert. What I want to avoid is a race condition where 2 callers of the stored procedure may see that the row doesn't exist and then both try to insert the row.

My first thoughts on solving this would be to lock the table, but unfortunately you cannot issue LOCK TABLE in a stored procedure.

I want to avoid trapping a duplicate key insertion exception at the client (C#).

Any way of locking the table or alternative solutions?

Tim Lloyd
  • 37,954
  • 10
  • 100
  • 130
  • 1
    what about insert into ... on duplicate key ... – Haim Evgi Sep 06 '11 at 09:11
  • @Haim Works great. I set a field to the same value in "on duplicate key update" and this prevents the duplicate key error and also does not update my ts field. Please add as an answer. – Tim Lloyd Sep 06 '11 at 09:15

2 Answers2

2

you can use

insert into ... on duplicate key

look on example @thummper answer:

On duplicate key ignore?

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • +1 Works great. I set a field to the same value in "on duplicate key update" and this prevents the duplicate key error and also does not update my ts field. – Tim Lloyd Sep 06 '11 at 09:45
0

Another option is to use 

INSERT IGNORE INTO ...

and as long as you have a unique key that would be violated by this insert happening twice, it won't be done a second time.

Scott C Wilson
  • 19,102
  • 10
  • 61
  • 83