Activation code issuance, high concurrency how to resolve possible exceptions.

Now there is a system for releasing the activation Code, Php+mysql.
Code table:
ID (primary key self-growth), code (activation code content, such as 123ABC), status (1 is not issued, 0 representative has been issued).

Now there are many users (registered users, can get the user information) to smoke these activation codes, each person can only smoke 1 times a day, this time is sure to be able to draw.

Each time the user smokes an activation code, it finds an activation code record with a status of 1, resets the status of the line record to 0, adds a row of records in the record table (user ID and Activation code ID), and returns the activation code content to the user.

The logic is quite simple, and now I'm going to solve the problem is that there may be a problem with high concurrency:

For example, a lot of users at the same time the activation code, I select a row status of 1 of the activation Code record, may be multiple users select to the same activation code. Is there any way that when a request selects a row of records, the record is locked, and the other request does not get the activation code.

I hope that the great God help me to analyze this logic in the high concurrency of what might happen, what is the solution? Because the project I'm doing now actually encounters high concurrency, so I have to take it into account ...

----------Supplement----------
Thank you for the answer, I will refer to each one. In addition, it may not be clear that the activation code is generated well in advance, the quantity and content are fixed, inserted into the database in advance.

Reply content:

Now there is a system for releasing the activation Code, Php+mysql.
Code table:
ID (primary key self-growth), code (activation code content, such as 123ABC), status (1 is not issued, 0 representative has been issued).

Now there are many users (registered users, can get the user information) to smoke these activation codes, each person can only smoke 1 times a day, this time is sure to be able to draw.

Each time the user smokes an activation code, it finds an activation code record with a status of 1, resets the status of the line record to 0, adds a row of records in the record table (user ID and Activation code ID), and returns the activation code content to the user.

The logic is quite simple, and now I'm going to solve the problem is that there may be a problem with high concurrency:

For example, a lot of users at the same time the activation code, I select a row status of 1 of the activation Code record, may be multiple users select to the same activation code. Is there any way that when a request selects a row of records, the record is locked, and the other request does not get the activation code.

I hope that the great God help me to analyze this logic in the high concurrency of what might happen, what is the solution? Because the project I'm doing now actually encounters high concurrency, so I have to take it into account ...

----------Supplement----------
Thank you for the answer, I will refer to each one. In addition, it may not be clear that the activation code is generated well in advance, the quantity and content are fixed, inserted into the database in advance.

Redis is recommended, and the data will be dropped to the database on a regular basis.

All the APIs provided by Redis itself are atomic operations

1, select ID, code from XXX where status = 0 limit 1; Then update the time remember to bring the conditions, update XXX Set status = 1 WHERE id = #id # and status = 0 If the update fails, then select again, then update, so loop.

2, a way to use the queue, take a record at the same time to delete the record, Redis can be achieved.

Build a file inside the project,

1. First determine if the file is locked
2. If you lock, you wait, and you lock the file without being locked.
3. Read the database after you get the lock.
4. Unlock the file after you finish doing it

This only solves the problem of mutex, but does not solve the problem of high concurrency, it is recommended that the master try Redis queue

This requires the landlord to set the Code table storage Engine to InnoDB, and then search under the MySQL INNODB transaction isolation level.
Recommend a post
Http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode

It is generally not recommended to do this transaction isolation directly on the DB, which is generally handled 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;

Here the ID must be the primary key. Otherwise, it will cause the whole table to lock.
You can also use the cache as a queue in the backend logic to communicate the results asynchronously.

Refer to the InnoDB line lock on the upper floor. No business.
Determine the return value of the update set and know if any updates are successful if no record rows are affected, the update is invalid.

Normally,

@ cabbage is always the answer to the Chinese cabbage should meet the needs.

But I think carefully, the landlord's needs in the query should not know the primary key, SQL should be more like

SELECT * from XXX where status=1 limit 1;

So the high-concurrency transaction Select should all be the same row. All select to the next line only when the first transaction completes the update.
So the effect is actually equivalent to a table lock? Ha, I do not know the details, this question is more interesting, I decided to as an experiment listed in todo list.

Back to the landlord's question, I prefer to make a production line on the code level.

Except for the InnoDB in the upstairs. The row lock, from the database select an activation code can be done according to the user ID simple processing:
offset = user_id % 30;
SELECT * FROM table WHERE XXX LIMIT offset,1;

This way, the likelihood of taking the same activation code is lower, and the time to wait for the lock is reduced.

