Concurrent db changes

Hello!

I need your help with an issue that we are facing. We’ve created an application that manages and generates orders for e-commerce stores. And we now have customers that generate multiple orders at the same second on the same product.

Our exact use case is that let’s say we have 1 product in stock and there are 2 users placing an order for that product at the same second. What happens is that we are currently processing both orders and we end up having -1 stock for that product.

We tried creating a list and always processing 1 order at a time (always the first item) and when we are done with it, we remove it from the list, so that the next one gets first. But somehow it doesn’t work and the process_order workflow is processing multiple orders at the same time.

Any good idea on how to solve this? Are we missing something here?

Thanks a lot!
Andrew

1 Like

This post and a search of the forum for “race condition” may provide some insight.

I would use a database that allows for transactions such as xano or Firebase/firestore.

There needs to be a way to verify that all parts of the workflow will pass before executing the db changes. Transactions do this for us.

I would use an “Optimistic Offline Lock”.

So grab the timestamp of the thing you are displaying. Then when you come to update it check to see if the timestamp has changed. If it has then display a “data has changed” alert.

This works best when there is a slim change of locking occuring (hence the optimism :slight_smile: ) but not when there is a massive race e.g. end of an auction.

4 Likes

Alright, thanks for the help! I ended up implementing a list where I push new orders and only process the last one every time so that I make the process iterative. I’m still due to test this solution in a large-scale environment and see whether the performance degrades too much. Thanks!

Why do you think that Bubble db doesn’t handle things transactionally? Seems like it does. :man_shrugging:

My understanding is that two users could buy the last item in a store. Maybe I’m misunderstanding atomic transactions but My understanding is that the transaction on a database would actually prohibit two users from buying the last item because it needs to check that it exists and then create the purchase simultaneously (in effect) instead of doing these as two different db actions separated by time.

So while two users should be able to throw the last item in their cart and click purchase only one should actually be charged and only 1 purchase should be recorded in the db. It’s my understanding that these types of errors can actually occur in Bubble where this process doesn’t work the way that it’s desired to work. In a fact, two users might get charged for the last single item even though only one could possibly receive one

3 Likes

Bubble doesn’t, unless I am missing something here, lock everything in a WORKFLOW.

Here “Workflow” = “Transaction” I think.

So (let’s ignore the order of operations here in workflows !!) … we have a three step workflow.

Step 1 - Update thing1 to have a status of “sold” (but only if it is not already sold)
Step2 - Create a new thing “sale”
Step3 - Update user to add “sale” to a list

In a trad SQL database, the “unit of work” here are all the steps 1-3. So you would ideally lock thing1 until Step 3 has finished.

I don’t beleive Bubble has the concept of “unit of work” in this way. In fact it would probably be quite happy to do Step 1 first no matter where it was in the Workflow. It just does database stuff as it comes up rather than try do a “transaction” at a time.

Bubble is not Consistent etc as in ACID.

Also, unlike some NoSQL databases, it will not be “eventually Consistent” either.

Yes. Effectively you would need to “lock” the item when it is put into a cart so only one person can buy it. But that is a poor way of doing it … when do you release the “lock”.

So you need to sort it out at purchase time. But what can happen (which is what you are saying I think) is that one Bubble database process can just not see something has been updated or created by another one (particularly if using searches) and both could be allowed to buy.

The only things stopping this happening is usually timing.

That said, this has been a problem with (distributed) databases since forever.

One can dream…

This has been a nagging problem for a number of years. I have a number of customers with popular Events that sell out in minutes and it always leads to issues

@NigelG the cart lock is effectively what most Ticketing systems do and then release it after X minutes if the transaction is not completed, but having even tried that in Bubble that first click to add to the cart (or ‘proceed with order button’ in my case) can lead to a race condition / inconsistency.

Happy to pay for a good solution here if someone can come up with something that consistently works with about 60 concurrent users going after the same 20 stock items

4 Likes

This may be a dumb question, but have you tried making the db write happen in an api workflow?

Regardless, love the idea of an explicit “transaction” in Bubble mentioned above.

Let’s say you have 10 people who reserve the same item in stock (the only one). Suppose they press Enter at the same time. Technically, 9 people will receive “no longer in stock” and only one will win the prize. The only way to succeed in my opinion is to use Add to List with a Unix timestamp (or similar precision). The first one who presses Enter automatically wins.

@nick.carroll can you elaborate?

Here’s another idea…create a table that has 3 fields - productNum (SKU), userId, timeStamp (to the millisecond). This table acts as a reservation for a later purchase, and the reservation is only good for x minutes (which you can derive by current_time minus timeStamp). Each time a user adds a product to the cart:

  • Check if item is available in inventory
  • If so, add row to table.
  • When user goes to purchase, then 1) Check how many items are available 2) Search table on how many other valid reservations are in line ahead of user. 3) If number of items available are greater than number of reservations ahead of user, that means there is a product available for that user at that time. You also need to check if user’s reservation is still valid. Otherwise, tell user item is no longer available for purchase.
  • Decrement inventory and purchase

Thanks @jared.gibb. That was a serious question and I see from some of the comment links that it apparently is a problem. Sort of surprising to me!

1 Like

Alright, so I’ve tested the solution with a list where I keep adding orders and only process a user’s order when it is the last item in the list (FIFO queue), after which I remove it from the queue, so that the next user can process it’s order. Works pretty well on small environments, but on large scale environments it breaks… when placing 10-20 orders at the same time for the last unit in stock, 1 passes, 9 receive a graceful reject and 10 get a timeout error. The biggest issue is performance degrading, it blocks our application eventually when this happens a lot, so it’s not scalable.

What we are considering now is:

  1. Send order ids to an external service (maybe Firebase service that receives orders, adds their timestamp and calls our Bubble API to process each order 1 by 1).
  2. Or the second option, after processing an order, if we get any negative stock value for any of the products in the order, we revert the whole transaction and display an error.

What is consistent, is that when multiple users purchase the last item in stock, we always get a “good” negative value (if 4 users place an order at the same time, and we have 1 item in stock, we always get -3 eventually)

Let me know what you think.