我有一个遗留API应用程序,它通过API调用特定日期获取大量JSON数据。
为了用新信息更新数据库,我必须删除与该日期相关的所有行,然后循环并插入所有新行。每个日期大约有1200行。插入需要作为一个"循环"来完成,因为JSON数据需要在每一行上完成额外的工作。
这种情况有规律地发生——大约每5分钟发生一次。
在此期间,有用户24x7定期访问当前数据。我有报告说,偶尔当他们加载报告时,没有数据,或者数据似乎不完整。
我有根据的猜测是他们在删除和"重新加载"数据之间访问报告。我怎样才能确保这一切顺利进行呢?
我需要知道的是DB事务会解决这个问题吗?我知道DB事务允许在操作过程中回滚,如果某些事情失败,但是它们也允许在一个事务内无缝删除和更新吗?
。像这样
try {
$db->beginTransaction();
$db->query('delete query');
$db->query('insert query');
$db->commit();
} catch (Exception $e) {
$db->rollback();
}
首先,您需要使用InnoDB。MyISAM不处理任何类型的事务,除了LOCK TABLE
,这会让你的用户感到混乱。
第二,确保你的表被正确索引,这样你的DELETE
查询就不会太慢——也就是说,不会做一个完整的表扫描。这可能意味着索引一些DATE或DATETIME字段。
第三,不要这样删除:
DELETE FROM table WHERE DATE(timestampcol) = '2014-01-01'
应该这样做:
DELETE FROM table WHERE timestampcol = '2014-01-01'
或者,如果您的timestampcol
同时包含日期和时间(也就是说,它包含非午夜时间),请确保您可以这样使用索引:
DELETE FROM table WHERE timestampcol >= '2014-01-01'
AND timestampcol < '2014-01-01' + INTERVAL 1 DAY
第五,对该操作使用事务。你的问题基本上是对的。
第六,是否有任何方法可以UPDATE
行而不是DELETE
和INSERT
行?您能在不破坏用户读取的数据完整性的情况下逐行完成这项工作吗?如果可以的话,这可能是一种"在用户眼皮底下"更改日期的方法,而不会让他们等待事务完成。
第七:这比较困难:您可能能够使用分区表,并在更新的当天交换分区。如果每个分区只有1200行,那么对于您正在做的事情来说,这可能是一个巨大的浪费。但是它将以编程和系统管理的麻烦为代价进行扩展。
一种方法是锁定数据库中的数据进程,例如mysql可以按行锁定。
事务模型和锁
另外,在mysql中,你可以编写存储过程来一次执行所有的插入和删除操作,并且在执行过程中其他函数不能访问数据。
我不认为数据库事务将解决这个问题。在您的例子中,由于您删除了所有数据:您可以通过执行"截断表"来加快删除语句的速度。截断表更快,因为它不保留日志。
请记住:Truncate将重置PK计数器与自动增量