0

I need to check if a unique int - otherID (not the primary key) exists in a table. If it does, return the primary key. If it doesn't, insert a record containing the otherID I checked, then return the new primary key.

ID unqiueidentifier, --PK
name varchar(100),
otherID int --Unique

I need to holdlock whatever statement I use because between checking and inserting a concurrent user could insert the same otherID.

I was looking at using a MERGE with holdlock - but it seems that can only be used for INSERT / UPDATE / DELETE - not for selecting.

I'm using this from Dapper in an ASP.net MVC 5 app.

I would like to do this in a single database roundtrip if possible.

Other than a MERGE I'm not even sure what to search for on Google - I don't know if this is possible?!

I want to avoid the chance of a race condition / unique key violation.

niico
  • 11,206
  • 23
  • 78
  • 161
  • Once a particular `otherID` value has been inserted, could anything cause it to be *deleted*? Because it seems like a conditional `INSERT` followed by a plain `SELECT` should do the business without requiring any explicit lock hints. – Damien_The_Unbeliever May 05 '17 at 13:18
  • We have to: Check if otherID exists, if not insert record with it in. The gap between checking & inserting allows another connection to also insert the same ID - so requires a lock no? It might already exist too so we can't just decide to try and insert it (which may violate the unique constraint) -we have to see if it exists. – niico May 05 '17 at 13:28
  • That's not what I asked. What I'm asking is, once a row with this `otherID` value does exist, could any *other* process cause it to *cease* to exist afterwards? Because if not you can write a simple `INSERT` that *conditionally* inserts the row and then (because you've told us that no other process exists to remove the row) just select the *now guaranteed to exist* row's data. – Damien_The_Unbeliever May 05 '17 at 13:30
  • Nothing can delete it. I'm still not sure why this wouldn't need a holdlock between seeing if it exists, and if it doesn't, in a separate statement inserting it though?! If between those tasks a concurrent process inserted it you'd get a unique constraint violation no? If we do use holdlock somehow, then in principle yes you could handle the insert if required, then just to a subsequent select. Are you talking "IF NOT EXISTS(SELECT otherID from x) THEN INSERT ...."? – niico May 05 '17 at 13:44

1 Answers1

1

We can do a conditional INSERT, followed by a plain SELECT:

DECLARE @OtherID int
INSERT INTO TableA (OtherID)
SELECT @OtherID
WHERE NOT EXIST (SELECT * from TableA where OtherID = @OtherID)

SELECT MainID from TableA where OtherID = @OtherID
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Ahh nice - I never saw a conditional insert before. So there is definitely no chance of a race condition here?! – niico May 05 '17 at 13:50
  • Apparently even this may be subject to concurrency issues - locks still required apparently: http://weblogs.sqlteam.com/dang/archive/2007/10/28/Conditional-INSERTUPDATE-Race-Condition.aspx – niico May 05 '17 at 13:52
  • The last item (4) in this post also discusses it - and the poster actually decides against this approach entirely: http://kejser.org/race-condition-when-creating-unique-values/ He suggests approach 3 - a transaction with XLOCK, ROWLOCK, HOLDLOCK?! Thoughts?! – niico May 05 '17 at 17:59