Re: Database syncronization

Yuval Oren <yuval_at_bluecast.com> wrote:
> Here are some options:
> 1) Pre-assign batches of values to each computer so there can be no
> conflict

Perhaps. If there are tables to which new values are regularly assigned, a good approach would be to NOT simply use counters, but rather to use OSF/DCE style GUIDs, which combine a network with a time stamp and some other more or less random "salt" to create unique values.

> 2) Set up some rules for handling conflicts when they arise
>
> 3) Check with a master database on every change
>
> Option 3 will of course be slow if you're doing many changes to your
> data. You can implement option 1 by creating a replicated table of
> "value blocks."

The problem you're missing here is that if the DB servers can't agree on what time it is, you've got a _world_ of trouble waiting for you.

A lot of the synchronization efforts depend on knowing at what relative time each update took place.

For instance, suppose if you have updates, based on True UTC times:

at 10:01 - change value of entry with ID 1234 to 555 on server A
at 10:34 - change value of entry with ID 1234 to 567 on server A
at 11:07 - change value of entry with ID 1234 to 789 on server B

If there were "syncs" every 15 minutes, or even six hours, then there's little problem here; these updates can consistently propagate back and forth between hosts A and B.

But if the clocks are deranged, perhaps with B being off by 45 minutes, you'd get: a sequence looking more like:

at 10:01 - change value of entry with ID 1234 to 555 on server A
at 10:22*- change value of entry with ID 1234 to 789 on server B
at 10:34 - change value of entry with ID 1234 to 567 on server A

• B is deranged by 45 minutes!

And so the update from B gets lost in the shuffle. Whoops.

The first crucial part of fixing this is to make sure the clocks _aren't_ deranged. That may be _somewhat__ difficult to accomplish on laptops with CMOS clocks of dubious integrity that might throw themselves out a bit when turned off and on.

But if all the hosts are connected to a common network, it's easy enough to synchronize their clocks using a protocol like NTP.

-- 
(concatenate 'string "cbbrowne" "@acm.org")
http://www.cbbrowne.com/info/internet.html
--Despite Pending :Alarm--