Methods for tracking changes when making realtime updates to a webpage

Question

I'm looking to update a list of orders (and statuses) real-time on a webpage. The orders in the (MySQL) database are updated asynchronously through other processes (PHP).

I'm familiar with the mechanics of pushing data to pages (polling, event-source). This is not about that.

What I'm struggling with is figuring out exactly what data to push for each user without

1. needlessly updating list entities that don't need to be
2. not missing an update.

My table does have a DateTime column last_update_date that I update when there are any changes to the order. I know MySQL doesn't really have any event triggers that can trigger other code.

Ideas so far:

1. In my JS I could track the time of the last request and on every subsequent request, ask for data since that time. This doesn't work because JS time will most likely not match server MySQL time.
2. The same could probably done storing the server time in the user session. I feel like this would probably work most of the time, but depending on the timing of the DB update and the requests, changes could be missed since the DB only stores a DateTime with a precision of 1 second.

I'm sure there's a more atomic way to do this, I am just drawing a blank though. What are suitable design patterns for this?

Answer 1

The solution O.Jones provided would work for making tracking updates atomic, though where it fails is if the following scenario occurs all within one second:

1. An order update is written to the table (update 1)
2. A poll action occurs
3. An order update is written to the table (update 2)

In this scenario, the next poll action will either miss update 2, or will duplicate update 1, depending on if you use > or >= in your query. This is not the fault of the code, it's a limitation of the MySql datetime type having only 1 second resolution. This could be somewhat mitigated with MySql v8 as it has Fractional Seconds Support though this still would not guarantee atomicity.

The solution I ended up using was creating a order_changelog table

CREATE TABLE 'NewTable' (
'id'  int NULL AUTO_INCREMENT ,
'order_id'  int NULL ,
'update_date'  datetime NULL ,
PRIMARY KEY ('id')
);

This table is updated any time a change to an order is made essentially numerating every update.

For the client side, the server stores the last ID from order_changelog that was sent in the session. Every time the client polls, I get all rows from order_changelog that have an ID greater than the ID stored in the session and join the orders to it.

$last_id = $_SESSION['last_update_id'];

$sql = "SELECT o.*, c.id as update_id
                FROM order_changelog c
                LEFT JOIN orders o ON c.order_id = o.id
                WHERE c.id > $last_id
                GROUP BY o.id
                ORDER BY order_date";

I now am guaranteed to have all the orders since last poll, with no duplicates, and I don't have to track individual clients.

Answer 2

You are correct that you must poll your database for changes, and that MySQL can't push changes to other applications.

The trick is to use server time throughout for your polling. Use a table to keep track of polling. For example, suppose your users have user_id values. Then make a poll table consisting of

 user_id  INT primary key
 polldate DATETIME 

Then, when you poll do this sequence.

First make sure your user has an entry in the poll table showing a long-ago polldate. (INSERT IGNORE doesn't overwrite any existing row in the table.)

 SET @userid := <<your user's id>>;
 INSERT IGNORE INTO poll (user_id, polldate) VALUES (@userid, '1970-01-01')

Then when you poll, do this sequence of operations.

Lock the poll row for the user:

 BEGIN TRANSACTION;
 SELECT polldate INTO @polldate
   FROM poll
  WHERE user_id = @userid 
    FOR UPDATE;

Retrieve the updated rows you need; those since the last update.

 SELECT t.whatever, t.whatelse
   FROM transaction_table t
   JOIN poll p ON t.user_id = p.user_id
  WHERE user_id = @userid
    AND t.last_update_date > p.polldate;

Update the poll table's polldate column

UPDATE poll p
   SET p.polldate = IFNULL(MAX(t.last_update_date), p.polldate)
  FROM transaction_table t
  JOIN poll_p ON t.user_id = p.user_id
  WHERE user_id = @userid
    AND t.last_update_date > p.polldate;

And commit the transaction.

 COMMIT;

Every time you use this sequence you'll get the items from your transaction table that have been updated since the preceding poll. If there are no items, the polldate won't change. And, it's all in server time.

You need the transaction in case some other client updates a transaction table row between your SELECT and your UPDATE queries.