Assume I have a dumb repository which stores numbers (for sake of this example). It is dumb because it may only create new record, update specified record and list all the existing records - no other logic included.

But I want to implement a business logic which will allow me to store new records safely, knowing that I will not store a duplicate record. Also I want to update existing records knowing that it will not lead to having duplicates.

Let's start with a creation of new records. The naive approach tells me that I need two steps to add a record:

  • (A) read existing records from repository
  • (B) check if existing records already contain same record as I need to store
  • (C) if existing records are free of duplicates - store record.

Works fine as long as everything runs in a single thread. How about multi-threading?

Another naive approach tells me, that I can introduce the lock around steps A,B,C. I am on a safe side again (let's ignore all the performance considerations).

Now let's consider records update. Steps are similar to creation:

  • (A) read all records
  • (B) check if duplicates will occur after update
  • (C) if check is in our favor - update the record.

Problems with multi-threading again? Just use lock again.

But what about the situation when one thread creates a record 200 while another thread updates record 100 to a value of 200. They both pass their checks on steps (B), both perform steps (C)... ant we end up with a duplicate.

It leads us to a conclusion that the whole design is wrong.

But what is the right design?

  • If trying to update a record runs into a duplicate, do you want to roll back the update, or just delete the old entry? If 'delete", the update can be written as delete then create, so you only have one step, create, to check. For the record, I think your requirements are messy and poor. – user949300 Dec 15 '18 at 0:53
  • Would it be ok to place these operations on a queue, so that only a single thread is creating the records? – user949300 Dec 15 '18 at 1:02

If I understand your question correctly, you're asking how to handle the multithreaded issue with: 1. Thread A checks for value X 2. Thread B checks for value X 3. Thread A inserts/updates value X 4. Thread B inserts/updates value X

If that is in fact what you're worried about, then generally I wouldn't, as it's just not very likely to happen for most apps.

Now if you are in fact working on an app that warrants this type of check, it should be happening on the storage level (ie. the database). This can be done in many ways but generally a unique key will do the trick.

Like you said, your repositories are (and should be) dumb. Their job is to interface a business layer (ie. Services) with the data storage system. It's job is not to protect from multithreading issues when adding unique values.

As long as uniqueness is a business requirement, then I would suggest having the logic that checks for uniqueness in your business layer, which will allow for more eloquent errors when this is being violated. But the real protection from the issue should sit at the source of storing the data.

| improve this answer | |

The solution I have found that works is to use the DBMS as a full data management system rather than just as a persistence layer. As studies have shown - such as Feral Concurrency Control concentrating on Ruby on Rails - implementing data integrity in the application/ORM means they are subject to race conditions, as you have found.

Instead any business rule that can be implemented as a data integrity constraint within the database should be. Ideally these should be implemented declaratively using the check, unique and foreign key constraints available in most SQL DBMS. The implementation of these in the DBMS engine should include any concurrency control mechanisms required.

Unfortunately most, if not all, SQL DBMS do not support the generic SQL assertion statement which would allow any data integrity constraint of arbitrary complexity to be declared. Therefore, I have implemented these programatically using triggers and included my own concurrency control using advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

By also using optimistic or pessimistic locking techniques, I am sure I have properly implemented a concurrency control mechanism that guarantees the integrity of the data without any race conditions.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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