Simon, Your example is a very simple, yet elegant example of why database synchronisation is a ?difficult? problem to solve.
As you say at the bottom of your e-mail, for the database to be correct, it has to determine the intentions of two sets of users to work out the solution. Even if somehow ?the database? manages to know what the users intended, using your example what is the correct answer, should town be ?Cambridge? or should town be ?Camberley?? One user would say Cambridge and one would say Camberley. So even knowing the users intentions would not be enough to work out the correct answer. A timing log simply shows who was first and second and has to ignore intentions as the database has no other information to work with. This problem has been going on for years and I am not aware of a solution to the problem, however I do not claim to be an expert (or even a knowledgable user), I suspect that the problem has been proven to insolvable if we make the assumption the databases are equal in status. if we have a master database and a slave database then the master always wins (kind of why they are the master) and the problem is simple. As a simple example of a large company who could not solve this, consider IBM and Lotus Notes. Lotus Notes has been running for around 25 years, it has a client database on the users machine and a remote database on a server. IBM could not guarantee that the client database and the remote server database were always synced correctly and would produce replication conflict errors (sync errors really) when your databases did not match. It presented the sync errors in time order (as Simon has suggested) and allowed the user to choose which was the correct version to keep and which to delete. IBM never resolved this problem after 25 years and I suspect nobody else has or will. I?d be interested if anybody has a counter example to this to show how syncing can be made to work, as I have said I do not consider myself an expert here, but I have <cough> sold </cough> DB2 to customers and have more than once stood up in presentations to talk about our DB2 technical solution. Its amazing how much one can cram in the evening before the client briefing ?. :) Rob > On 13 Aug 2015, at 17:20, Simon Slavin <slavins at bigfraud.org> wrote: > > > On 13 Aug 2015, at 4:03pm, sqlite-mail <sqlite-mail at dev.dadbiz.es> wrote: > >> With that would be trivial to log the statements that change the database to >> replicate elsewhere. > > As Jean-Christophe wrote, it's not that simple. There are huge books written > on the problems involved in synchronising two copies of a database, and none > of them end with a chapter called "Solution". > > Here's an example of one of many problems. > > Two copies of a database are maintained. In one copy, the following command > is issued: > > UPDATE contacts SET town = "Cambridge" WHERE town = "Grantebrycge" > > In the other copy the following command is issued: > > UPDATE contacts SET town = "Camberley" WHERE town = "Cambridge" > > So when the databases are synchronised, one copy has command 1 then command > 2, whereas the other copy has command 2 then command 1. Here's the result > after the synchronisation: > > Original Copy A Copy B > -------- ------ ------ > Grantebrycge Camberley Cambridge > > As you can see, the two copies are not identical. And the resolution of the > problem (deciding what /should/ happen) depends on the intentions of the > users, which is something the software can't know. > > This is normally resolved by logging the time each command was issued and > keeping a central copy of the database which remains unchanged when changes > are made to the live copies. Upon synchronisation the change logs for all > copies are merged in time order, and the resulting log is applied to the > central unchanged copy. Then copies of this new version of the database are > copied to each client. > > So now you need some sort of 'boss' node which does extra work. And even > this still doesn't please users, some of whom will see their changes be > ignored or changed again by other people. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
