Validation layer of clustered WebApi solution

Question

Currently I have a design problem, which I am not sure how exactly to solve and what would be the best approach.

So what I have is a ASP.NET Core WebApi project which is actually clustered solution. It has several layers, of course representation (RESTful API), services, validation, repositories and data. Solution is using SQL database.

My current problem is in validation layer. So I have an HttpPost action which receives parameter X that should be stored in a SQL database. This parameter should be unique. What I am doing in my validation layer is checking if it already exist in database, if exist => return proper Http response, if it doesn't then I'm updating the database.

The problem comes when API is deployed and actually it's clustered solution. So if we have 2 requests at same time we might have a case where both of requests are validated through validation layer (because still nothing is written to database) and then 1 of the requests will successfully write to database and update record data, but second request will receive an exception which will be in data entities.

What can be done in that case is the proper exception in data layer to be treated in a different way and of course return again proper response to the customer, but will only happen if we add extra constrains to our SQL database and say that X column should have unique value.

If we imaging that we don't have access to database and we cannot add this constraint (so X column can actually have same values), then we depend only on API to validate.

I am not sure what is the solution of this problem. Just once again, if I try to add same values which should be unique, but SQL doesn't validate them (maybe we don't have such permissions for access) and we only depend on our WebApi, which is async and validation layer might say "Ok" to both requests, because DB is still not updated, then what is the solution.

Thanks in advance

Answer 1

This is not a design problem; it is an implementation problem.

You simply need to use a sql transaction. Within that transaction, check for the existence of the record, if it does exist, write it, if not don't.

You will have to modify your repository methods to take a transaction token as a parameter.

var t = CreateTransaction();
if (!repository.Exist(parameter, t))
{
    repository.Create(parameter, t));
}
t.Commit();