Как обеспечить одинаковое обновление на 2 таблицы одновременно на MySQL
У меня есть проблема, которую я не могу решить. Я пытаюсь обновить 2 разные таблицы с одинаковыми данными, но всегда получаю несоответствие
У меня есть 2 таблицы: users а также stats
users состав:
id: int(25)
balance: decimal(18,6)
а также stats состав:
date: date
userid: int(25)
balance: decimal(18,6)
Я использую транзакции innodb для обновления таблиц следующим образом:
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");
}
Я обновляю таблицы не менее 5 миллионов раз в день. Независимо от того, что я всегда получаю незначительное расхождение между балансом от users а также stats с помощью этих запросов:
SELECT sum(balance) from stats
а также
SELECT balance from users
Например, я получаю баланс = 302,001731 по статистике и баланс = 302,19010 по пользователям. Это должно быть то же самое.
У меня вопрос в чем здесь ошибка или что я делаю не так? Или как лучше подойти к этому вопросу? обновить 2 таблицы одновременно с одинаковыми данными.
Решение
Ответ прост: не иметь нескольких точек авторитета. Если два столбца должны быть одинаковыми, сделать их в одном столбце. Я бы сделал это удаление stats.balanceи вычисление статистики, когда вам это нужно, на лету через SQL.
В конце концов, специальные запросы являются одной из основных причин, по которым системы управления реляционными базами данных (RDBMS) были первоначально исследованы в 1970-х годах.
Между тем, похоже, что вам нужно какое-то «материализованное представление», чтобы вы могли отображать данные из прошлого. Если это так, рассмотрите возможность сохранения каждый транзакции на некоторое время, а затем вставлять в таблицу статистики через равные промежутки времени на основе расчетов SQL. Что-то вроде:
INSERT INTO stats (date, userid, balance)
SELECT CURDATE(), id, SUM(balance) FROM users GROUP BY 1, 2;
И, возможно, в зависимости от ваших потребностей, использовать UPSERT через ON DUPLICATE KEY UPDATE,
Очевидно, что SUM(balance) не является полной или правильной, но идея суммирования — это точка после обновления вашей схемы.
Другие решения
Других решений пока нет …