Syncing graph of data to other database (like data)

Question

Switching to new system and need to keep old data going for some time. So, we need to update old database with data when new system being updated.

It's a transactional data, like orders/shipments. Databases similar in principle but structures different. When change made to Order in new system - up to 10 tables need to be synced.

Here is my dilemma. How to go about transactions? Obviously, whole batch need to be wrapped into transaction. But it becomes pretty big chunk of code inside of transaction. I'm trying to minimize time "in transaction" and instead of just running "UPDATE" - I'm first checking (querying) destination if any of the fields was changed and then mark record for update. So, in the end, inside transaction we running updated on only records that only actually changed, not refreshing whole thing.

I'm just curious what you think about approach? We have much more code upfront (before transaction) but then actual transaction is super-light, instead of refreshing 20 rows in 10 tables - we only may refresh 1 table 1 row. Does this make sense?

Answer 1

I think batch processing is what you are looking for.

You can separate a big transaction into smaller batches, in order to avoid sending huge packets in the database and make the locks have smaller duration.

The tools you should use depend on the technology you are using, but batch processing is very common and every language has a related API.

If you are using Java, you can check this link to see an example of using JDBC batch API.

If want to use a framework, Spring is a framework providing a lot of functionalities (Batch API included). You can check this link to see some usages.