How to assure equal update on 2 tables simultaneously on MySQL

Question

I have a problem that is impossible for me to resolve. I am trying to update 2 different tables with the same data but I always end up with discrepancy

I have 2 tables: users and stats

users structure:

id: int(25)
balance: decimal(18,6)

and stats structure:

date: date
userid: int(25)
balance: decimal(18,6)

I am using innodb transactions to update the tables like this:

    mysql_query("BEGIN TRANSACTION");

    $result1 = mysql_query("INSERT INTO users(id, balance)
        VALUES ".$balance." 
        ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance)");


    $result2 = mysql_query("INSERT INTO pubday (date, userid, balance) 
        VALUES ".$balance."
        ON DUPLICATE KEY UPDATE balance=balence+VALUES(balance)");



if($result1 === false || $result2 === false) {
mysql_query("ROLLBACK");
}else{
mysql_query("COMMIT");
}

I am updating the tables at least 5 million times per day. No matter what I always get a minuscule discrepancy between the balance from users and stats with this queries:

SELECT sum(balance) from stats

and

SELECT balance from users

For example I get balance=302.001731 on stats and balance= 302.194501 on users. It should be the same.

My question is what is the error here or what I am doing wrong? Or what is the best way to approach this issue? to update 2 tables simultaneously with the same data.

Answer 1

The answer is simple: don't have multiple points of authority. If the two columns should be the same, make them the same column. I would do this be removing stats.balance, and calculating the stats when you need it on the fly through SQL.

After all, ad-hoc queries are one of the core reasons Relational Database Management Systems (RDBMS) were initially researched in the 1970s.

Meanwhile, it looks like you'd like some sort of "Materialized View" so that you can show data from the past. If that's case, consider saving each transaction for a time, and then inserting into the stats table at regular intervals based on SQL calculations. Something like:

INSERT INTO stats (date, userid, balance)
  SELECT CURDATE(), id, SUM(balance) FROM users GROUP BY 1, 2;

And possibly, depending on your needs, utilize an UPSERT via ON DUPLICATE KEY UPDATE.

Obviously, SUM(balance) is not complete or correct, but the idea of summation is the point, after you update your schema.