0

I'm working with a client who currently uses a database system that they've outgrown, so we're re-building it using a different platform that can handle their growth. One feature of their current system, built into the platform, is that updates to data are automatically propagated to others viewing the data in real time. So if multiple users see a particular record on their screen and one of them updates that record, everyone viewing the record will see the updated data immediately. There is record locking, so only one user can be editing the record at a time.

This feature is important to the client. Currently the most likely replacement for their current system is a web-based client accessing a back-end database. Obviously this auto-update feature isn't built into such systems, so we need to recreate it. But we're unsure of the best way to do so. I've thought of a few possibilities, such as each client tracking which records they're viewing and periodically polling the server to see if those records have changed, or having the server do this tracking, and send a message to the clients if one of the records they are viewing changes.

But I'm pretty sure this is a solved problem. So is there a standard method in software engineering to deal with this?

4
  • you dont say what the client is. but i think you mean this : en.wikipedia.org/wiki/WebSocket
    – Ewan
    Commented Apr 24, 2021 at 17:39
  • The client is a web browser. We're currently considering the Lighting platform from Salesforce, but that's not set in stone. We might roll something custom using MySQL or PostgreSQL for the back end store and a framework like Flask or Express for the back end and React or Angular for the front end. But my question is about the issue in general, not solving it with any particular technology. But I will investigate the link you provided.
    – Chuck
    Commented Apr 24, 2021 at 17:48
  • 1
    I believe the standard method is to not do this in the database. It's done by the application server(s). Commented Apr 24, 2021 at 18:38
  • 1
    Web Sockets gives you the capability to speak to the browser in real-time. Now all you need to do is figure out how to kick off that communication when someone changes a record in the database. How that is done will depend on what technologies you're using. There is no "standard" method; your final solution will be technology-specific. Commented Apr 24, 2021 at 20:53

2 Answers 2

2

I don't know about standard, but here is how i would do it.

Components:

  • (DB) Database - for persisting data
  • (MQ) Message Queue - for pushing events to listeners
  • (CC) Client connector App - to send and receive the messages to the client web browsers
  • (DAL) Data Access Layer App - updates/reads the database in response to messages

Load Use Case

  • Client clicks Load Doc on website Message sent to CC
  • CC assigns client to watchers list for the doc, retrieves current version from DAL and sends back to Client
  • Websockets, long polling, web notifications, or other push notification channel opened between CC and Client

Edit Use Case

  • Client updates document, update message including the new doc or the change is sent to CC

  • CC sends update message to MQ including the details of the change.

  • DAL receives update message, locks db record and updates

  • DAL sends doc-changed message to MQ

  • CC revives doc-changed message from MQ (remember you might have more than one instance of the CC running)

  • CC forwards message from the MQ to listening clients via open push notification channel.

The difficulties are:

  • Push notifications to browser. You can't use a standard web request as the server initiates the connection. But there a number of technologies available to do this now.
  • Managing many constantly connected users. Every user has an open connection to the server. if you only have a few users at any given time this is fine, but if you have millions its going to be a problem.
  • Scaling. when you scale out to multiple instances of the CC each instance needs to know which documents the connected clients are watching. You can't move those connections around easily
  • Lost messages. You can't guarantee every message gets through to the client. Although you should be good with 99.9% you will need a fall back mechanism to detect when you missed a message and to bring the client back up to date.
  • Conflicting edits. If you allow multiple users to edit the same doc you need a way of resolving two conflicting edits that happen at the same time. You wont know exactly which happened first due to the various latencies in the system.
1

In general this isn’t done by the DB, instead it is done by using a middle man that keeps track of active records and viewers. When a client requests a record , it doesn’t do so by directly connecting to a database and reading the record, instead it makes the request of a service that includes a call back mechanism. When updating a record, again the client doesn’t do it directly, it calls the service asking for the data to be updated and notification be sent to anyone that is currently viewing the data.

What middle man you use, will depend in large part on your client technology.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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