2

I work on a large and old application consisting of a server and a fat client. Part of what the application does is handle a large-ish (a few 100MBs) database of frequently changing data (~a dozen rows per second). Because of the size, there is a master copy of the database on the server and a local copy on every client and they need to be synced. Changes can either be caused by outside events coming to the server or they can come from user interactions. In the first case, the server updates the master DB and propagates that to the clients. In the second case, the client sends a request-to-change to the server, the server updates the DB and propagates that to all clients, including the original one. In addition, clients have a "undo"-feature that allows to reverse-apply the couple of last changes the user caused.

We use JPA/Hibernate as an ORM layer between the database and our code, both on the client and the server side. But there are different database backends on both sides.

At the moment, our solution is old, half-baked legacy code: Diffs between objects are calculated based on string representations of their attributes. The corresponding old/new pairs of string-values get distributed for syncing and stored in a separate table for later undos. Lots of things can and sometimes do go wrong the way it is now.

What is the preferred way of doing this? I've looked through some Hibernate docs and tutorials, but it seems there is no ready made solution with JPA that does this out of the box. I could probably design something that's less half-baked, maybe three-quarters-baked with @Audit and Entity Listeners. But I'm assuming that some smart people have already come up with some design pattern that realises this. Can someone please point me in the right direction?

  • Does the "undo" go through the server too? In other words, it sounds like in all cases the central server is the authoritative location, correct? – davidbak Nov 24 '20 at 21:17
2

Sounds like a nightmare.

In theory, if you publish the events to a message queue you can have them processed against the master database and have undo event work fine.

However, with multiple clients all processing transactions I'm not sure I would trust it not to go out of sync. Really each client should potentially be getting a lock on all databases before writing to maintain atomicy, (this would I assume kill any performance advantage).

I think I would go with making the local DBs read only caches and having all writes processed by an API which writes to the master DB.

This will allow you to use simple one way replication from the master and undos will work fine.

If the local DBs needs to see the local writes prior to getting a sync from the master, then you have the bigger problem of them not seeing the updates from other clients, which they need, and your system is already broken.

This would be a step on the way to getting rid of the local DBs entirely and running everything through the api, which can cache, cache invalidate and scale better than a central DB.

But imagine that is the "Big Rewrite" option, which is never palatable.

  • Oh, I see I didn't make myself particularly clear: All changes coming from clients, including undos, are sent as change-that-object instructions to the server which changes the master database and then propagates the change-the-db instructions to all clients, including the original client. Clients do not have their changes in their own DBs before they are committed on the server side. – Johannes Hahn Nov 24 '20 at 15:28
  • I have updated the question accordingly. I hope it is clearer now. (But you are still completely right that this system is highly questionable, full of race conditions and just in general error-prone in a myriad ways) – Johannes Hahn Nov 24 '20 at 15:40
  • so whats the problem with undos? are you replicating the dbs out or is it custom code pushing the changes? – Ewan Nov 24 '20 at 15:55
  • ie., dev.mysql.com/doc/refman/8.0/en/…. – Ewan Nov 24 '20 at 15:56
  • An undo-operation only undoes some user-generated changes, but not necessarily the changes that came from external sources, because those take precedence. If the outside world changed against the wishes of our users, the data should reflect the outside world, not the wishes of the user. So it's not just a rollback to a previous version of the database, it really is a selective reverse-applying of specific changes, which can even fail sometimes when the outside world made incompatible changes in the mean time. That is one (of several) reason(s) why a database replication is not feasible. – Johannes Hahn Nov 24 '20 at 17:26
1

OK I'm going to add a second answer in the assumption that the replication of the data isn't really the problem at all. Its applying an undo operation.

First off, you have to take into account that given your "external world" changes, the undo may be impossible. If you have sent the email, its sent, you cant undo.

However, given that you can write some rules to handle those cases the general case can be handled by variations of

  • Command pattern
  • Event Sourcing

I wont go into too much detail but essentially both of these patterns keep track of changes to the object rather than just the object state.

Thus you can recreate the object to any point in time by simply replaying the changes, and hence "undo" changes

eg.

  1. new order
  2. add PS5
  3. add plug
  4. set address
  5. amend card holder name
  6. remove plug
  7. add furbie
  8. make payment
  9. send order to delivery centre
  10. post order

So you can see by storing each event or command, you can recreate the object by simply replaying the events from the start to a desired point.

Obviously if your events had real world consequences such as making a payment, or posting a package, recreating the object as it was before those events isnt helpful. you have to apply a reverse event, a repayment or returns process etc.

But for things like undoing adding an item, or editing a document etc it works well.

-1

But I'm assuming that some smart people have already come up with some design pattern that realises this.

And they have. The Memento pattern was one of the original design patterns in the GoF book of the same name. As stated in the wikipedia article:

The memento pattern is a software design pattern that provides the ability to restore an object to its previous state (undo via rollback).

It's not specific to your situation but, conceptually, it provides both a explanation of the kinds of issues you are running into and strategies for solving them.

The general approach of storing a series of diffs seems to potentially be aligned with the above. If there was only one client apply changes, I think this can be made to work. The big challenge is if you have other users making changes to the same or related entities. What does that mean for the other client's changes, especially in the case that their change depended on a change that another user has now undone?

What specific kinds of issues occur? Are they related to multiple users making modifications? Is there a flaw in the string representation of the object and/or the interpretation of the associated diffs? Something else?

  • Maybe I'm missing something, but the memento pattern does not seem viable in my situation or really any situation with lots of different changeable business objects. Writing memento-related classes and methods for each entity would simply be an unmaintainable nightmare of boilerplate code. And that code would also need to replicate some JPA-features like handling of @ Embeddable, @ IdClass, @ EmbeddedId etc. The only hope of ever improving the mess that is our legacy code is some generic solution that builds on top of JPA. – Johannes Hahn Nov 24 '20 at 17:34
  • @JohannesHahn The point here is that the way you solve this kind of problem is fundamentally about how you structure the data. That is, you need to keep track of all the transformations. If you were starting from scratch, I would suggest a insert-only database design although that doesn't necessarily address changes made by multiple users. In that model, you would simply delete the changes or mark them as soft deletes. – JimmyJames Nov 24 '20 at 19:37

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.