How to reliably determine changes in a DB table since some sort of "checkpoint"?

Question

My scenario is fairly typical, but I can't figure out a good solution. I have a DB table (in reality several unrelated tables, but let's focus on one) where there is a fair deal of activity going on. In the table:

• Records are rarely inserted
• Records are often updated (I expect multiple records to be updated every minute)
• Records are never deleted (they are merely marked as "deleted" by one final update)

There are also multiple external systems that are interested in having an up-to-date copy of this table. The number of systems changes fairly rarely, but it does happen every now and then (this involves contracts being signed).

I now want to implement a "push" mechanism that will send a notification to these external systems when something changes. I also want to send just a "delta" - only the changed records.

The primary process which does all the frequent updates has its hands full of other things already, so I don't want to burden it with notifying the external systems. I'd rather create a separate process which regularly polls for changes and then sends the notifications as needed.

In addition, some of the systems might be offline now and then (it's not normal, but this is life - things happen). In that case, when it comes back online, I want to know what was the last update that I sent to it and send all the changes since then. And, of course, if there is a new system added, then it needs all the records to be sent.

My DB is MySQL 8.latest and I struggle to figure out a good way to do this. In every mechanism that I come up with (last_updated timestamps, counters, "generations", whatever) I can also come up with a (more or less convoluted) scenario where an update might get lost.

I would like to think that this is a solved problem in the industry. How can I reliably do this?

Answer 1

The reason @kiwiron suggested you look at replication is because it contains the technology you need to do all of these things: the binary log. Once configured, every modification to the database gets written to the binary log, in order.

You can then replicate the events either "offline" by reading the binary log, or by writing a program which appears as a MySQL slave. It would not actually be a MySQL slave, instead it would take the stream of events from the master MySQL server and convert them into the API transactions you need. The pseudo-slave will need its own persistent way of keeping track of exactly which transaction it last handled successfully.

It's still a fairly large project, but I think it holds the best chance of working.

Answer 2

Another way to do this would be to use a queue system like rabbitmq and sending events after making changes to the DB. The drawback is that any change to the DB external to your application would go unnoticed.

Answer 3

I'm going to post an answer here myself, but not accept it yet, because I might not end up using it after all, and perhaps someone will have something useful to comment at this.

The way I currently think of approaching this is by leveraging 3 MySQL-specific features:

1. You can have UTC timestamp columns that automatically update whenever a record is updated. In CREATE TABLE I can go: last_updated timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)
2. These timestamps can have up to 6 decimal places after the seconds (so basically a microsecond resolution). This is noted above by the (6) parts.
3. You can do lock tables tbl1 read, tbl2 read, ... Which locks the tables in read-only mode and makes sure that no transaction can update (or hold locks) them while I check for changes.

So my idea is to save somewhere the timestamp of the last update, then do lock tables, select all records that have changed since the last update, and then immediately unlock tables.

Now, the lock tables part feels really dangerous to me since it essentially freezes the rest of the system while I check for changes. So I need to be fast. I might even lock/select/unlock each table individually, although then I need to take care of the order of the selects (for tables that have a parent-child type of relationship).

Since the timestamps have a microsecond resolution, I'm fairly confident that it's impossible to get a situation where a past timestamp appears after I've already selected the data.

EXCEPT.

This is a real danger for stored procedures. The current_timestamp() function returns the time value as it was at the start of the procedure. So an update to a row from a stored procedure COULD get a timestamp that is who know how long in the past.

Luckily we don't use stored procedures in this project, so I think I'm good.

Added: I realized a problem with the above. The timestamp is in UTC, which means it is actual date and time. The problem is - in computer world, time can also flow backwards. And no, it doesn't involve theory of relativity. Rather computer clocks are fairly imprecise, so in order to keep them correct they need to be regularly synced to some authoritative time source. This is an absolute must-have for any production server, but it also means that the current date time can jump backwards a little when a synchronization happens. Thus there is a window of opportunity where a row can appear with a timestamp in the "past" after I've already selected all the rows to sync.

In order to combat this I've come up with another idea: for each row add another field sync_trigger which is exactly the same as the last_updated field, except that it's nullable. When you want to select rows that need syncing you look at the rows that have sync_trigger is not null and then set them to null when they're synced.