Conflict resolution for two-way sync offline sync

Question

How do you manage two-way synchronization between a 'main' database server and many 'secondary' servers, in particular conflict resolution, assuming a connection is not always available(offline syncing)?

For example, I have an Android application which is use data from local database(sqlite). And user can deal with data even it is online or offline. In meantime when if it is online, then local database(sqlite) should sync with real database(mysql) in the server and resolve conflicts as a background service.

Is there any solution already implemented for this kind of scenario ?

I heard about Couchdb which is a NoSQL solution can be a solution for this. If it is so Can I use it with SQL ?

• Peter: Depends entirely on the database structure in question. The proper way is to make things so there can be no conflicts, but that's not always possible.
• Dumindu Madushanka: As I know Couchdb and Couchbase Mobile SyncPoint have offline syncing and conflict management features. But they both are NoSQL. Is there a way to make them to use other SQL database instead of NoSQL ?

Answer 1

Basically you would need to take the commit logs from both databases and compare them, looking for instances of updates to the same table and row (and possibly column) in both databases. Any commit records for rows not in conflict can be sent to the appropriate database (i.e., a commit record from the master can be transformed into an appropriate CRUD statement and issued to the client), all rows in conflict will have to have their commits ordered temporally and the last commit issued would "win". If the commit logs are not retained, you'll have to keep them manually (probably by structuring your updates using the command pattern). This is a non-trivial exercise.

• Dumindu Madushanka: Is there any existing frameworks or implementation I can use to resolve this issue ?