mysql way to make a lock in a php page

Question

Hello, i have the following mysql table:

myTable:

id int auto_increment

voucher int not null

id_user int null

I've populated voucher field with values from 1 to 100000 so i've got 100000 records; when a user clicks a button in a PHP page, i need to allocate a record for the user so i make something similar like:

update myTable set id_user=XXX where voucher=(SELECT * FROM (SELECT MIN(voucher) FROM myTable WHERE id_user is null) v);

The problem is that I don't use locks and i should use them because if two users click in the same moment i risk to assign the same voucher to different persons (2 updates in the same record so i lose 1 user) ...

I think there must be a correct way to do this, can you help me please? Thanks ! cris

Answer 1

If you truly want to serialize your process, you can grab a Lock Tables tablename Write at the start of your transaction, and Unlock Tables when done.

If you are using Innodb and transactions, you have to perform the Lock Tables after the start of the transaction.

I am not advocating this method, as there is usually a better way of handling, however if you need a quick and dirty solution, this will work with a minimal amount of code changes.