Calling a web service in an atomic way?

Question

I have a MySQL database with product prices and from now on I want to synchronize another database with these values, but this other database is not in my server, and I can only update it through web services...

Is there a way to make these two operations (updating my database and calling the web service for the other database to be updated too) atomic? I don't want to find out one day that the second databse was down when the server tried to call the web service to update it, and so, it doesn't have the same values as my current database.

I'm using PHP, by the way.

Thank you very much.

Answer 1

Your answers to these questions may help in thinking about the best solution -

• Is the price updation a bulk process? I mean once in a week or something all the prices of your site are edited and bulk changes have to be done on the other server (where 2nd database is hosted)?

• Or is it like individual prices can be edited and the same change has to either take effect on both or not at all (in case the 2nd server was down)?

• What would you like to do in case the 2nd server was down, rollback changes on first server?

If the web service gives back a response - whether it successfully updated its product prices things are pretty easy. Here is one way of making it appear atomic -

• Have two fields - price_backup and lock_item in the table where all the item prices are stored.
• Before starting the updation process, set lock_item = true, so that the item cannot be purchased during that time (to prevent inconsistensies).
• Update price field on first database keeping the price in the price_backup field, then call web service to update and check for the response.
• If response is false, revert the price of the item with the price_backup value and reset lock_item, or else simply reset the lock.

This method ensures same values across the servers. However, there may be better methods