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.


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. 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

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. May 1 '15 at 0:47
  • compensatory changes might be complex to implement and can fail to be applied. And as Greg says, AFAIK transactions are not locking anything on DB (unless you don't use a connection pool but then you have bigger problems!) May 14 at 18:50

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.
  • 2
    So how to deal with transaction keeping much more time open due external api calls?
    – Andre
    Dec 3 '19 at 14:22
  1. Begin db transaction
  2. Update the db, setting a status or a flag that means something like "processing".
  3. Commit transaction
  4. Perform the API call.
  5. If the API call succeed, the process still in control, begin transaction, do your db updates, also update the status/flag to "done", commit transaction. All good.
  6. If the API call fails, or the process fails non-gracefully, the situation remains "dirty", but you have an indicator (status) that something has failed (status value is "processing"), so you can perform some "offline" (scheduled, regular) cleanup, as per your context. If more complicated, you involve in help "semaphors" (applock in db, the one that cleans up automatically on db connection drop) to control concurrency.

This should be done after each API call involving updates. Therefore, make some generic code there and use it for every API call; address specificities additionally for each API where needed.

  • Don't know why somebody downvoted. This is the right answer if the operation is mission critical and fault tolerance is required.
    – John Wu
    Sep 10 at 22:33

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.

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