Unable to reproduce primary key error on 'create if not exists sql'

Question

This API endpoint persists a user's "like" using a 'create if not exists' sql:

if not exists(
    select 1 from likes 
    where UserId = @UserId and EntityId = @EntityId and EntityType = @EntityType)

insert into likes (UserId, EntityType, EntityId) 
values (@UserId, @EntityType, @EntityId)

The Likes table has no Id col, but rather has a primary key defined by: (UserId, EntityType, EntityId).

It works as expected...I've tested this both through 1) querying the database directly and 2) through the deployed API and it works as expected. If the like already exists, the database stays exactly the same.

However, I recently encountered this error:

Violation of PRIMARY KEY constraint 'PK_Likes'. Cannot insert duplicate key in object 'dbo.Likes'. The duplicate key value is (19, 1, 113). The statement has been terminated.

I'll also mention that the server is running Azure Sql.

I can't reproduce this error. Please help me understand how this can happen.

Thank you.

Answer 1

This is a classic race condition, "check then do". Consider what happens if two database connections try to do the same thing at roughly the same time.

Connection A	Connection B
if not exists...
	if not exists...
true
	true
insert...
	insert...
success
	duplicate

To avoid this, "do then check". Do the insert and catch the duplicate key error.