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?