Data Synchronization in mobile apps - multiple devices, multiple users

Question

I'm looking into building my first mobile app. One of the core features of the application is that multiple devices/users will have access to the same data -- and all of them will have CRUD rights.

I believe the architecture should involve a central server where all the data is stored. The devices will use an API to interact with the server to perform its data operations (e.g. adding a record, editing a record, deleting a record).

I imagine a scenario where synchronizing the data will become a problem. Assume the application should work when it is not connected to the Internet, and thus cannot communicate with this central server. So:

1. User A is offline and edits record #100
2. User B is offline and edits record #100
3. User C is offline and deletes record #100
4. User C goes online (presumably, record #100 should get deleted on the server)
5. User A and B goes online, but the records they edited no longer exist

All sorts of scenarios similar to the above can come up.

How is this generally handled? I plan to use MySQL, but am wondering if it's not appropriate for such a problem.

Answer 1

I'm currently working on a mobile/desktop/distributed app with exactly the same requirements and issues.

First of all, these requirements are not inherent to mobile apps per se, but to any disconnected/distributed client-server transactions (parallel programming, multithreading, you get the point). As such they are, of course, typical issues to address in mobile apps.

Generally, what this all boils down to is that you have a potential data record that is distributed to n clients, who may edit it at the same time. What you need is

1. a proper version control/locking mechanism,
2. a proper rights/access management,
3. a proper synchronization/caching strategy

For (1) you may apply some patterns: There are two frequently used locking strategies: Optimistic Offline Locking, and Pessimistic Offline Locking. Some of these come applied in different version control "patterns", such as MultiVersion Concurrency Control (MVCC), which uses a counter (sort of a very simple "time stamp") for every data record, which is updated whenever the record is changed.

(2) and (3) are very broad issues themselves, which need to be dealt with independently of (1). Some advice from my experience:

• Use a client-server technology that abstracts away most of the issues for you. I highly recommend some web technology such as CouchDb, which handles (1) via Optimistic Offline Locking + MVCC, (2) via Web API, and (3) via Http caching very well.

• Try not to invent things yourself if you can rely on proven technologies and approaches. I believe any hour spent researching and comparing existing technologies/patterns is far better spent than trying to implement your own system(s).

• Try to use homogeneous technologies if possible. By "homogeneous" I mean technologies that have been built with the same principles in mind, e.g. web 2.0 usage scenarios. An example: Using a proper CouchDb and REST Client (Web API) with a local caching strategy is a better choice than using SQL for mobile apps.

• I strongly advise against the use of MySQL because it is a technology that was not explicitly made for such usage scenarios. It works, but you are much better off with a database system that already embraces the web communication and concurrency style (such as many NoSQL Databases).

By the way, I have settled for CouchDb with a custom local client working against the CouchDb APIs, which works and scales beautifully. I switched from using MSQL + (N)Hibernate and paid a high price for not making the right choice (meaning not doing enough research) in the first place.

Answer 2

First, you mentioned both an API and a database (MySQL). I very much recommend that you use an API and don't try to communicate directly between the databases. That latter route will not scale well at all.

One good starting point you should consider is using Apache CouchDB. It is schema-less, based on HTTP and JSON, and has a very good replication mechanism. We use it to solve a similar problem.

CouchDB's replication mechanism uses the same HTTP API that any other client uses. So in essence, it provides replication over an API.

For iOS, I recommend using the Couchbase Lite project. It works very well for synching data. For Android, the same company that makes the aforementioned Couchbase Lite project is working on a similar offering - Couchbase Lite for Android. It is not as complete as the iOS version and has some work left to accomplish.

There are a few things to consider with CouchDB however.

1. You will need to provide your own conflict resolution. Fortunately, if conflicts occur, CouchDB keeps the conflicted versions and picks and arbitrary, but deterministic conflict to have as the main version. So you could consider delaying conflict resolution for your initial version.
2. The replication mechanism is made for replicating databases, not synchronization per-se. So if you have a lot of deleted documents, your replication from the server to the client will take longer and longer. There is a way to avoid this using "database rotation." This essentially removes old deletes.
3. You can't control the replication order. You can, however, make some clever solutions to improve the replication performance such as using filtered replication to get some documents first, or even access the server directly on-demand.
4. The replication will not happen in the background on iOS. You can utilize the iOS SDK to provide some cases of background replication.

Finally, if you don't want to use CouchDB, you can at least use it as a good reference for how you could make a synchronizing algorithm using an HTTP API. My suggestion would be to start with CouchDB and then, if you need something more custom, to consider rolling your own.