Hi Rick,
Thanks for you reply! Answers below.
* 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;
#####
##### Would the LOCK TABLES stop any selects etc from being run on that table while locked?
* Did you want an ORDER BY on the SELECT, or just get a random 3?
#####
##### The products can be any of the selected type, the products table will be a live stock table, for example there will be 1000 of the same products added so the 3 selected can be any 3 of the selected type.
* If you want a particular 3, do you have an appropriate index?
#####
##### I currently have no index, I am not familiar with using them but I think I need one in this case. Could you recommend which fields to set it up on?
* If you want a truely random 3, use ORDER BY rand() -- but that will seriously slow things down.
#####
##### Do not need this.
* WHERE id = ... probably needs to be WHERE id IN (...)
#####
##### Yeah, how can I pass the result from the first query into the WHERE IN (result of 1st query)?
* If you switch to InnoDB, look up "FOR UPDATE";
#####
##### Have just looked this up, this answers my previous question.
* MyISAM can probably do the suggested code sequentially in 1/100th second. So it might take 10 seconds. Is that good enough?
#####
##### Would it really take that long return 3 rows and update them? Maybe we have our wires crossed?
* I have trouble imagining an application with 1000 connections running the same query in the same second.
#####
##### If you can imagine a first come first served sale opening at 1pm on a certain day with a lot of people waiting for 1pm to come so they can buy at once before the products are gone?
* With InnoDB, you would need to respond to failures, especially deadlocks. This is very likely to occur when you have the described contention.
#####
##### Would lock, commit, unlock take care of this?
As requested:
CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`supplier_e` int(10) NOT NULL default '0',
`color_b` int(10) NOT NULL default '0',
`name` int(50) NOT NULL default '0',
`make_r` int(11) NOT NULL default '0',
`cost_type` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`user_updated` int(11) NOT NULL default '0',
`date_updated` datetime NOT NULL default '0000-00-00 00:00:00',
`user_added` int(11) NOT NULL default '0',
`date_added` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4001 DEFAULT CHARSET=latin1
"products" "MyISAM" "9" "Fixed" "4000" "50" "200000" "214748364799" "34816" "0" "4001" "2009-05-13 21:05:22" "2009-05-13 21:30:00" \N "latin1_swedish_ci" \N ""
SELECT id
FROM products
WHERE supplier_e = 1 &&
color_b = 1 &&
cost_type = 1 &&
status = 1
LIMIT 3
Hope my answers help you.
Martin