I have a SQL table that is filled with pending orders on day one. On the next day, at a specific time, I have to fetch the ‘PENDING’ orders from DB and process each of them by calling an external API. This is what my code flow looks like:
SELECT * FROM orders where status = 'PENDING' LIMIT 200
Now I will call the external API for each of those orders which will either return success or failure for each order and then I’ll update the order status in DB.
UPDATE orders SET status = 'COMPLETED' WHERE id = ANY(<success list>)
UPDATE orders SET status = 'FAILED' WHERE id = ANY(<failure list>)
The above flow will continue to run multiple times until the select query returns 0 rows. I’ve put a LIMIT in the query in order to avoid memory issues and the external API’s throughput capability.
Now there are a couple of issues with the above flow:
- Let’s say my code executed the SELECT query and started processing the orders. What if my service gets crashed in between? There will be some orders which would have gone through the API and would’ve received passed or failed responses. But I missed updating their status in DB, therefore when my service will start again, it will again pick those orders and process them again which I don’t want.
- My service can be running from multiple instances, therefore same orders whose status = ‘PENDING’ can be picked by various instances leading to double processing of the same order. How to avoid this?
If it helps, my tech stack is Go and PostgreSQL. I am sure the above are some common issues and there must be some standard ways to approach them. I am open to changing any part whether it’s the Go code or DB change which may include locks or transactions. I just want which direction to look for the solution. Any help would be appreciated.
3
Answers
First of all use the Pgx library for accessing postgresql. Then you need to use transactions and row locking. For performance you can use goroutine to do concurrent selection and updation.
Below given is a sample code for the same. Goroutines are not included in the code
Instead of
do
https://stackoverflow.com/a/11769059/965900
As you are calling an external API, which might be broken or timeout etc. you might want a more row-by-row approach (perhaps not fashionable but useful in some situations still). Assuming you want to use Postgres itself and not some external programming this might work for you:
Create a PL/pgSQL block to process the pending orders using a cursor:
The heavy lifting is inside the called function. This example places an explicit commit here (i.e. per row) this may should allow the possibility that some rows will work whilst others don’t and they can be picked-up/re-processed later. You could also include logic for an "in progress" status depending on your needs and how slow/flaky that API call actually is.
Alternatively, you could move the commit around the cursor instead if this level of paranoia isn’t needed. Note row levels commits will add time to the process (how much I cannot tell). It might also have an effect on things like rollback log size so that may affect your choice.