CODEWITHSUNDEEP
databaseload-balancingscalability
Sahil
Sahil

Reputation: 9486

Design of database/ backend to solve this high load demand scenario?

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?

Upvotes: 2

Views: 337

Answers (1)

usr
usr

Reputation: 171236

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.

Upvotes: 2

Related Questions