Handle simultaneous booking (Database transaction)

Ask the community
,
1

Hi,

We want to do a transaction in our database. We have a booking calendar where if a user starts a booking on a certain machine we want it to be locked, so another user cannot “steal” the slot reserved for the machine.

So, the question is if there are some ways to do multiple actions in the database, like an SQL transaction ensuring no other action will happen in the meanwhile.

1 Like
2

Hi, and thanks for reaching out to the community!
Appfarm doesn’t use SQL, but if I understand your problem correctly, there could be easier ways to approach this.

One alternative is to check out the feature concurrency control on the object class. Based on this, you can decide wether the first or last write to the object wins, depending on what your need is.

Another approach could be to make use of a property that lets you control the object’s behaviour. If you have a boolean property on Machine (“is Locked”) that is set to true when a user starts a booking, then you can enable subscribe to updates on the data source you are showing to other users to make sure that the slot is unavailable to them in the UI. It is probably a good idea to secure it by having this property set to false whenever a booking is cancelled in the app.

I hope this helps, please let us know if you need further assistance.

2 Likes
3

Say I want to implement the “is locked” approach. It is still not clear to me
how to move forward. To implement this kind of approach, we would still need to
be able to make some sort of ACID transaction.

Here is an example to help make myself more clear:

Say I have a booking app where we can book tickets to an event. This event only
has 4 tickets available. For every user that books a ticket, I will create a
new ticket object that points to an event.

Tickets → Event

To make sure I don’t sell more tickets than slots, I will do a check on the
number of tickets that point to said event:

  • if (count(tickets where event == event) < event.NumberOfTickets){
  • create new ticket
    }

The problem, however, is that two users can hit the if at the same time, lets say the
number of tickets sold is 3. Both will pass the test and move on to create
a new ticket, and we will get 5 tickets for an event with only 4 spaces. So
what I would need is a way to make an ACID transaction [1]; where I am
guaranteed that both the if and the create will happen in sequence without any
other operation being done on the object

[1] ACID Properties In DBMS Explained | MongoDB | MongoDB

4

Hello!

This is a good request. MongoDB has supported multi-document ACID transactions for a few years, but we currently have no built-in concept for it. I just submitted a challenge on it now.

For your use case, with the ticket vs events, there are a few decent solutions to solve this using the current functionality in Appfarm.

OPTION 1:

  1. Use “Strict object versioning” on Event (or create another object class just for holding the number of tickets for an Event).
  2. Event has a “number of tickets sold”, and “number of seats”. As long as number of tickets sold < number of seats, the event is marked as “available” in the list.
  3. User opens an event, creates a Ticket in draft status, and clicks “confirm booking”.
  • Read the event into a runtime data source (as mentioned here)
  • Update the Event, set number of tickets sold = number of tickets sold + 1.
  • Persist Event.
    → If persist fails, the strict object versioning applies. You do not have the latest version of the Event. You may re-read the event to see if there are still available tickets (this retry logic can be made simple or complex, depending on the use case)
    → If persist succeeds, then the Event info you have has the correct version, and nobody has updated the number of tickets sold… Now, update that Ticket object to “booked”, and inform the user.

OPTION 2 (without using Strict Object Versioning):

  1. Create a new object class “Event slots” (or Ticket slots etc) having a reference to Event. If the Event has 10 seats, create 10 Event slots when the event is created (in other words, let the number of Event slots equal the number of available tickets).
  2. When the booking is confirmed: Read the Event slots for the event, sort randomly (Sort Object → Random Sort), select the first object, and do a delete objects of that record towards the database.
  3. Catch exception: If delete fails, then someone deleted that record just ahead of you. You may repeat step 2, or warn the user.
  4. If the delete objects is successful, there is now 1 less Event slot, and you may create or update the ticket (“confirmed”). And inform the user of success.

There might be other options to solve this as well.

Please also note: We do not recommend using Initially subscribe to updates as a mechanism for concurrency (it does not have 100% delivery guarantee and is not 100% “live”/instand)

1 Like