Database update for seat availability

#1

I have some needs that I don’t know if I can solve them directly with Wappler
A Query Insert form has a numerical select from 0 to 20 to choose how many seats you want to book
The form calculates and displays the availability when the page is opened, but it could of course vary during the purchase process

  • A first problem is this
    For example, if the availability is 14, I would like the range of the select options to change from 0 to 14
  • But the most important problem is this:
    Let’s assume that the user has made his choice (4 seats), the procedure first blocks availability by changing the available seats in the database and then completes the order.
    However, if at the time of purchase before making the payment by choice or due to an unforeseen event, I need to cancel the order after a certain time and update the availability again (adding what was previously removed
    In practice, since it is very likely that there will be several users connected in a given period, what you want to avoid is that availability is incorrectly updated and therefore excess orders are accepted.
    Has anyone had similar needs?
    Thank you all
Mutex lock/unlock step (to prevent race conditions)
#2

I didn’t reply earlier because I was waiting for someone with more experience than me to reply, but since no one replied :man_shrugging:

In the scientific literature, this is called a race condition:

A race condition or race hazard is the condition of an electronics, software, or other system where the system’s substantive behavior is dependent on the sequence or timing of other uncontrollable events. It becomes a bug when one or more of the possible behaviors is undesirable.

Certain database softwares like PostgreSQL have something called Transaction Isolation, you’d be looking at Serializable:

In order to prevent or reduce the probability of race conditions, you can’t rely on standard Wappler database queries. You need to read & update availability directly using custom SQL queries and perhaps using transactions like with the above transaction isolation level “serializable”.

I don’t have experience in this, I just have a general idea :slight_smile: