2

I was asked this question in a interview. The problem statements is this

You have a website which handles load of millions of users. You have got a shared database across various load balancing web servers which has got details of coupons in a table. Every person who logs in will get a random coupon id from the table.

The question was how will you design your system to cater this many users?

In my opinion every time a user logs in, we will have to run a select query to fetch a coupon id and delete it from the table using a delete query to ensure that it does not get assigned to other user.

However I don't know how it will scale to millions of users? In my design until deletion of first user has not been done, I can't entertain any other user. That is why a lot of slave and a master database server won't help. Because deletion in master must be synced to slave servers for duplication not to occur.

What is my way around this problem to scale my backend? Also, if caching is supposed to be used here, what should be my approach?

Max
  • 9,100
  • 25
  • 72
  • 109

1 Answers1

2

The problem statement does not say which coupon to hand out to each user. I assume that any will do.

Your current design does not scale because it has a contention point: Taking the first coupon.

Instead, just take any coupon from the table. Add a random number column to the table storing the coupons. It's value range should be [0, 1000000).

When a coupon must be taken, take the coupon that has the smallest random number above a number that you have just drawn:

SELECT TOP 1 *
FROM Coupons
WHERE RandomNumber >= RAND(0, 1000000)
ORDER BY RandomNumber

This removes all contention because writes happen at random points in the table. Here's the query formulated as a write:

DELETE x FROM
(
 SELECT TOP 1 *
 FROM Coupons
 WHERE RandomNumber >= RAND(0, 1000000)
 ORDER BY RandomNumber
) x

This has the proper locking semantics ensuring atomicity.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Thanks for your answer. Can you give me an example to illustrate it further? – Max May 11 '14 at 10:43
  • I thought I had given an example(?). What piece do you not understand? – usr May 11 '14 at 11:54
  • I did not understand the random number part. Anyway, I was thinking about this problem. Can I do it like this, Store all of coupon id's in redis/memcache(which is heavy, so we are using the just the id's not the whole objects) which represents how many coupon ids are left. Now when the request comes, get one coupon id from the database using select query. After that we check, if the cache has that id, then we assign that id to the user and delete it from the cache,else we ask for another select query. Also, using somthing like cron, we can also delete the used coupon ids from the database. – Max May 11 '14 at 12:35
  • Your proposal is very racy. Multiple users might fetch the same id. Even if it was safe you also have the problem that you must somehow achieve that not every user tries to obtain the same coupon because that would be a contention point. The random number removes the contention because every user will (randomly) try to get a different coupon. Conflicts will be very rare. Makes sense? – usr May 11 '14 at 12:38
  • You are saying add a new column whose values are random values betwenn 0 and total number of coupons. Is this right? They you say, when the request comes, you run the query and get some id. But where and how you will write/update the database to ensure that some id does not get fetched again? " take the coupon that has the smallest random number above a number that you have just drawn", to accomplish "just drawn" part, there has to be some update query. And if that is the case, then with every select there is update/delete and problem is same is what I originally described.What I am missing? – Max May 11 '14 at 12:47
  • Good questions. You can either have a retry loop that first picks an ID and then tries to delete it and retries if the delete did not succeed. Or, you use a properly locked DELETE statement that does everything in one go. I added an example to my answer. If you don't understand it (which is likely), just go with the retry loop. – usr May 11 '14 at 13:09
  • Okay I don't want to extend the chat further, so one last question. Will the delete/select mechanism scale for millions of users. I mean, deletion will require the lock on table and in a cluster we need to sync the deletion to all the slaves. – Max May 11 '14 at 13:38
  • Deletion requires a row lock, not a table lock. You introduce clustering as a new requirement. Distributed databases are a whole new topic. A simple scheme would be to store each coupon only at one node. That way you don't have to synchronize or coordinate anything. – usr May 11 '14 at 14:06