0

We have Java and reactJs based application with SQL database. We have requirement to periodically reload ( truncate and load) tables from external system while application is running.

Thinking of cases where reload is in progress and application is reading from the sql table at the same time. How to best handle this ?

Trying to simulate the scenario but not able to reproduce.

Atul
  • 1
  • 1
    Instead of truncate/load you can load the data into a staging table that has the same schema. And once the load phase is succeeded then you can rename these two tables in a single transaction as a swap operation. So the staging table becomes the main one and the other one becomes the staging table. Once everything is fine you can truncate the staging table to clean up – ozgenbaris Aug 31 '23 at 08:53
  • Thanks for input. Just trying to understand during the swap operation do we normally need to pause/halt the application ? Just thinking if reading from main table and swap operation happen exactly at the same time and how to handle this situation. Do we need a lock to perform one operation at a time ( reading from main table and swap operation ) – Atul Sep 01 '23 at 06:10
  • Since you're trying to truncate/load this table I am assuming that your application is only reading from this table (not writing any data). So I am not really sure but I am guessing that if there is a select query that is already running on the table and your table is not optimized (missing indexes etc) for the submitted queries, then your rename operation should wait for the queries to end. But if your table gives response fast enough then this atomic operation can be completed in just a moment. – ozgenbaris Sep 01 '23 at 08:06

0 Answers0