0

I am developing a reservation system using Flutter, Hasura graphql, and Postgres DB.

Suppose my database has two main tables, "Operation" and "Ticket"

"Operation" : "id", "operation_date", "seat_available".

"Ticket" : "id", "seat_no", "operation_id", "user_id".

In the table "Operation" I have a column named "seat_available" which is a string array that contains an available seats (e.g., ["1", "2", "3", "4", "5"])

When some user is reserving seats, it created a record in a table "Tickets" with "operation_id" and has not assigned any seat number yet.

I created an Event Trigger in Hasura to trigger "insert" in table "Tickets" and I used netlify as a webhook. A netlify function is used to check the "seat_available" in the table "Operation" with specific "operation_id" in ticket and assigns a seat number for that reservation ticket.

The problem is when multiple users are reserving a seat at the same time. They got the same "seat_available" data. That makes a reservation conflict. For example:

  • User_A requests to reserve the tickets. at that time "seat_available" is "["1", "2", "3", "4", "5"]" and they get the seat 1. so the operation is updated to "["1A", "2", "3", "4", "5"]
  • After that User_B request to reserve the ticket, at this time the User_B get the same "seat_available" data as the User_A get which is "["1", "2", "3", "4", "5"]". It should be "["1A", "2", "3", "4", "5"] that updated from User_A.

This makes User_A and User_B got the same reservation seat, which is "1A". How can I solve this problem?

Alpha9
  • 101
  • 7

1 Answers1

0

This could be done by adding a where condition on seat_available during update. For example, you could do something like this

UPDATE operation SET seat_available = {"1A", "2", "3", "4", "5"} where id = "<operation_id>" and seat_available = {"1", "2", "3", "4", "5"} 
RETURNING *

If the query returns a row, then the update happened, and you can proceed. Otherwise you should repeat the process of getting the latest seat_available and assigning the seat.

In your example case User_A query will return a row. The User_B query will not return, since User_A query would have already updated the seat_available, and the where condition for User_B on seat_available will be false.

Nizar M
  • 361
  • 2
  • 5