Doing simultaneous writes to two databases at the application level is very fragile and have many points of failure, all the places involving write in the application code. Also distributed transaction is probably not in place since it's an one-off thing, and there are risks of inconsistent updates to both database where one write goes through and the other one fails. Recovery from that would be nasty since failures can be on either way.
A better approach would architect to write all the updates to a persistent message query. Then a updater can read the update messages from the query and apply them to one or both database. The updates are welled ordered with respect to time and to both databases. The potential failure scope is limited to one place and it's easier to go through the recovery cases.
Persistent queue has other benefits in scalability and fast apparent saving to the users.
Sort of. It's a tradeoff. First off, I was suggesting doing the dual writes in the code that does the write in your DB layer, not by doing fixes all over the application code. That reduces the code changes by a ton.
Second, I guess it's true that there are some consistency issues since you have to commit one transaction only if the other succeeds. But this is way smaller a risk, I think, than yours.
With your method you're writing way more code just for the update and adding a whole new layer (both failure modes in their own right), and then just moving the transaction inconsistency problem to the updater instead (when you choose to write to both databases). The benefit is that you can "replay" the updates from the updater, but the truth is not all changes are idempotent so replaying some queries may fuck things up without storing DB state (e.g. inserts, increments) which is a mess in itself.
A better approach would architect to write all the updates to a persistent message query. Then a updater can read the update messages from the query and apply them to one or both database. The updates are welled ordered with respect to time and to both databases. The potential failure scope is limited to one place and it's easier to go through the recovery cases.
Persistent queue has other benefits in scalability and fast apparent saving to the users.