Firebase并发问题：如何避免两名用户获取相同游戏密钥？

Great question—this is a classic race condition that plagues inventory-style systems, and the fix boils down to using database-level atomic operations to ensure only one user can claim a key at a time. Let’s walk through the most reliable solutions tailored to your setup:

1. Use Atomic Delete + Return (Recommended) #

The cleanest way is to let your database handle the entire "grab and move" operation in a single atomic step. This eliminates any window where two transactions could read the same key. Most modern databases support syntax to delete a row and return its data in one go:

Example for PostgreSQL: #

BEGIN TRANSACTION;

-- Atomically delete the first available key and return its value
DELETE FROM NTWKeysLeft
WHERE id = (SELECT id FROM NTWKeysLeft ORDER BY id LIMIT 1)
RETURNING key_value;

-- Insert the returned key into NTWUsedKeys (adjust columns to match your schema)
INSERT INTO NTWUsedKeys (key_value, purchased_at, user_id)
VALUES ('[RETURNED_KEY_VALUE]', NOW(), '[CURRENT_USER_ID]');

COMMIT;

Example for SQL Server: #

DECLARE @ClaimedKey VARCHAR(20);

BEGIN TRANSACTION;

-- Delete the first key and capture its value
DELETE TOP(1) FROM NTWKeysLeft
OUTPUT DELETED.key_value INTO @ClaimedKey
ORDER BY id;

-- Insert into used keys table
INSERT INTO NTWUsedKeys (key_value, purchased_at, user_id)
VALUES (@ClaimedKey, GETDATE(), '[CURRENT_USER_ID]');

COMMIT;

Example for MySQL (8.0+): #

MySQL supports SELECT ... FOR UPDATE SKIP LOCKED to grab a locked row without waiting, perfect for high-concurrency scenarios:

BEGIN TRANSACTION;

-- Lock the first available key and select it
SELECT key_value INTO @ClaimedKey
FROM NTWKeysLeft
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Delete the key from the left table
DELETE FROM NTWKeysLeft WHERE key_value = @ClaimedKey;

-- Insert into used keys
INSERT INTO NTWUsedKeys (key_value, purchased_at, user_id)
VALUES (@ClaimedKey, NOW(), '[CURRENT_USER_ID]');

COMMIT;

Why this works: The database ensures the delete/select operation is atomic—no two transactions can execute this step and get the same key. Other transactions will either wait for the lock to release or skip locked rows (with SKIP LOCKED), depending on your setup.

2. Avoid "Read-Then-Write" in Your Application #

The #1 mistake that causes this race condition is splitting the operation into two separate steps in your code:

1. SELECT the first key from NTWKeysLeft
2. DELETE that key and add it to NTWUsedKeys

Even if you wrap this in an application-level transaction, two concurrent requests can still read the same key before either deletes it. Always push the entire "grab and remove" logic into the database to leverage its built-in concurrency controls.

3. Add Safeguards (Just in Case) #

Even with atomic operations, it’s smart to add a safety net:

• Add a unique constraint on key_value in NTWUsedKeys. If somehow a duplicate slips through, the database will throw an error instead of storing it—you can catch this in your code and retry the key-grabbing process.
• Test with concurrent traffic: Use tools like Apache Bench or JMeter to simulate 100+ simultaneous purchases and verify no duplicate keys are issued.

4. Distributed Systems Extra: Use a Distributed Lock (Optional) #

If your site runs on multiple app servers, you can add a distributed lock (e.g., using Redis) to ensure only one server attempts to grab a key at a time. But don’t rely on this alone—always pair it with database-level atomic operations, as distributed locks can have edge cases (like network timeouts causing lock leaks).

Any of these methods will eliminate the race condition. Start with the atomic delete/return approach—it’s the most reliable and doesn’t require extra infrastructure. Let me know if you need help adapting this to your specific database!

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