3 ms·

Going one further, if you really do have to maintain doing writes, what you do is you modify application code to do simultaneous writes to both databases instea

Going one further, if you really do have to maintain doing writes, what you do is you modify application code to do simultaneous writes to both databases instead of one (i.e. "fork" your writes). This is where you are super glad you did the right thing (did you?) and your DB layer is abstracted and your SQL is standards compliant, and this saved you hours of headaches. Anyway, you maintain reads from the old db. Then, as the site is running, you migrate all data pre-fork, to the new database. Finally, after validating that you got it right, you flip the switch again to have reads come from the new DB. But you're not done yet. Keep the reads forking for a while, till you're sure everything went okay. If not, you can flip back to pre-migration instantly with zero data loss. Presumably, all this flipping between dbs is done through some kind of flag that can be modified at runtime, so you don't have to do more code deployments, because you're dancing with the devil here already. In any case, with proper capacity planning and good code, this is also doable, but not super required unless writes are mission critical for your customers 24/7. But the last thing you want to do is to flip the switch, pray, lose customer data.

ww520 14y ago
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.
- makmanalp 14y ago
  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. So, I guess, choose your poison :)

ww520 14y ago

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.

makmanalp 14y ago
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. So, I guess, choose your poison :)