Many-to-many how to avoid Race conditions?

Question

Database

There are 3 tables: Users, Subscriptions, UsersSubscriptions(join table). When adding a new entry to UsersSubscriptions, you need to check the number of user subscriptions, if it exceeds a certain value, then do not insert.
Pseudocode:

If(!usersSubscriptions.userCanSubscribe()) throw new Exception('Subscriptions exceeded!'); /** ( SELECT COUNT(*) FROM UsersSubscriptions WHERE userId = currentUserId ) < maxSubscriptions */
UsersSubscriptions.addRecord(); /** INSERT INTO UsersSubscriptions  (subscriptionId, userId)
VALUES (currentSubscriptionId, urrentUserId); */

Race Conditions: If thread #1 and thread #2 successfully passed the if condition (the first line of code), and the number of user subscriptions at that time was, say, 4 out of 5 possible, then both threads will execute UsersSubscriptions.addRecord() and count in the user's subscriptions will be more than expected. How can this be avoided?
PS: in NoSQL databases, for example, MongoDB, one could not do join table UsersSubscriptions, but simply place an array of subscription ids in the users table and place an array of user ids in the subscription table. Then a condition could be added to the update operation that the length of the array is less than maxSubscriptions, that is, something like

Users.updateOne( {_id: userId, $expr: { $lt: [ { $size: '$subscriptions'}, maxSubscriptions] } } }, updateObject );

And the same thing, only for Subscriptions. But with this approach, you will need to add transactions , since you need to modify more than 1 document.

Answer 1

How can this be avoided?

Everything has already been thought of before us. Years of commercials for 50.
Do in one transaction . And checking and adding a subscription.

Answer 2

I see two solutions - use queues or perform checks at the level of database triggers.

Answer 3

An adequate solution seems to be to create a layer that will process the requests of streams one after another, i.e. some kind of bottleneck that will not allow threads to work as threads. Then the race condition will be impossible.