2

Is there a generic way (useful formalisms) to think about data synchronization between two systems, especially in the case of slightly asymmetric roles (eg, one system contain master data, another one specialized set of data, overlapping master data), broken down to individual entities. For simplicity, mapped one to one.

So far I can see the situation modeled as two sets with relation between them (say, A and B). Each set correspond to their system. And each element has some attributes like UPDATED, DELETED, UNCHANGED. As a result, there can be 16 different combinations (include MISSING, when another system lacks element present in the other). Given combination, action is almost obvious (or maybe not).

The above is just my ad hoc model. I guess, there are serious books and practitioners out there, which do it better.

As a result I want to have a procedure, which correctly synchronizes two systems' data and requires a minimal set of attributes to store on each side (for example, last modified date, last synchronized date, element ids, etc). Smaller details (like what to do when both systems have updated element) should also be localized and clearly visible from the formalism itself, not just left to ad hoc decisions.

Even though I've read quite a lot on topics related to systems integrations, I've never seen anything of more or less rigorous. And the only other domain, which comes to mind, is version control systems, but those are too specialized. Also, I am not interested in this question on the specific implementation (eg, using queues or some other components) as I want to get to the bottom of the math of this.

Any pointers?

I realized that I have not described some assumptions. One of which is that unfortunately there are no streams of events for the systems, especially, the remote one, so the only option is to make queries (lists as well as for getting/upserting individual elements). However, there are "last modified" timestamps and "local" system can record last synchronization times and remote system ids (if that will be needed). Also, queries can be batched, so theoretically it's possible to fetch all entities of one type. For simplicity, it can be assumed that ids and timestamps can fit into memory at the same time. Also both sides are OLTP type, no big data or high velocity of data involved. Data transfers are bidirectional. Even though systems are using transactions, single transaction is confined to a single query, that is, for example, fetching and then updating can't be done in one transaction. Luckily, only "eventual consistency" is required for synchronization scenarios.

Also, I am trying to use same approach possibly for other systems with the same means of access.

  • Okay. What sort of data can you obtain from the remote system? Can yoiu access a persistent event store? Do you receive a lossy stream of events and have to reconcile? Do you obtain a single Delta, or data dump periodically? Is it that you do not have an access at the moment and are negotiating how you will receive this data? – Kain0_0 Apr 8 at 23:11
  • Updated my answer. There are no event streams, deltas or data dumps. Remote is available for queries. – Roman Susi Apr 9 at 3:11
  • Are you able to access the transaction log of the database server for this database? Is it possible to push updates/insert/delete through some form of pub/sub. If not is it possible to obtain differential backups from the database? I realise that this does not currently exist, but could it? – Kain0_0 Apr 9 at 7:41
  • No. It's behind REST API only. Will never be the case. – Roman Susi Apr 9 at 11:01
  • I've amended my answer somewhat. – Kain0_0 Apr 9 at 23:31
2

Version Control, and Event Streams

Actually version control systems are the most efficient way to achieve this, and also the most mathy way to describe this.

  • The system is an append only series of events, with an initial state.
  • Each event applies a state transition to the prior state and returns an updated state.

Each event can be considered a lambda, and as such we can apply formal lambda calculus to the topic.

API Access Only, without an event stream

Unfortunately that does not bode well. Timestamps in a database are not necessarily sufficient to determine if a state transition has occurred:

  • First the system may not have updated the field,
  • but presuming it does, the granularity of the time stamp may be to coarse to pick up fine grained time transitions.
  • also a long running transaction may commit a much older time stamp.

As you are aiming for eventual consistency you can use the time stamp to form a pseudo event stream. Periodically ask for all records whose timestamps were around (some k seconds before) the last retrieval or newer.

If there are logical constraints on the form of data, you can of course use them now to infer missed updates. Something like a person always has contact details but the record isn't known/is too old. This can allow some measure of self-healing.

However at the end of day (or some other periodic scale) you will want to balance the books. Essentially dump all data from the API and check it for consistency. If the API provides a checksum, you could compare checksums, but as your intention is to cache the data, a full data dump is both more rigorous (no hash collisions) and far simpler to ingest.

Unfortunately the mathematical underpinnings here are a direct set comparison. Nothing terribly fancy. A simple Venn Diagram of two intersecting circles representing each system. The goal is to get all the data into the intersection. There are two method to do this:

  • insert
  • delete

Update is simply a (delete, insert) pair over a record that is presumably the same identity.

No Duplication

Alternately dispense with the notion that your program controls this data at all. Any time it needs data from this API call to retrieve it. If you are reasonably happy with 10 minute old data, add a cache which expires entries older than 10 minutes (or whatever interval is appropriate).

  • Thanks! Can you please elaborate more on that link to lambda calculus? – Roman Susi Apr 8 at 11:24
  • The problem is that remote system is not under my control (as is usual with data sync), so it's a bit hard to have it as an argument to lambda... – Roman Susi Apr 8 at 13:55
  • The answer somewhat confirmed my doubts there could be some fancy TLA⁺ proofs there. Also, good catch - "no dup" option considered as well, but the data in the system B is too central to go that path. Thanks for sharing you thoughts on this! Hopefully, this answer will be useful for those searching something similar. – Roman Susi Apr 10 at 3:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.