First: MySQL is one of the worst possible pieces of software to implement this, specially if it is very dynamic. The reason is that engines like MEMORY and MyISAM have only full-table locks while more suitable engines like InnoDB have a higher write penalty (to provide ACID properties) and are optimized for accessing records that are spatially and temporally close (those are set on memory). There is also not a good change notification system for MySQL- it has to be implemented as a polling. There are dozens of pieces of software more optimized for that task.
Having said that, I have seen successfully implement this kind of access if the performance/efficiency requirements are not very high. Many people cannot afford to introduce and maintain a complete separate piece of technology just for a small part of the business logic.
SELECT FOR UPDATE is what you are looking for- read serialization. While an UPDATE/DELETE will always lock the row during a running MYSQL transaction, you may want to avoid a large transaction while the process is going on, so:
START TRANSACTION;
SELECT * FROM your_table WHERE state != 'PROCESSING'
ORDER BY date_added ASC LIMIT 1 FOR UPDATE;
if (rows_selected = 0) { //finished processing the queue, abort}
else {
UPDATE your_table WHERE id = $row.id SET state = 'PROCESSING'
COMMIT;
// row is processed here, outside of the transaction, and it can take as much time as we want
// once we finish:
DELETE FROM your_table WHERE id = $row.id and state = 'PROCESSING' LIMIT 1;
}
MySQL will take care of locking all the concurrent selects except one when selecting rows. As this can lead to a lot of locked connections at the same time, keep the initial transaction as small as possible and try to process more that 1 row at a time.
