Good morning, we have a very large company application in the form of ERP. We encountered a problem when several users used the same database entries at the same time. Namely, action orders are sent synchronously, which means that when two people click at the same time, e.g. “Subtract one (number)” and, for example, the entry in the database has the value 100, the return for both orders will be 99 because both of them see when the server asks value 100 because they execute at the same time. Is there any way to solve this?
Hey Dariusz,
Various solutions (which may or may not be applicable in your situation 
):
- Some kind of locking mechanism. When 1 user is editing something, another cannot do that (old school but as I mentioned the solution may or may not be applicable for your situation). It can however turn into a nightmare with locked objects that should not be locked anymore.
- Instead of directly modifying the number, use a changes table in which you record a new change document for every addition and substraction. When you display the amount, you need to take into account the original number + additions and subsctractions in the changes table.
In addition to solution 2 you can use a recursive workflow that processes the change documents with additions and substractions successively (in groups) and marks them complete. When you display the amount, you still need to take into account the original number, additions and subsctractions but you can exclude the completed ones. This might limit the WU usage.
I have used this 2nd solution for inventory management where there are orders, returns, reservations (which can be cancelled), inventory counts and other goods movements and it works quite well.
Generally, it is not about the mechanism itself when it comes to workflow.
Whatever the mechanism, when two or more people click at exactly the same moment, the entire chain of cause and effect takes place simultaneously.
My question was more about whether it is possible to somehow set the asymmetry of the tasks performed in bubble, in other words, queuing them.
I have a booking app with hundreds of users making bookings for 1 event (during peak periods). What’s work for me is making the booking WF an API WF. Then just schedule the action with as short interval.
To ensure a good user experience there’s an extra stage where the booking is confirmed/rejected depending on the capacity (first come first serve), which is reflected client side.
Works well so far even with a few hundred people rushing to book at the same time.
I understand, but it won’t work for us. We are talking about operations on raw material warehouse data, so I cannot add a safety feature in the form of confirmation of editing an entry, as half of the mechanism that edits entries takes place automatically on the backend, listening to fields in the database, and half of it takes place via a trigger in the user’s application.
Bubble’s DB is not transactional so you will have to implement some sort of pseudo transactional process natively. The methods I have seen people try to create one natively in Bubble always incur some sort of delay and/may result in conflicts during edge cases.
If your application requires strict transactional integrity, consider using an external database that supports transactions.
In that case kind of a task bucket with a recursive backend workflow which picks up a new task when the previous one is completed. But just like ihsanzainal84, I would not use that since it might give inaccurate numbers (because of delayed processing).
Using solution 2 I proposed in my previous post works very well in warehouse management / inventory management scenario’s where there are a lot of users and there is a goods movement, a financial booking and maybe also changes triggered in other modules in your ERP system. The number is not adjusted but calculated based on subsequent change documents (together with the right safety stock level and calculated predictions about future use based on the changes table you should be good 
).
You do not want to prevent concurrency, you want to make use of it. When you are trying to prevent it from happening, there is a good chance you are looking for the wrong solution.
Can You give me example of solution 2?
Sure, a simplified example (and I might be missing some obvious steps), let’s say you sell tables (consisting of a table top and 4 legs). and the stock is low. Based on the safety stock level a production order is generated in status planned to produce 10 tables.
Table material, fields name, (stock amount)
Table matDoc, fields material, movementType, amount, object 1, object 2
-
A production order is generated, status planned.
a) an entry is created in table matDoc, movementType reservation, material table top, object 1 storage location , object 2 planned order, amount 10
b) an entry is created in table matDoc, movementType reservation, material table legs, object 1 storage location, object 2 planned order, amount 40 -
Once production needs to start, the production order is set to the next status (and maybe a pick list is automatically created) the table tops and legs are picked up and a goods issue is created.
a) a) an entry is created in table matDoc, movementType goods issue, material table top, object 1 storage location , object 2 production order, amount 10
b) an entry is created in table matDoc, movementType goods issue, material table legs, object 1 storage location, object 2 production order, amount 40
After production is completed, the finished tables are put in stock, etc. The benefit of this approach is that you can see future demand, pipeline inventory, stock in transit etc. Step 1 only creates a reservation and the stock level is not decreased for table tops and legs. Step 2 does decrease the stock level for table tops and legs. You can also create separate document types for inventory adjustments like inventory audits, variation and others.
Because matDoc contains a lot of details, you probably want to keep a total stock amount in the material table (or a stock amount per storage location in another table) and periodically recalculate it based on the documents in matDoc. To get the current stock amount you only need to search for matDoc entries since recalculation. While the recalculation is running you can even set an indicator on the material that warns the user (although the recalculation should never run for long).
More or less the same logic you can use for financial bookings.
There is quite a WU cost to this approach but since you mentioned you use it for an internal ERP for your company (and not a small team building a SAAS MVP ) so that probably matters not that much. Another benefit is that things like material tracking (because of legal requirements), supplier evaluation are all possible because of the detailed registration this solution offers.
1 Like
It sounds great.
We are currently testing the dedicated version, so if we switch to it, there will be no problem with WU, and currently we are going to the legacy mode of the production version for a few more months, so WU does not apply to us either.
I just wonder if if I listen to the matDoc database, the problem I mentioned will still occur when several entries appear at once. In another case, I used the “Locked”/“Unlocked” column method and, for example, with 10 entries at the same time, 9 were executed correctly, but the first one was still wrong, as if between the first and the second the mechanism had not yet clicked and it only worked after the second entry.
Never ran into that issue using this solution, but it might be an option to ask Bubble about it. You just need to take the document type into account when recalculating the total stock (good receipt is plus, good issue is minus). Bubble has its limitations, but that is probably why you are going dedicated. You will probably also have options like load balancing / tuning / caching etc.
Maybe you can even emulate this situation by creating the tables with a couple of materials and some logic. The next thing would be to execute that logic while performing a CSV-upload in the matDoc table / bulk API or recursive workflows running and/or triggers firing or simply ask a number of users to perform some actions.
This topic was automatically closed after 70 days. New replies are no longer allowed.