Fetch Unique Rows from Database in a Multi deployment Environment

Question

I have a table (codes) in relational database MySQL which stores a list of unique codes. The schema sample is given below.

Integer id 
String codes 
String user
Boolean is_available

id | codes | user | is_available
1  | ABC   |      | true
2  | XYZ   |      | true 

Whenever a code is assigned to a user user_id is updated and is_available is made false.

1 | ABC | user_id | false

There are multiple instances of a service running which fetch the code from database and gives it to the user. Each request must give a unique code.

GET /code -> returns a unique code 

As there are multiple instances what is the best way to handle the concurrency.

1. Is update and then select the correct way to do it ? Does it make the full table scan ?
2. Read optimistic locking , where we can set retries on a failed attempt but dont feel this is a good approach.
3. Setting isolation level to serializable is suggested but i dont think it should be used in production environment.
4. Having an centralised blocking queue which pops a unique code each time the request is made but it will be a single point of failure.

I have read a lot of theroy on this but I am looking for how it should be implemented on an enterprise scale application having thousands on concurrent calls.

Answer 1

Trying to answer "which is better" would be an opinion-based answer, because there's hardly ever a single right answer to architecture problems. They're always a tradeoff, so you have to decide which advantages are priorities for your project.

I'll try to address your specific questions, which are not opinion-based.

1. You can use a locking read to acquire an exclusive lock on a single row.

   SELECT * FROM codes WHERE is_available='true' ORDER BY id LIMIT 1 FOR UPDATE;
   

   Then update that row to claim it for a given user, and then return the code as the response.

   UPDATE codes SET user = ?, is_available = 'false' WHERE id = ?;
   

   Neither of these statements will do a table-scan. The SELECT will terminate when it gets the first row that satisfies the LIMIT, and the UPDATE applies only to one row which it looks up by its primary key.

2. Optimistic locking has no automatic retries. That's up to your application code. But ideally, you shouldn't need to do retries due to lock timeouts if your code is prompt about finishing transactions.

3. There's no need to set the isolation level to serializable globally even if you chose this strategy. You can use SET TRANSACTION immediately before starting a transaction, to change the isolation level once.

4. A centralized queue of unique codes would be an effective way to scale further, once your concurrent traffic grows so much that the locking SQL reads are a bottleneck. Any architecture need to be reevaluated periodically, because what worked at scale N may not be the best for scale 10 * N.

Answer 2

Have user_id NULLable, Then is_available can be eliminated and be replaced by user_id IS NULL.

Pseudo-code:

BEGIN;
$code <-  SELECT code FROM t WHERE user_id IS NULL LIMIT 1 FOR UPDATE;
UPDATE t SET user_id = '$user_id' WHERE code = '$code';
COMMIT;

It's atomic. It assigns some code to the given user_id. It allows a user to have multiple "codes".

If a user can have only one "code", I think the transaction can be simplified.