如何设计优惠券表及查询逻辑确保API不重复获取记录？

Hey there, let's work through your Coupons table problem step by step—this is a super common scenario with high-concurrency APIs, so I've got some practical solutions and breakdowns for you.

First, let's clarify why grabbing the "last record" directly causes duplicates:

When multiple API instances run something like SELECT * FROM Coupons ORDER BY id DESC LIMIT 1 at the same time, they'll all read the same "latest" record before any of them can mark it as used. That's a classic read-write race condition that leads to duplicate allocations.

Do you need record locks? #

Short answer: Yes, but you don't need to lock the whole table—row-level record locks are perfect here, especially since you mentioned partial locking is acceptable with only 10k records. That said, there are even more efficient lock + atomic operation patterns you can use to eliminate race conditions entirely.

Performance impacts of record locks #

Row-level locks are pretty lightweight, so the impact is minimal for your scenario:

• Pros: Only the specific coupon record being claimed gets locked. All other 9999+ records are still fully accessible for reads/writes—no widespread blocking.
• Potential gotchas:
  • If you split the process into "read first, lock second", you'll still have race conditions. You need to combine locking with an atomic update to make it safe.
  • In extreme high-concurrency spikes, you might see brief lock waits if multiple requests fight over the same last record. But with 10k records total, this bottleneck will clear up quickly as requests move to the next available coupon.

Practical Implementation Options #

Option 1: Atomic Update + Return (RECOMMENDED) #

Skip the separate read and lock steps entirely—use a single atomic SQL query that grabs and marks the coupon in one go. This eliminates race conditions by design:

UPDATE Coupons 
SET is_claimed = 1, claimed_at = NOW()
WHERE id = (
    SELECT id FROM Coupons 
    WHERE is_claimed = 0 
    ORDER BY id DESC 
    LIMIT 1
)
RETURNING *;

• The database handles locking automatically during the update, so no two requests can grab the same coupon.
• The RETURNING * clause sends the claimed coupon data right back to your API, so you don't need a second query.

Option 2: Pessimistic Row Lock (For Validation-First Workflows) #

If you need to check coupon details (like expiration date) before marking it as used, wrap a locked read and update in a single transaction:

-- Step 1: Lock the latest unclaimed coupon so no one else can grab it
SELECT * FROM Coupons 
WHERE is_claimed = 0 
ORDER BY id DESC 
LIMIT 1
FOR UPDATE;

-- Step 2: After validating, mark it as claimed
UPDATE Coupons 
SET is_claimed = 1, claimed_at = NOW()
WHERE id = [the ID you just fetched];

• Critical: Both steps must run in the same database transaction—otherwise, the lock will release right after the first query, and another request can sneak in.

Option 3: Optimistic Lock (For Ultra-High Concurrency) #

If you want to avoid locks entirely, add a version column to your Coupons table and use optimistic locking. This works well when conflicts are rare (which they'll be with 10k records):

-- Step 1: Grab the latest unclaimed coupon and its version number
SELECT id, version, ... FROM Coupons 
WHERE is_claimed = 0 
ORDER BY id DESC 
LIMIT 1;

-- Step 2: Try to update only if the version hasn't changed (meaning no one else touched it)
UPDATE Coupons 
SET is_claimed = 1, claimed_at = NOW(), version = version + 1
WHERE id = [fetched ID] AND version = [fetched version];

• If the update returns 0 affected rows, that means another instance grabbed the coupon first—just retry the query. No locks, no waits, just a tiny bit of retry logic.

Final Recommendation #

Go with Option 1 if you can—it's the simplest, most efficient, and completely eliminates race conditions with minimal performance overhead. If your business logic requires pre-validation, use Option 2 with proper transaction wrapping. Optimistic locking (Option 3) is a great fallback for extreme traffic, but adds a bit of code complexity.

内容的提问来源于stack exchange，提问作者Arkin