I am working on an application (Java + MySQL) which will be hosted(application + db) in different servers across the glob. The same application and db will be hosted in Asia, Europe, Australia and North America.
For faster access, the application and db will be hosted in different servers and when the user tries to access the application, the server closer to user location will serve.
But the data in all the locations will be same. That means when the data will be updated in server in Europe, it will also sync / update database in Asia, Australia and North America.
I have never came across this type of scenarios before and don't have much knowledge on MySQL. But from research I came to know, we can use MySQL clustering or Replication for this scenario.
When the database will be updated, definitely it will take some time to update other databases.
My issue is, when the user in Asia tries to update a record through the application, the record will be locked by setting a flag in the field("locked" 0/1) for the record. When another user tries to edit the record, it will check the 'locked' field value and then allow the user to update or tells that the record is locked.
When the record is open for edit by a user in Asia, the locked field will be updated to 1. But it will take some time may be 2/3 seconds to replicate the locked field value in other servers in Europe, Australia etc. If any user from Europe in the mean time opens the record to edit from application, at that time the locked field value may not be replicated in Europe server. So the user from Europe can also open the record for edit. So there will be conflict as user from Asia and user from Europe will have the record edit interface.
Can anyone help me on this. I need suggestions of a better MySQL global db architecture for this scenario.
Thanks
