I dont' see the difference between a a single database and 2 in your example.
I would claim the delete is most certainly not unintended.
If you had just one database a user could come in one hour later and delete the
record you updated. I realize in the "pure" sense this may not be desirable in
some situations. But you're talking right now about how you keep two users in
sync which is a totally different story.
And, of course, you NEVER really delete a record...you just tag it as deleted.
So I still maintain -- he who updates last wins -- regardless of 1, or N
databases.
I would bet that 1/10th or 1/100th timing with appropriately time-synced
computers would work just fine.
Trying to sync two databases without transactions is pretty hopeless and
fraught with problems. Unless you have a "master" who always wins. You can
tag a record as "changed" and the slave can update if the master has not
changed, otherwise the master wins. The master then replicates to all slaves.
As for connectivity each system just ensures that it not process any
transactions beyond the most recent time received from the other system.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, June 11, 2012 5:31 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Features of SQLite question
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
