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

Question

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'?

How likely is this scenario? It's easy enough to grab the current subscriber count when you render the page, and then display an apology if the would-be subscriber is unsuccessful. For a low probability that might be good enough. Otherwise, you create an endpoint to update the subscriber list, and have your workstations subscribe to a Server-sent event that's updated by this new endpoint. Much more complex, but more real-time. I'm sure there are other ways. — Tangentially Perpendicular, Aug 02 '23 at 21:04
@TangentiallyPerpendicular great possibility, will keep that in mind. tnx — flow24, Aug 02 '23 at 21:12

score 0 · Accepted Answer · answered Aug 03 '23 at 05:07

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.

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

1 Answers1