Is there a way to prevent an object from beeing changed by two sessions simultaneously?

0
Case:  In my app it is possible for Customers to create appointments for certain timeslots. Each timeslot has a number of availableplaces. When i have only one place left on the timeslot and two customers try to create an appointment for this timeslot at the same time, the microflows for both cases retrieves a timeslot where there is still one place available, so both can subscribe, while there is only one available (this actually happened a couple of times on production) Things i've tried so far but didn't work: - Create an afterCommit on the appointment. This aftercommit checks how many appointments there are for the timeslot, if it is more than the max. then throw an exception. This exception causes the initial microflow to break and all changes are rollbacked including commits. I would expect that at least one aftercommit, would retireve more appointments for the timeslot than there are available. This stil does not seem to work. Both customers end up in the aftercommit at the same time. While retrieving appointments for the timeslot, there is still only one (the one the user himself created, but not the one the other created).   - I've thought about putting everything in a processqueue. Sothat they are handled one after the other. The problem is that the createAppointment functionality is an API call to another application. So if i put all the calls in processqueues. i canno't tell the customer emediately that the createAppointment was succesfull or not.    - It is also possible to set "Disallow Concurrent execution" on the microflow being triggered by the customer. The problem is that, this will also result in an error when two customers are subscribing to 2 different timeslots at the same time, which in my case should actually be possible.    So thats why i now ask this question in the title, but any other solution is welcome to! Thanks!
asked
6 answers
3

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.

answered
0

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!

answered
0

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...

answered
0

Hi Remi,

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.

Kind regards,

David

answered
0

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.

Regards,

Ronald

 

answered
0

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.

answered