Best way to implement concurrent table based queue

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.

As I explained in this article, MySQL 8 introduced support for both SKIP LOCKED and NO WAIT.

SKIP LOCKED is useful for implementing job queues (a.k.a batch queues) so that you can skip over locks that are already locked by other concurrent transactions.

NO WAIT is useful for avoiding waiting until a concurrent transaction releases the locks that we are also interested in locking. Without NO WAIT, we either have to wait until the locks are released (at commit or release time by the transaction that currently holds the locks) or the lock acquisition times out. Therefore, NO WAIT acts like a lock timeout with a value of 0.

For more details about SKIP LOCK and NO WAIT, check out this article.