0

If I have a table called Coupons that contains 10,000 records. How should I design the schema or what query should I use so that my API never gets the same record twice. If I simply ask the DB for last record I think it's possible another instance could ask the DB at the same time for that record.

Should I look into record locking ? Are there any performance implications with this? The table is going to be accessed a lot. Since there are 10,000 records it's ok if some records are locked since the DB can return a record that isn't locked.

Coupon table so far is just

id [int]
coupon [varchar]
project [int](fk to projects table)

Mysql 5.6

Edit: I am just serving coupons. As soon as I pull from DB it is considered used to me. I don't have an ID to look up either. I want to literally recreate that ticket machine at the DMV that puts people in order of who to be served. Once a number is pulled no one else will get that number. I'm concerned if code tries to SELECT last coupon simultaneously and gives out same number twice.

1
  • 1
    "Should I look into record locking ?" Lookup isolation levels and transactions. Feb 23 '18 at 21:33
1

I'm assuming this is the following case:

  • You can look up coupon multiple times (example: verifying the coupon exist)
  • Once you use the coupon something the coupon becomes invalid

Easiest way is to have two fields called isUsed int, ExpirationDate datetime Then create two APIs that do the following:

  • CheckCoupon (coupon id)

    This checks against the table for coupon id, ExpirationDate, isUsed. If the stored procedure returns 1, the coupon is valid, else invalid.

    Create a stored procedure called usp_check_coupon

    SELECT count(*) 
    FROM Coupon
    Where id = @id
    and ExpirationDate > [today's_date]
    and IsUsed = 0
    
  • UseCoupon (coupon id)

    Marks the coupon as used. Before updating IsUsed to 1, check to make sure IsUsed is 0.

    Create a stored procedure called usp_use_coupon

    DECLARE @isused int =
        SELECT count(*) 
        FROM Coupon 
        Where id = @id
        and ExpirationDate > [today's_date]
        and IsUsed = 0
    
    IF (@isused == 1)
    BEGIN
        UPDATE COUPON
        SET IsUsed = 1
        WHERE ID = @id
    
       SELECT 1 --Updated Successfully
    END
    
    SELECT 0 --Updated Failed
    

Then on the API, if usp_use_coupon returns 0, that means the coupon is used or expired. If 1, it has been processed.

2
  • I am not the system claiming coupons so I won't know when a coupon is claimed so to me the coupon is claimed right when I get it from the DB. I also am not looking up coupons, just serving.
    – Arkin
    Feb 23 '18 at 22:23
  • @Arkin It's obvious that your Coupon table misses that information IsUsed. Just set it inside a transaction whenever you hand one out. Feb 24 '18 at 11:35
1

After looking into Isolation levels and Transactions I've come up with the following solution. Currently untested but the theory should be correct.

My API (nodeJS) will execute the following transaction:

START TRANSACTION;
SELECT @1:=id as id, @2:=coupon as coupon FROM coupons WHERE isUsed = 0 LIMIT 1 FOR UPDATE;
UPDATE coupons SET isUsed = 1 WHERE id = @1 AND isUsed = 0;
COMMIT;

The idea is to WRITE LOCK the row and then update to isUsed because once I send a coupon it is considered used to me. Since another process could read the same row at the same time, it won't know about the update. But because we have locked the row only 1 process will be able to UPDATE with a result of 1 row affect. If the UPDATE query results in 0 rows affected or an error then the API will retry to get another coupon since it either tried to update a coupon already used or was blocked from writing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.