How to handle concurrency using SELECT?

Question

I need to validate if a record already exists. The portion, in the stored procedure, that does that is:

SELECT EXISTS (SELECT 1 FROM MyTable 
               WHERE FirstName = @FirstName 
                 AND LastName = @LastName 
                 AND Address = @Address)
BEGIN
    SET @IsNewRecord = 1
END

IF @IsNewRecord = 1
BEGIN
    INSERT INTO MyTable
    VALUES (@FirtName, @LastName, @Addres, @City, @Phone)
END

That works fine, the issue is when the stored procedure is called by several clients at the same time, it will return the @IsNewRecord = 1.

I already tried surrounding the validation among a TRANSACTION BLOCK, but it still creates a new record.

How can I handle concurrency when using SELECT?

In addition to the transaction, add a locking hint to serialize access to the row: `SELECT 1 FROM MyTable WITH(UPDLOCK, HOLDLOCK)...`. It is important to have a composite index on FirstName and LastName to improve concurrency too. — Dan Guzman, Aug 20 '18 at 22:44
That is kind of special rule over that table, so for other circumstances is valid to have more than one row for those columns. — Jamo, Aug 20 '18 at 22:49
Possible duplicate of [What is the best practice for inserting a record if it doesn't already exist?](https://stackoverflow.com/questions/38497259/what-is-the-best-practice-for-inserting-a-record-if-it-doesnt-already-exist) — Zohar Peled, Aug 21 '18 at 07:20

score 0 · Answer 1 · answered Aug 21 '18 at 07:35

0

do like this

begin transaction
SELECT EXISTS (SELECT 1 FROM MyTable WHERE FirstName = @FirstName  AND LastName = @LastName 
                 AND Address = @Address)
BEGIN
   INSERT INTO MyTable
    VALUES (@FirtName, @LastName, @Addres, @City, @Phone)
END
commit

answered Aug 21 '18 at 07:35

Ravi

1,157
1
9
19

Thanks but as I mentioned above, that is what I already tried. – Jamo Aug 21 '18 at 15:45

How to handle concurrency using SELECT?

1 Answers1