With online/offline applications, the data is distributed between the back-end database and the data on the client. The data can be updated in two places: client or database.
Conflicts occur when the same data is updated at client and the back-end database at the same time.
For example, a user downloads a list of jobs to his PDA before starting work. During the day, he completes his jobs and updates them on his PDA, but does not have a network connection until the evening.
Also during the day, someone in the office decides to move some of the jobs to another employee and updates the database.
In the evening, the user synchronises his updated jobs - he has completed them all, even though some were re-assigned during the day.
What happens to the jobs he completed, but which were re-assigned to another employee (who may also have completed the job)?
Data conflicts like this may need to be detected and controlled. There are a few ways to handle this:
- Server wins: Any update to the data on the back-end database is kept, and the client data is lost.
- Client wins: The data from the PDA is applied to the database, and any updates at the backend are lost.
- Mixture: The server-wins and client-wins rules are applied per database field. Some fields are updated with data from the client and some keep the server data.
The two main ways to detect a conflict are:
- Optimistic locking.
- Data hashing.
Note that when the server does not use the data that the client has sent (a server-wins scenario), the data at the client will be different to the data at the server. Your app may be coded to delete the data on the client after a successful update, in which case there will not be an issue. Or you may write the code so that after sending all the data, you delete all local data and request everything from the server; everything is still fine.
However, if you need to keep data on the client and make sure that it holds the same data as the server, you can use the return value from the HTTP request to indicate that a data clash has occured. The client can then re-query the server for that job and get the most up-to-data data.
Add a data_version column to each table. Whenever an update is made to a row in the table, increment the data_version number.
When an update from the client PDA comes in, the server checks the data_version number from the client against the data_version number currently in the server row.
If they are the same, the update from the client can be done.
If they are different, the data on the database has been altered since it was downloaded to the client. There is a data-clash and the software needs to decide whether the client or server data should be kept.
As each row of data is sent down to the client PDA, a hash of the data in that row is sent to the client and/or stored in the database against that client. The hash must be made up of the "important" fields in the row, not necessarily all the fields.
When an update from the client PDA comes in (including the hash originally sent from the server), the server checks the original hash from the client against the hash of the data currently in the database. If they are different, a conflict has occured. As above, your software needs to apply client-wins or server-wins rules.