Syncing SQL changes with GCM

[jogo]

Hi,

does anybody use GCM for syncing changes in SQL database across multiple Android clients? I am working on a project where I need to propagate changes made in database in one client to other clients. All clients are Android applications and we talk about ten max. twenty clients which needs to be notified of changes.

And using GCM could be a viable alternative rather than implementing my own sync mechanism with polling.

Basically, when a client makes a change in database (new row is added, existing row is updated or a row is deleted) it sends this change to the server. Server decides whether it approves the change (client is in sync) or rejects (client is out of sync).

If the change is approved, server updates its database and notifies other clients via GCM (excluding the one who sent the change) sending a multicast message carrying operation that needs to be performed on those clients (add / update / delete row).

I did yesterday a proof of concept implementation and it works like a charm. I get notifications almost immediately.

I am not concerned about receiving duplicate messages. Operations sent in those messages will be idempotent. So receiving the same messages twice is ok.

I am not concerned if messages are not delivered immediately. But must be delivered. Ideally within 30 minutes or less.

But I am concerned if a client does not receive a message. Loosing just a single message means client lost synchronization with server and is out of sync.

So my question is how reliable is message delivery in GCM? Can I bet on GCM in this scenario? Or should I rather implement my own sync mechanism to be on the safer side.

What is your experience using GCM and reliability of message delivery? And ideally I would like to hear if someone uses GCM for syncing SQL data.

I love the idea using GCM for syncing data changes. That really simplifies it a lot. But I would like to avoid any problems as soon as the solution goes into production as syncing data changes is the most critical part of the solution.

Thanks.

[jogo]

Hmm, I did few tests today what happens when device gets offline and server keeps sending notifications. It seems all messages were delivered but not always in the same order. Some messages were "stuck" and delivered after newer messages when devices got back online.

What still should be fine for ADD / DELETE operations. And for UPDATE operations I will need to add time stamp to each operation and keep track of last processed operation on each row in the database and skip all operations which have older time stamp than that.

But I am checking the docs and maybe using a collapse key for INSERT operations could be a good idea. The key will be the ID of item being updated. This should make the trick that older messages for the same item ID will be discarded by GCM so the client always receives the latest message when gets back online and no additional logic will be needed on the client.

[Yi Cui]

There is no need to resend a message if your device is offline. We keep undelivered message for a month. Whenever your device comes online, we deliver it.

Collapse key is great to "correct" system behavior. Besides the use case you mentioned, another case is if you realize you pushed down a wrong UPDATE/DELETE operation, send the correct operation with the same collapse key. It will cancel out the old message on all disconnected devices. But the damage would still arrive to the online devices.

GCM is pretty reliable, though not entirely. And we do not guarantee ordering. So you still need to develop application-layer mechanism (e.g. per-item sequence number and timestamp) to achieve reliability and ordering. If your app has a high delay tolerance to miss an update, then "syncing via GCM" is an worthwhile effort (which saves your app from waking up and syncing frequently). Otherwise, it's probably not worth it. The bottom line is your app must still initiate sync to your server periodically. And you have to reconcile conflict of simultaneous updates  from multiple devices.

[Bogdan Zurac]

Hello.

We are in the last stages of developing an app which works similarly to what you describe. The difference is that we also have a master database on the server side. We use GCM CCS with upstream messages to send the changes on the client up to the server; also downstream messages to propagate the changes received from the upstream message to the other clients that need the same data.

As for the CRUD issues that may appear:

1. Message ordering. In our case, it really matters. Because you can't do a DELETE first on a resource, than receive the UPDATE message on it. So we put guards into place for this, because GCM can't send the messages in the same order they were sent.

2. Duplicate messages. We did encounter this issues. Check this thread. We solved it on the server side, we are still testing on the client side.

The GCM CCS system works well in our case because each device would only send maybe half a dozen messages per day, and require that the message would be received in a timely manner, not necessarily instant. So like you specified, implementing a syncing mechanism from scratch would've been more of a headache than GCM was. It's also more battery efficient for the device. You just need to at least triple check every single case in your system in order to be sure everything works fine. As for the messages being received, it works pretty well. Haven't seen a case on production (unreleased though) where a message wasn't received. I've seen this issue however when testing locally in the office. But I presume that was from the router or ISP. As soon as we deployed the staging server, the problem disappeared. But yeah, you still need to keep an eye out for this sort of issues.

I do however have a question for you. I didn't really understand this part "Server decides whether it approves the change (client is in sync) or rejects (client is out of sync)." Could you bring up an example for which a client is out of sync ?

[jogo]

Thanks for your suggestions.

@Yi

There is no need to resend a message if your device is offline. We keep undelivered message for a month. Whenever your device comes online, we deliver it.

I was not sending the same messages. I was sending messages with payload containing different ids. I understand that messages are being queued for four weeks if a device is offline.

@Bogdan

I do however have a question for you. I didn't really understand this part "Server decides whether it approves the change (client is in sync) or rejects (client is out of sync)." Could you bring up an example for which a client is out of sync?

What I meant was that a client is of of sync when a client has a different value in DB (to be more precise different value in a column) for given resource ID than the master DB on server. 

Also to clarify it a little bit. I am using HTTP transport so I can send only messages from GCM, no upstream messages from clients.

My initial thought was that I could send messages via GCM carrying a payload with new values. So the server is authoritative and whatever he sends clients accept. And clients would send via a web service their delta changes performed on columns e.g. (resource ID, column index in DB, current value on client, delta change made on the client).

So for example, client A sends values (R100, 3, 10, -1). Server then compares those values against master DB. If value for resource R100 in column with index 3 is 10, then the server changes it to 9 and notifies other clients via GCM with a message carrying payload (R100, 3, 9) with a collapse key consisting of resource ID_column index => collapse key R100_3.

This would ensure that if a client is offline and gets back online it will receive always the latest value for given column and just updates it in its DB.

But still as you mentioned I would have to do some sort of ordering of messages on the client in case ADD operation is received after UPDATE for the same resource ID. Or server will have to resend all operations since last sync, etc. What quite complicates implementation & testing and I don't have much time now for that.

Moreover, using the collapse key this way I could sync only four columns as GCM allows only up to four different collapse keys per client what is unfortunately not sufficient in my scenario.

But I will use GCM I like it :) But I will take the standard approach for now.

I will use messages with collapse key only containing the resource ID. So GCM will notify clients of (any) changes made on particular resource in master DB.

And clients will then store received resource IDs and call a web service with those IDs to fetch current data from server.

This way it will be pretty easy to implement on the client and on the server. And it will be reliable. And clients will fetch only resources with IDs which have changed. 

On Saturday, January 17, 2015 at 12:42:24 PM UTC+1, jogo wrote:

[jogo]

I got another idea which I like most and is at the end pretty simple :)

I can carry in the message entire row from master DB collapsed by ID. And that's it. The 4KB limit per payload is enough for that. I just need to sync one table and it is not possible in my case that two rows can be modified simultaneously from two different clients.

Instead of sending updates when a value in a column has changed (only 4 columns could be synced this way as posted in my previous post), GCM will broadcast entire row from master DB which has changed with collapse key = ID. This will ensure that GCM will discard previous messages for the same ID for offline devices and devices will always receive exactly one message for each row which has changed containing the latest row values => no need for any extra ordering logic on clients.

Clients then simply delete that row from DB and insert new values => no need for fetching data from server. Only if clients are too long offline and receive the special "messages_deleted" message then clients will do full sync via a web service with server.

Also in case they have lost synchronization with server a full sync will be needed. But I hope this will not be often ;)

On Saturday, January 17, 2015 at 12:42:24 PM UTC+1, jogo wrote: