3

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?

  • Do those calls have any sort of natural order...? A flow that makes sense? Any examples could be great too. I tend to prefer db-first, you needn't lock the db during API calls anyways, but that depends a lot on your environment and the transactions you need to do. – Fernando Cordeiro Apr 28 '15 at 5:01
  • The order of the calls is pretty irrelevant. I don't hate @TMN's solution, but it would require slightly cleverer code. I think regardless of whether you go the two-phase commit route, though, you still want the DB calls first. – EJay Apr 28 '15 at 19:44
2

It's far easier to roll back changes made in a database that it is at the "other end" of an API call.

Here's how I'd do it:

  • Start a database transaction and make the changes there.
  • Do the API calls.
  • If those work, then commit the database changes; if not, roll the changes back.
1

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.

  • I would suggest that most API calls are going to be far quicker than anything a database can do and committing the database changes "prematurely" may trigger (hint!) downstream activities that you can't "recall". – Phill W. Apr 29 '15 at 11:07
  • The locking of a DB during a transaction depends on the database vendor. For instance Oracle gives each user its own area to make changes and essentially does a merge when you commit. Transactions don't lock the database. – Greg Burghardt May 1 '15 at 0:47
0

From your statement " the DB will be locked for an uncontrollable amount of time during the external API call, potentially affecting performance" I infer that the external call could take a lot longer than the DB call.

So if this is the case, then I would make the request asynchronous and send a response to the user/UI saying that the request has been submitted. On the backend, an async call is made to the external API and when the API returns and depending on the response the DB call can be made.

If this a UI application, it can periodically refresh the data using an AJAX call or if the user has registered a listener, it can be notified of the completion of the request.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.