How to make sure a record is only retrieved once

Question

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.

Answer 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.

Answer 2

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.