Getting the correct current value of limited item (System Design/MySQL)

We ran into an issue. Our company plans to provide a service where we would sell a package where up to 200 users from the client's company can be subscribed to at a time. We are using MySQL. The issue arises when a client either reached the limit/close to reaching it. Diagram:

MySQL's atomicity will prevent breaching the limit, but in instance where there are 198 subscribed, and let's say 50 more try to subscribe at once - how can we render in the UI a correct state of 'Click to subscribe'/'Subscription limit reached'?

You may use "attempt to reserve" technique.

Create a table which contains 2 columns:

CREATE TABLE subscription (
    subscription_ID INT,
    subscriber_ID INT,
    UNIQUE (subscription_ID, subscriber_ID)
    );

For each subscription you create a pack of rows whose amount is equal to the subscribers amount limit for this subscription. The value of subscriber ID is set to NULL.

When the use tries to subscribe he executes a query which tries to reserve one row with the query

UPDATE subscription
SET subscriber_ID = @current_user_id
WHERE subscription_ID = @needed_subscription_ID
  AND subscriber_ID IS NULL
LIMIT 1;

And then, immediately after this attempt, he checks does the attempt is successful with the query

SELECT EXISTS (
    SELECT NULL
    FROM subscription
    WHERE subscription_ID = @needed_subscription_ID
      AND subscriber_ID = @current_user_id
    ) AS subscription_success;

Possible variants:

• the reservation is successful (subscription was reserved, and nobody have re-reserved it) - both queries executed successfully, 1st query reports "1 rows affected", 2nd query returns TRUE
• reservation is not successful due to available slots absence - both queries executed successfully, 1st query reports "0 rows affected", 2nd query returns FALSE
• reservation is not successful due to another user interference - both queries executed successfully, 1st query reports "1 row affected", 2nd query returns FALSE
• "this subsription already exists" detected - 1st query fails with duplicate violation error, 2nd query returns TRUE
• some another problem - 2nd query or both queries fails, or 1st query reports "1 rows affected" but 2nd query returns FALSE

The user may check before this attempt does there are empty slots available for the reservation with the query

SELECT EXISTS (
    SELECT NULL
    FROM subscription
    WHERE subscription_ID = @needed_subscription_ID
    ) AS subscription_success;

When the subscription finishes then according reservation row is cleared with the query

UPDATE subscription
SET subscriber_ID = NULL
WHERE subscription_ID = @needed_subscription_ID
  AND subscriber_ID = @current_user_id;

This clearing may be performed by both the user and the event procedure.

When you need to alter the limit of subscriptions amount then you simply add or delete according rows for this subscription.

---

This method seems to be (is) interference-safe.