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.

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.

commented: To prevent lock or duplication. Generate primary key from one server odd nos. and another server even no. +1
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.