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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.