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 i 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 Answered by Daniel Black in this comment.
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
