Software is not Jewellery

A study of syncing databases that are simultaneously being updated by multiple clients where the clients are unaware of each other.

---

It is quite simple to build client server based application that has only one client accessing data at a time. Only one client has access to a database at a time and the diagram below shows how that happens.

A database server that is accessed simultaneously by multiple clients will have issues while the client and server data is being synced. The diagram below shows a client updating a server.

The figure above demonstrates how client C1 could potentially overwrite modifications made to the database by client C2. Client C1 is unaware of client C2.

Design Requirements for Syncing

The following fields are required for every table by the application for syncing:

1. Auto incrementing integer ID
2. Last Modified Time (LMT) field that is updated with current time whenever
   1. A row is ADDED or UPDATED at client end
   2. A row is uploaded to database server

Algorithm for syncing database

• Before downloading data the server and client clocks are to be synchronized

• When data is downloaded by client at time Download Time (DT) a copy of the server database (DBsv1) is stored locally. And DT is noted

• Whenever a row is ADDED or UPDATED by the client the LMT field is to be updated with current time

• After modifications are done, the client copy of the database (DBcl) is saved locally. Another copy of server data is to be downloaded (DBsv2)

• Create a list of [ID, LMT] for every table in DBsv1, DBcl and DBsv2. Lock DB at server from accepting any modification

• The following conditions need to be checked for every table in the database. Comparing DBsv1 and DBcl

Rows Modified at Client (By client C1)	Named List 1
ROW ADDED 1. If ID present in DBcl but not in DBsv1	RAcl (ROW ADDED at Clinet)
ROW DELETED 1. If ID present in DBsv1 but not in DBcl	RDcl (ROW DELETED at Clinet)
ROW UPDATED 1. If ID present in DBcl and DBsv1 && LMT in DBcl > LMT in DBsv1	RUcl (ROW UPDATED at Client)

• Comparing Named List 1 and DBsv2

Rows modified at Server (By client C2 or other)	Named List 2
RDcl ROWS UPDATED 1. If ID present in RDcl and DBsv2 && LMT in DBsv2 > LMT in DBsv1	DRUsv (DELETED ROW UPDATED at Server)
RDcl ROWS DELETED 1. If ID present in RDcl and not in DBsv2	DRDsv (DELETED ROW DETELED at Server)
RUcl ROWS UPDATED 1. If ID present in RUcl and DBsv2 && LMT in DBsv2 > LMT in DBsv1	URUsv (UPDATED ROW UPDATED at Server)
RUcl ROWS DELETED 1. If ID present in RUcl and not in DBsv2	URDsv (UPDATED ROW DELETED at Server)

• Start making changes to server. ADD all ROWS in RAcl to database server

• Delete ROWS in DRDsv from database server

• For each ROW in DRUsv alert the user. If possible show the contents at the server and ask for action to be taken

• For each row in URUsv alert user. If possible show the contents at the server and client side by side and ask for action to be taken

• For each row in URDsv alert the user. If possible show the contents at the server and ask for action to be taken

• Ask the user if new data is to be downloaded from the server. If yes load DBsv2 into the client

• Unlock the Database