I recently interviewed for the position of Senior software engineer (SDE-3) at Aspora.

First round was Resume deep dive + HLD for 90 mins. Interviewer only spent five minutes on my intro and resume and started with the HLD question. The question was to design a checkout system for a flash sale.

Main scope of the problem was limited to selling only 1 item of limited quantity(upto 75k) and allowing users to buy on first come first serve basis. We could also assume that users have the amount in their wallets and there was no need for a separate payment flow during checkout. Also, to prevent scalping by malicious users and bots.

I explained the simple design of scaled servers behind a LB and a SQL DB for storing inventory and wallet balances. To scale to potentially 1-10M users attempting to buy, I placed a queue(Kafka or SQS FIFO) to absorb the throughput and process in order. I mentioned that we can scale to 50-100 consumers depending on benchmarks, and processing the messages by taking pessimistic lock as contention/concurrent queries are guaranteed. The interview caught on this aspect of optimistics vs pessimistic locking and questioned on queries waiting for locks with pessimistic locks. They wanted me to implement and use conditional writes like below:

BEGIN;

UPDATE inventory SET quantity = quantity - {buy_qty}
WHERE sale_id = {sale_id}
AND quantity >= {buy_qty};

UPDATE wallet SET balance = balance - {checkout_amount}
WHERE user_id = {user_id}
AND balance > {checkout_amount};

COMMIT:

instead of

BEGIN;

SELECT * FROM inventory WHERE sale_id = {sale_id} FOR UPDATE;

SELECT * FROM wallet WHERE user_id = {user_id} FOR UPDATE;

-- Validate qty and wallet balance in application

UPDATE inventory SET quantity = quantity - {buy_qty}
WHERE sale_id = {sale_id};

UPDATE wallet SET balance = balance - {checkout_amount}
WHERE user_id = {user_id};

COMMIT:

They were not satisfied with pessimistic locking approach and ended the interview.