Say you have a Web app that uses data from both a database and an external API, and for certain pieces of functionality, you need to call in to both in order to read/write the necessary data.
Is it better to:
A) First start a DB transaction and then make all of the necessary calls to the DB BEFORE calling in to the API, and then roll the transaction back if the API call fails.
OR
B) Make the API call first and then call in to the DB upon success.
Pros and Cons of each option as I see them:
The big Pro with A is that data integrity remains intact, regardless of any failures. If the DB calls fail before I hit the API, then I return before making the call, and if the API call fails, I can roll back my DB transaction. If everything succeeds, then I commit the DB transaction and go on my merry way.
The big Con with A is that due to the transaction, the DB will be locked for an uncontrollable amount of time during the external API call, potentially affecting performance.
The Pros and Cons of B are essentially the opposite of the Ps and Cs for A. The Pro is that you avoid a DB lock during the API call. The Con is that if the API call succeeds but your DB calls fail, you essentially need to roll back the changes you just made thru the API, which may or may not be possible for any given context.
I'm partial to option A personally, as I'm a big fan of "better safe than sorry" approaches, but what do you all think?
Best Answer
Maybe consider a variation of two-phase commit: Make and commit your changes to the database first, then do your API calls. If the API calls fail, then make compensatory changes to the database (basically, update the database with the previous values). This had the virtue of not locking the database for any appreciable length of time, but gives you a method to keep your changes in sync across the DB and API.