0

Dear Everyone,

I have a central Database which I want to spread across our regional offices for faster access and performance.

Requirements:

  1. A transaction that is effected in a regional office must be syncronized to the central database and vice versa.

  2. The databases have primary keys auto-generated. There should be no conflict of such keys or unique constraints.

I researched on the internet and found that this can be done using master to master replication.Is this method 100% sure of fixing key constraint conflicts ?

Or is there a better method ? I am using Mysql 5.4

Regards.

Edited by mwenyenia07

2
Contributors
1
Reply
17
Views
3 Years
Discussion Span
Last Post by Taywin
1

If both sides could be affected (modified), then you can't have either side to be a slave in replication. Master to Master is the only way. The unique key is a race condition that can't be guarantee regardless. The reason is that you have 2 separated database that require synchronization. If both sides attempt to insert a new record at the same time, the time both attempt to acquire a lock may allow the duplication. However, it is very rare case. If you want guarantee, you need to implement the synchronization yourself and give a weight on only on server (i.e. central data has priority to acquire the lock), but could theoretically lead to starvation.

Votes + Comments
To prevent lock or duplication. Generate primary key from one server odd nos. and another server even no.
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.