How to lock row in db while reading?

Question

MySQL

Hello!
There is a table, for example, of generated promotional codes. When registering a user, I need to give him a free promotional code, and mark in the table of promotional codes that he is already taken by this user.
Problems arise when several users register at the same time - a situation is possible when they simultaneously receive the same promotional code, and the last user registers in the promotional code itself.
How to avoid this?
I see several solutions, but all of them are kind of clumsy: use redis with its incr which contains the id of the next free promotional code (they are auto-increment), you can make a queue with one worker to receive a free promotional code, you can generally create a separate fpm pool with a single php-fpm process (this is sadomaso of course) or limit the queue for a user registration request in nginx.
But surely there is some simple way?
Thanks in advance.

Answer 1

why generate them in advance at all?
You can make a little bit random generator during registration and the promo code is ready.
If you discard the options from the category promo_code_{user_id}
Well, or select select for update to lock the line
https://dev.mysql.com/doc/refman/5.7/en/innodb-loc...
but this is an option through the ass as a whole -then.

Answer 2

As an option, first mark the nearest free promotional code, and only then read it and give it to the user.

Answer 3

Maybe the Before Update trigger will do? in the promotional code, look for the presence of the user, if there is, then make the select again and issue another promotional code