Lock data in DB while external API call is pending?

Question

I have an interesting situation, and I am looking for the best way to solve it. My app runs like this:

1) The user gets a Promotion Pin from Some Company, and I have a db table with these pins also. The user submits my form to recieve a reward.

2) I first check if the pin is valid/non-expired using a basic stored procedure.

3) I contact a third party API to send them the reward.

4) I retire the pin using a basic stored procedure.

My issue is, how do I guarantee that the pin is not reused again while the API call is pending. I know this is a split second operation, but I would rather remove this possibility.

Answer 1

You can introduce an expiry column for your pins. Once you query the pin table to see if your pin is valid (and not expired), you update that expiry date to current time.

If your API call succeeds, No further action is required and you have a record of what has happened (you have only done one db write). If the API call fails, you need to go set the expiry date to Null so that records becomes available again.

Answer 2

If your PIN is valid only once, then why don't you write the stored procedure using DELETE INTO so that, given the proper isolation level, you know the PIN can be used exactly once. Then you query the temp table you inserted into to see if it has any values--if it does, the PIN is valid. That way you can eliminate step 4 entirely.