Queueing MySQL record inserts to avoid over-subscription of a related resource … table locking?

Given a simplified hypothetical of seats in a lifeboat, if I have the following setup with a lifeboats table and a seats table where each record is one occupied seat in the given lifeboat:

CREATE TABLE lifeboats (   id INT UNSIGNED NOT NULL,   total_seats TINYINT UNSIGNED NOT NULL,   PRIMARY KEY (id));  INSERT INTO lifeboats (id, total_seats) VALUES (1, 3); INSERT INTO lifeboats (id, total_seats) VALUES (2, 5);  CREATE TABLE seats (   lifeboat_id INT UNSIGNED NOT NULL);  INSERT INTO seats (lifeboat_id) VALUES (1); INSERT INTO seats (lifeboat_id) VALUES (1); INSERT INTO seats (lifeboat_id) VALUES (1); INSERT INTO seats (lifeboat_id) VALUES (2); 

I can find lifeboats with available seats by querying:

SELECT      l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken FROM     lifeboats AS l         LEFT JOIN     seats AS s ON s.lifeboat_id = l.id GROUP BY l.id HAVING COUNT(s.lifeboat_id) < l.total_seats 

What is the best way to ensure 2 clients do not grab the last seat in a lifeboat without implementing some coordinating process queue?

My only idea (assuming I’m trying to grab seat in lifeboat 2) is going LOCK TABLE rambo like:

LOCK TABLE seats WRITE, lifeboats AS l READ, seats AS s READ;  INSERT INTO seats (lifeboat_id) SELECT      id FROM     (SELECT          l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken     FROM         lifeboats AS l     LEFT JOIN seats AS s ON s.lifeboat_id = l.id     WHERE l.id = 2     GROUP BY l.id     HAVING COUNT(s.lifeboat_id) < l.total_seats) AS still_available;  UNLOCK TABLES; 

but this is not very elegant, needless to say.

(My environment is MySQL8/InnoDB)