2

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

1
  • You could add a validation layer that you would pass your requests to, and which would forward your requests to the database after verifying that they are OK. But that would easily become a bottleneck.
    – Mael
    Commented Jan 31, 2018 at 12:40

1 Answer 1

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();
2
  • Hi there, I can agree with you, I think that's really more implementation issue, then design. The problem is that with this approach I am mixing services and validation layer, which I want to keep as more isolated as I can. Also "validation class" for customer for example might have multiple requests to database to validate different things, also then in services layer I might update also another databases before I proceed to updating X value. My point is that, with this sample method I am validating and updating as well, where I want to have them separated. Commented Jan 31, 2018 at 22:16
  • So between create and commit transaction might happen other database operations. Also I am working with generic Validator<T>. What is happening in code is something like validator.Validate<Customer>(customer); customerService.UpdateCustomer(customer); Commented Jan 31, 2018 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.