Mysql – How to make sure a record is only retrieved once

lockingMySQLmysql-5.6performancequery-performance

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.

Best Answer

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.

Related Question