Data Synchronization in Mobile Apps – Multiple Devices and Users

datadatabasemobileMySQL

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.

Best Answer

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.

Related Topic