0

I'm working within a microservice environment and have two independent Azure serverless functions that both perform a SQL INSERT if a prior SELECT returns no row. Each serverless function runs on an as-needed basis based on various triggers. At times, the functions can be running at the exact same time which appears to be exposing a race condition. The race condition being that both functions will first check if a row exists in a table and if it does not exist it will create it, but if both functions do this at the same time then the both see that no record exists and one creates it while the otherwise fails due to a unique key constraint in the database table.

What approaches are there for me to remove this race condition?

Is there a solution that I can perform purely in SQL (a query, locking, stored procedure, etc) that would allow me to leverage that solution in my code without having to go with a larger code modification to fix the issue?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ryan
  • 7,733
  • 10
  • 61
  • 106
  • I think [this answer](https://stackoverflow.com/a/3407890/1127428) is what you are looking for. – Dale K Jul 13 '23 at 01:22

2 Answers2

1

Is there a solution that I can perform purely in SQL

Yes. In a transaction, check for the row with the (UPDLOCK,HOLDLOCK) query hints.

eg

begin transaction

if not exists (
  select *
  from sometable with (updlock,holdlock)
  where Name = @SomeName
)
begin
  insert into sometable(...)   
end

commit transaction

This will lock the target row (or empty range) so if two sessions run this at the same time, one will be blocked on SELECT until the other commits the insert.

Dale K
  • 25,246
  • 15
  • 42
  • 71
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
0

Assuming what you mean is that you want to actually see the result of that row:

You can actually do this in a single statement using MERGE.

DECLARE @found bit;

MERGE sometable WITH (UPDLOCK, HOLDLOCK) AS t
USING (VALUES (@Id, @SomeValue) ) AS v(Id, SomeValue)
  ON t.Id = v.Id
WHEN NOT MATCHED THEN
  INSERT (Id, SomeValue)
  VALUES (v.Id, v.SomeValue)
WHEN MATCHED THEN UPDATE
  SET @found = 1
OUTPUT inserted.*;

But given that MERGE is known to have bugs, I would still advise a separate IF NOT EXISTS ... INSERT and SELECT pairs.

Charlieface
  • 52,284
  • 6
  • 19
  • 43