Multiple problems...
* MyISAM ignores BEGIN/COMMIT. The alternative is
LOCK TABLES WRITE
SELECT id FROM products WHERE status = 'available' LIMIT 3;
UPDATE products SET status = 'pending', timestamp = now()
WHERE id = #the 3 rows in the previous query#
UNLOCK TABLES;
* Did you want an ORDER BY on the SELECT, or just get a random 3?
* If you want a particular 3, do you have an appropriate index?
* If you want a truely random 3, use ORDER BY rand() -- but that will seriously slow things down.
* WHERE id = ... probably needs to be WHERE id IN (...)
* If you switch to InnoDB, look up "FOR UPDATE";
* MyISAM can probably do the suggested code sequentially in 1/100th second. So it might take 10 seconds. Is that good enough?
* I have trouble imagining an application with 1000 connections running the same query in the same second.
* With InnoDB, you would need to respond to failures, especially deadlocks. This is very likely to occur when you have the described contention.
Please provide
* SHOW CREATE TABLE tbl\G -- I want to check the indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- table size could be an issue
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]