On 11 Jun 2012, at 11:11pm, "Black, Michael (IS)" <michael.bla...@ngc.com>
wrote:
> Isn't it true that semi-accurate (sub-second) time-tagged transactions will
> generally keep 2 databases in sync?
>
> I've done that before with considerable success. You don't sync the
> fields...you sync the transactions. The presumption is that later
> transactions win regardless of intervening updates.
Your idea, if expressed in terms of SQL, is that you do not keep copies of the
records in the databases, instead you keep copies of all the INSERT, UPDATE and
DELETE commands executed. To synchronise two databases you just make sure all
the commands are executed on all copies of the database.
It's a great idea. Except it doesn't work unless the commands are executed in
the same order and there's no way to do that starting with one or other copy
which has been changed since the last sync. So you need a third copy of the
databases: the database as it was the last time two copies were synchronised.
And this needs to be stored either centrally or with each copy of the database,
which means there's no magic simple synchronisation algorithm.
And then you get into intent. Suppose you have this row of data:
{name: Mike Smith, town: London}
on your database. Since the last synchronisation one user of the database
executes the command
UPDATE contacts SET town='Hendon' WHERE town='London'
And an hour later the user of the other copy executes the command
DELETE FROM contacts WHERE town='Hendon'
Before the two were synchonised, the row for Mike Smith still exists in both
copies. Both users were happy with this, even though they didn't know they had
different towns for Mike. But after the two are synchonised and all
transactions are played back in log order, Mike Smith has somehow disappeared
from both copies. That's not synchronisation, that's deleting data we wanted
to keep ! That's disastrous !
> Only problem is when times are too close (beyond your time-sync resolution)
> which requires human intervention...though in a user-driven system that
> should be nigh on to impossible to create.
For the sake of discussion you can pretend that your log stores times to Unix
epoch precision. Even with that, as you can see, there are still problems.
> I do realize the complexity of keeping two database in sync....but
> transaction systems have been around for decades. Depends on your
> application.
Yet it's still an unsolved problem. I can (maybe should) write some text
layout out this problem and some solutions which work in some situations but
there's still no general solution that I know of.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
