Data synchronization between databases

I have a website, the main database is MYSQL, I add a search for entities that are often updated and new ones are added. Elastic as the second DB. Question: how to avoid data inconsistency? some values ​​in mysql, others in elastic. This happens because of the race, when two competing requests for updating 1 entity come, in mysql the request turned out to be the first, in elastic the second request became the first and the data broke. I can build request queues, but this will greatly affect performance, and then there will be a delay that is added to mysql, not yet in elastic, which is essentially also a data out of sync. Who decided how? Are there any thoughts?

From my 10000 feet view this issue is not an issue between mysql and elasticsearch, but more about finding the proper architecture regarding updates. My idea would be to treat one of the systems as the system of the ultimate truth and always update the other system based on that.

Having an application write into both stores without knowing if a write has been accepted by the other or with writes competing sounds like a way into chaos to me.

You could think of a mechanism based on hooks or with a queue, there is a fair share of solutions to this, but I think it requires some time on the drawing board how to properly integrate that with your existing architecture.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.