Doing this reliably in the runtime, where you have concurrent sessions, concurrent microflows, concurrent transactions and possibly multiple concurrent runtimes is going to be very hard.
The best (and easiest) tool for this is a unique constraint on an entity attribute: this way you use the power of the database to validate that something is done only once, regardless of the number of transaction.
One way to implement this with a unique constraint is to have an entity with an attribute that is unique for every appointment, e.g. <date><time><appointment sequence numer per timeslot>, so something like: “20211209-09:00-01”.
I would create one entity containing all available appointment slots. This entity would contain an attribute with the unique slot identifier, Then I’d have one entity to contains all requested appointments. Users could select a timeslot from the first entity, and that would get saved into the second entity.
The requested appointments slot identifier needs a unique constraint. Now when 2 or more users try to save (commit) a requested appointment for the same slot identifier the database would accept the first requested appointment, but would throw an error on the second as that requested appointment object doesn’t have a unique slot identifier.
Having a separate entity for the available appointment also makes it easier to have a flexible number of slots per time. You can simply add more or less available appointsments at a specific moment into the entity.
A possible option could be to block a certain timeslot for x number of minutes for the person who selects the timeslot and decrease the availability of your timeslot for the other customers. I’ve seen this used in some apps where your selection of a seat in a theatre etc is reserved for x minutes and then released if the user does not go through with it.
Maybe it works, though I don’t know if it will have the same problem as you mentioned. But thought of sharing this, hope it helps!
This would work, when one sesson has a timeslot open already and another one is trying to open it. But not when they click on the button to open it within the same (mili?)second. This last situation seems to occur ocasionally on production...
Did you considder creating a LockObject? this could be an object attached to a new appointment, and offcourse to a users session with a cascading delete on that session (to clear the lock if the user closes the app).
If a user completes the flow, then you can just break the association with the session, and it will no longer be removed when the user’s session is ended
In you cancel flow, you could delete the lock and the attached appointment, to free the slot again for other users.
During the locking process adjust the amount of your timeslot usages, and adjust the number correctly in an after delete of the locking object (either leave the amount if the user reserved the slot, or lower it by 1 when the user cancelled, or the lockobject get removed with the user’s session).
I hope this brings you to a working solution for your problem.
Using the EndTransaction action from the community commens, might prevent the actions at the same time, as then the commit to database goes instantly and not at the end of the microflow.
There is stil a module with optimistic locking: https://marketplace.mendix.com/link/component/109405 Do note that it is in Mx7. But you could evaluate this and rebuild in later Mx versions.
Community Commons has locking mechanism already. See the image below. I have also posted an image of how I used it.
With this, you must be able to lock the object for the session.
When a user wants to edit the object, you can lock the object for the current session and when done you can release the lock.
With getLockOwner you must be able to know who has locked and compare with the current session too.
SE is also there which could release old locks.
Check if it is helpful.