Get unique row (at the moment)

Question

I have a high traffic PHP site (lots of concurrent visitors) that uses MySQL for persistence. The site can be used to purchase various gift cards.

The controller in PHP handles purchases like this:

Step 1

SELECT id, reference_number, card_number FROM cards WHERE category_id = 3 AND card_case = 'unused' ORDER BY id ASC LIMIT 1;

+---------+------------------+------------------+
| id      | reference_number | card_number      |
+---------+------------------+------------------+
| 3324791 | 11556267335      | 0638570501       |
+---------+------------------+------------------+

Step 2

INSERT INTO orders (date_created, card_id, price, client_id) values ('2021-09-08 06:40:55', 3324791, 110.00, 7);

Step 3

UPDATE cards SET card_case = 'used' WHERE id = 3324791;

The problem: When there are lots of visitors to the site, there can be multiple "simultaneous" purchases (at least, within the same second) for the same category of card. This means that different clients end up taking the same card. How can I change the query in Step 1 so that the row returned is unique, and not returned for other clients (so that there are no duplicate purchases for the same card id)?

Which concurrency mode are you using? – MaartenDev Sep 08 '21 at 18:09 — MaartenDev, Sep 08 '21 at 18:09

score 1 · Answer 1 · answered Sep 08 '21 at 18:23

In this case I would suggest to use transactions and locks - SELECT ... FOR UPDATE (and maybe SKIP LOCKED):

-- Begin transaction
START TRANSACTION;
-- Grab first non-locked unused card
SELECT * 
FROM cards 
WHERE category_id = 3 AND card_case = 'unused' 
ORDER BY id ASC 
LIMIT 1 
FOR UPDATE 
SKIP LOCKED;
-- Add the order
INSERT INTO orders 
  (date_created, card_id, price, client_id) 
values ('2021-09-08 06:40:55', 3324791, 110.00, 7);
-- Change card status so it won't get picked anymore
UPDATE cards SET card_case = 'used' WHERE id = 3324791;
-- Commit the changes
COMMIT;

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

You can also add UNIQUE constraint to orders.card_id column for additional protection and possibly use a foreign key constraint there to make sure data is consistent.

You can also consider removing card_case column and simply join the orders table on card_id = orders.id. The unused cards are the ones where order.id IS NULL.

In really high throughput systems, where you can't afford to lock the records, you may want to pre-reserve the cards before the order is actually confirmed and periodically release them to the pool if the payment was not made.

score 0 · Answer 2 · answered Sep 08 '21 at 18:05

For this query:

SELECT id, reference_number, card_number
FROM cards
WHERE category_id = 3 AND card_case = 'unused'
ORDER BY id ASC LIMIT 1;

You want an index on cards(category_id, card_case, id). Do note that an index will have a slight impact on insert performance and on updates that affect the indexed columns. However, the win on the SELECT probably balances all that out.

Get unique row (at the moment)

Step 1

Step 2

Step 3

2 Answers2