Indexes need to be added for what would be useful. This is not necessarily the "most commonly used" fields".
Your suggestion of
LOCK TABLES products WRITE
SELECT id FROM products WHERE status = 'available' LIMIT 3;
UPDATE products SET status = 'pending', timestamp = now()
WHERE id IN (the 3 rows in the previous query)
UNLOCK TABLES;
would need
INDEX (status) -- probably useless unless very few are 'available'
INDEX (id) -- certainly
There are multiple ways to get the list of 3 rows...
* Do it in your application layer (PHP?)
* CREATE TEMPORARY TABLE to put then in
* Combine the SELECT and UPDATE, something like this:
UPDATE products SET
status = 'pending',
timestamp = now(),
user_id = ...
WHERE status = 'available' LIMIT 3;
Note that I had to say who has them reserved. After that, you have to find what you grabbed:
INDEX(user_id) -- needed for this
SELECT id FROM products
WHERE user_id = ...;
That should return the (up to) 3 ids, if you need them.
If the user can move onto another 3 items, the above does not lay the groundwork for that.