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?
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!
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
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.
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
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.
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.
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:
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).
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)