3

I am working with an N-tier application, and I'm updating the Data Access Layer (DAL) to use a new ORM. As I'm updating the DAL I'm finding some issues with how we save objects to the database.

One question I have is should the DAL have a simple SaveObject(objectToSave) method that checks for it's existence in the database and then either updates or saves the object? Or should the Business Logic Layer figure that out, and the DAL only have CreateObject(objectToCreate), UpdateObject(objectToUpdate), and ObjectExists(objectToLookFor) methods?

Improve this question
4
  • 1
    A sequence "check if exists, if not then insert else update" is not always going to work, for example in a concurrent environment where multiple threads or processes might be inserting entities with the same PK (you would need locking or synchronization between check and insert). It's also inefficient if you'd always first have to do a query before you do an insert. Commented Jun 29, 2017 at 14:16
  • So, that explains a problem with it, but what is the correct solution in this case? Commented Jun 29, 2017 at 15:24
  • As you can see from the answers below, there is not a single correct solution, there are different solutions which each have pros and cons, and what's most appropriate also depends on other requirements. Commented Jun 29, 2017 at 21:12
  • Why such a sour reaction? My comment was not meant in a condescending way. Commented Jul 13, 2017 at 20:05

2 Answers 2

Reset to default
1

That's a classic architectural decision. You'll want to look at the pros & cons of each approach, and make the decision that's best for your system's consumers.

The benefit of the Save/Upsert approach is that it simplifies the API. Many systems do this, including Salesforce.com.

But, if the consumer knows (to the extent possible) that a record is brand new, it is more efficient to do a Create/Insert without first applying a lock and checking for existence.

(Although your database might be super optimized for Save/Upsert - you'll want to consider that too.)

Lastly, you'll want to think about the concurrency behavior you're exposing to the consumer. For example, if the consumer believes it's adding a brand new record, but in reality another user has slipped in ahead and created the record, is it ok to quietly update the record? Or is it better to return a hard error ("record already exists")?

Improve this answer
1

Data constraints such as uniqueness can only be enforced by the database server itself. It alone implements the locks and other ACID properties required to address this issue, which incidentially is a very common concurrency control problem.

In order to make this work, you can either deal with it in stored procedures, or if you are lucky you can configure/program your Data Access Layer so that it will do it for you. If the DAL does it, it will still need to create a database transaction, but it can take care of the implementation details.

The business layer can't do it because it does not know how to construct the right kind of transaction to prevent concurrent users from interfering with your uniqueness rule. You can still do a preliminary check, but you also need to handle the sort of exception that could be raised if the object gets inserted by someone else in the narrow time between your check and insert.

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.