How to insert only the number of data specified by mariadb?
We are making a seat reservation system. Only 100 people can enter the theater. We have developed a web application so that you can make a reservation if there are less than 100 people who have made a reservation. However, too many people have applied for reservations, so more than 100 people have been booked. I think it is difficult to solve the concurrency problem just by modifying the application because the web application runs on multiple machines. So, using Mariadb, can you create conditions so that only the maximum number of people can be booked at a specific theater? Here is table scheme.
Theater (id, description) Moviegoer (id, name) Reservation (id, theater_id, moviegoer_id)
If there are reservations of 100 moviegoers in theater 1, I want to fail this query in maria DB.
INSERT INTO Reservation (theater_id, moviegoer_id) VALUES (1, 101);
I'm sorry, but i'm not good at English. I'll wait your question.
Answer
To your `Theater (id, description)` table add a field of `remaining int default 100`.
Use transactions when making the reservation:
BEGIN UPDATE Theater SET remaining=remaining-1 WHERE id=1 AND remaining>1 (in application code, examine ROWS_AFFECTED from this update and if not 1, ROLLBACK + no seats available INSERT INTO Reservation ... COMMIT
