SQL Server Interview Question #298

How do you prevent duplicate records when two requests arrive at the same time?

ASP.NET Core, EF Core & SQL Server Real-World Scenarios Senior Advanced

Detailed Explanation

Do not rely only on an application-level 'check then insert' sequence because two concurrent requests can both pass the check before either inserts.

Enforce the business uniqueness rule in SQL Server with a UNIQUE constraint or unique index. The application can perform a friendly pre-check for user experience, but the database constraint is the final concurrency-safe enforcement point.

Catch the resulting database exception and convert it into the appropriate application response.

Code Example

CREATE UNIQUE INDEX UX_Users_Email
ON dbo.Users(Email);