How can I prevent two users from accessing MySQL table at the same time?

Question

Let's say I got a website which, when visited, shows what is your lucky word today. The problem is that every word can be lucky for only one person so you need to be fast visiting the website. Below is a sample table with lucky words:

+---------------------+
| lucky_word          |
+---------------------+
| cat                 |
| moon                |
| piano               |
| yellow              |
| money               |
+---------------------+

My question is: how can I prevent two (or more) users from accessing that table at one time. I assume that every user reads the first lucky_word from the existing table and the chosen word is deleted immediately so it won't be the next user's lucky word. For instance, I want to avoid cat to be shown to more than one visitor.

Should I solve this using an appropriate MySQL query or some lines in a PHP code or both?

Answer 1

You can use a locking read within a transaction; for example, using PDO:

$pdo = new PDO('mysql:charset=utf8;dbname='.DBNAME, USERNAME, PASSWORD);

$pdo->beginTransaction();

$word = $pdo->query('SELECT lucky_word FROM myTable LIMIT 1 FOR UPDATE')
            ->fetchColumn();

$pdo->prepare('DELETE FROM myTable WHERE lucky_word = ?')
    ->execute(array($word));

$pdo->commit();