15

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?

2
  • 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
    Commented Apr 28, 2015 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
    Commented Apr 28, 2015 at 19:44

4 Answers 4

Reset to default
12
  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.

6
  • Don't know why somebody downvoted. This is the right answer if the operation is mission critical and fault tolerance is required.
    – John Wu
    Commented Sep 10, 2021 at 22:33
  • What if the process crashes after api call is successful, and is not able to update db? How would you distinguish that from api call failed and the process fails un-gracefully / crashes ?
    – Jasper Wu
    Commented Aug 23, 2022 at 21:57
  • @JasperWu that's where the "offline" clenaup comes in. You can also keep track using a datetime or timestamp column (date when status was last changed) to determine how long a certain entry was in "processing" state and when it crosses a certain threshold, do the appropriate cleanup
    – georaldc
    Commented Dec 6, 2022 at 17:08
  • My concern is that, the successful API call already updates the remote data, yet the local cleanup may revert the data back to what it was before since it sees it's still "processing" after it crashes.
    – Jasper Wu
    Commented Dec 7, 2022 at 6:58
  • @JasperWu you will need some way to check the status of remote data before you revert the data back to initial state. Also, if the api is idempotent you can simply retry.
    – bornfree
    Commented Feb 24, 2023 at 7:00
4

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.

4
  • 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.
    Commented Apr 29, 2015 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
    Commented May 1, 2015 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!)
    – user1075613
    Commented May 14, 2021 at 18:50
  • @TMN What's the different between this approach and "API call first, then DB ?
    – Jasper Wu
    Commented Aug 23, 2022 at 22:00
1

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.
3
  • 5
    So how to deal with transaction keeping much more time open due external api calls?
    – Andre
    Commented Dec 3, 2019 at 14:22
  • What's the difference between this approach and "Do API call first, then start and commit the transaction" ?
    – Jasper Wu
    Commented Aug 23, 2022 at 22:01
  • (1) The API call succeeds. (2) The database update fails. Now, you've fired off that API call with /no way/ of calling it back. That's what the Transaction gives you; the ability to reliably "call back" the database update.
    – Phill W.
    Commented Aug 24, 2022 at 13:19
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.

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