We have a CARD table that stores card number and user account number. Only a max of two users can share the same card. This constraint is not implemented as an SQL constraint on the table but is imposed in the code in the following way.
When a user tries to add a card -
- check whether the card number already exists in the DB. Done by querying the table with the card number.
- If no records, it means it is a new card. Add a record.
- If there are already records - if the no. of records is less than 2 - add a record in the table for this user. <same card number,this user account number> if it is equal to 2 - the limit is reached and we cannot add this card to this user. So, the code will throw an error.
Now the problem:
Assume that 100 users are trying to add the same card at the same time. This card did not exist in DB before. The following will happen. 100 requests(one request for each user) are processed in 100 threads. Each thread will see that the card number is not present in the table -> the no. of users who added this card is 0 which is less than the limit.i.e, 2 . So, each thread will try to insert a record into the table. In the end, we will have 100 users in the table sharing the same card.
user 1,card number - thread 1
user 2,same card number - thread 2
user 3, same card number - thread 3.
......
....
user 100, same card number - thread 100
We wanted only a max of 2 users to share the card but our shared card limit is broken now. How to tackle this scenario?
One obvious answer is synchronization of inserting the record in the DB. But,it leads to the following problem.
Assume 100 users are trying to add 100 different card numbers. If we synchronize insert operation, when one request tries to insert a record into the table, all other requests will be kept waiting->Response time for the API takes a heavy beating. But, in this case, there is no need to do it because the user,card Number combination is different for every request. So, that option is ruled out. What options are we left with now?
