I have a query on bi-directional data sync. The scenario is, that we have ERP software running on a local network which is developed in PowerBuilder and the database is SQL Anywhere 16, Also, we have our cloud software which is developed in .net6 and the database is Azure SQL. And also we have a Middleware developed on .net which interacts with our API and local DB. After an operation like Invoice generation, we need to keep the quantity of a product accurate the same as local DB and cloud DB. Whether the operation happened in the cloud or local network. Please share your thoughts.
The approach would depend on whether you are willing to sacrifice consistency or concurrency.
If you are willing to sacrifice consistency, which in your case would be acceptable I believe for some use cases like syncing local invoices to the cloud, your middleware could asynchronously ensure that both the databases are in sync on the side.
If you are willing to sacrifice concurrency, which would be needed to ensure quantity of a product is accurate before checking out, you would essentially use a lock to ensure the product is available and is not being checked out by someone else. This has the down side of slowing down the system since multiple requests would be waiting as previous requests are being processed.
As for the actual implementation itself, you could use a queue for each transaction which the middleware could receive and sync for the first option. And for the second option, you would need to use some kind of distributed lock for each product that your API/Middleware would need to acquire before committing changes.
Depending on the scale and business KPIs of your application, you would have to decide how to approach the second option. Here is another SO thread which has a decent discussion on various approaches based on what you are willing to sacrifice.