I want to synchronise the local database of mobile devices with the server's database. This synchronisation will happen for example when user comes online, to update the local as well as the remote database with possible changes.

I thought about 2 approaches and already implemented 1 of them, but it's quite complicated and I fear a bit about it's performance and error-proneness.

The approach I implemented does all the synchronisation in the server. I'll store in the client's database the last server update timestamp for each item and a flag for items that are marked for removal. I send all my items - with their timestamps and flags - to the server, and the server then decides which items have to be inserted, updated - only if the last update timestamp of the client is equal to the last update timestamp stored in the server (meaning the client has the latest version of the item) and what has to be deleted. Then I do a fresh query to the database and send everything back to the client. The client overwrites it's local database with the result.

The problem with my approach is that I'm doing many different queries and programmatic processing, and another client (some items can have multiple users) could update something in between these queries and updates can get lost. I assume I have to compact my queries (try to somehow do everything in a single transaction, maybe use stored procedures?) and lock rows to avoid synchronisation issues.

Because of these complications I was thinking if there's maybe a simpler way. The other idea I had, was to do the synchronisation basically in the client. So I'd do first query all my data and do and update (for which I still need the last update timestamp and delete flags - only this time the sync logic is in the client and I don't have to worry about multiple users here), and then send the sync result to the server which will just do an overwrite. The problem here, of course, is that another client can update items in the server while this client is doing sync, and when it uploads the results, the updates of former client will be lost. I thought maybe I can work around this with a hash, so the downloaded data has a hash and when the database is to be overwritten with the sync result I do a query first and check it's the same hash, if not return an error. But then again I'm with multiple queries in the server and not sure this a recommended way.

I'd appreciate thoughts on this matter. Is my current approach the best? Or should I rather go with the client sync (this would also reduce workload in the server, which is good), but then how do I avoid possible overwrites, is the hash a good idea? Note that my app doesn't have super strict requirements, it's something like shared todo lists with a few additional features, nothing generic (like a file sharing service) or for health, science etc. so if once in a (long) while an update is lost it's not the end of the world. I care a little more about performance and implementation easiness than 100% correctness. Of course the less error prone, the better.