How to solve possible exceptions in high concurrency when the activation code is issued.

Now there is a system that issues the activation code, php + mysql. Code table: id (primary key auto-increment), code (activation code content, such as 123abc), status (1 indicates not issued, 0 indicates already issued ). Currently, many users (registered users can get user information) smoke these activation codes. Every... Now there is a system that issues the activation code, php + mysql.
Code table:
Id (primary key auto-increment), code (activation code content, such as 123abc), status (1 indicates not issued, 0 indicates that it has been issued ).

Currently, many users (registered users can get user information) smoke these activation codes, each person can only smoke once a day, this time is certainly can be drawn.

Each time a user draws an activation code, the user finds an activation code record whose status is 1, sets the status of this record to 0, and adds a record (user id and activation code id) to the record table ), then, return the activation code to the user.

The logic is quite simple. The problem I want to solve now is that there may be problems in high concurrency:

For example, many users pull the activation code at the same time. When I select an activation code record whose status is 1, multiple users may select the same activation code. Is there any way to lock this line of record when a select line record is requested, and the activation code will not be obtained for other requests.

I hope you can help me analyze what problems may occur in my logic with high concurrency, and what solutions are there? Because my current project will actually encounter high concurrency, you must consider it...

---------- Supplement ----------
Thank you for your answers. I will refer them one by one. In addition, it may not be clear that the activation code is generated in advance. The quantity and content are fixed and inserted into the database in advance.

Reply content:

Now there is a system that issues the activation code, php + mysql.
Code table:
Id (primary key auto-increment), code (activation code content, such as 123abc), status (1 indicates not issued, 0 indicates that it has been issued ).

Currently, many users (registered users can get user information) smoke these activation codes, each person can only smoke once a day, this time is certainly can be drawn.

Each time a user draws an activation code, the user finds an activation code record whose status is 1, sets the status of this record to 0, and adds a record (user id and activation code id) to the record table ), then, return the activation code to the user.

The logic is quite simple. The problem I want to solve now is that there may be problems in high concurrency:

For example, many users pull the activation code at the same time. When I select an activation code record whose status is 1, multiple users may select the same activation code. Is there any way to lock this line of record when a select line record is requested, and the activation code will not be obtained for other requests.

I hope you can help me analyze what problems may occur in my logic with high concurrency, and what solutions are there? Because my current project will actually encounter high concurrency, you must consider it...

---------- Supplement ----------
Thank you for your answers. I will refer them one by one. In addition, it may not be clear that the activation code is generated in advance. The quantity and content are fixed and inserted into the database in advance.

We recommend that you use apsaradb for redis, which is fast. You can migrate data to the database regularly every day.

All APIs provided by Redis are atomic operations.

1. select id, code from XXX where status = 0 limit 1; then remember to include the condition when updating, update XXX set status = 1 where id = # id # and status = 0; if update fails, select again and then update.

2. One way is to use a queue. If a record is obtained, the record is deleted. redis can achieve this.

Create a file in the project,

1. First, determine whether the file is locked.
2. Lock the file and wait until it is locked.
3. Read the database after obtaining the lock
4. Unlock the file after the operation is completed

This only solves the problem of mutual exclusion, but does not solve the problem of high concurrency. It is recommended that the subject try redis queue

You need to set the storage engine of the code table to Innodb, and then search for the mysql innodb Transaction isolation level.
Recommend an article
Http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode

Generally, we do not recommend that you directly perform this transaction isolation processing on the database, which is generally processed in the application.

InnoDB row lock + transaction,

Start Transaction; Select * From `xxx` Where `id` = 123456 for Update;Update `xxx` Set status = 0 Where `id` = 123456;Commit;

The id must be the primary key. Otherwise, the entire table will be locked.
You can also use the cache in the backend logic as a queue to asynchronously convey the results.

Refer to the InnoDB row locks on the upstairs. You don't need to use transactions.
Judge the return value of the update set to check whether the update is successful. If no record row is affected, the update is invalid.

Normally

@ Is always the answer to Chinese cabbage.

However, after careful consideration, the landlord's requirements should not know the primary key during the query, and the SQL statement should be more like

Select * from xxx where status = 1 limit 1.

Therefore, the select statements for highly concurrent transactions should be the same row. Only when the first Transaction Completes update will all select to the next row.
So the effect is actually equivalent to the table lock? Haha, I don't know much about the details. This is an interesting question. I decided to use it as an experiment in the todo list.

Back to the landlord's question, I prefer to create a production and consumption queue at the code level.

Apart from the InnoDB row locks mentioned above, you can simply select an activation code from the database based on the user ID:
offset = user_id % 30;
SELECT * FROM table WHERE XXX LIMIT offset,1;

In this way, the possibility of concurrently obtaining the same activation code is lower, reducing the waiting time for the lock.