Slashdot

my scary drug was coffee
I got by on many lukewarm cups of dodgy brew
now I watch the young baristas sigh
of boredom, while they serve the listless queue

... to be continued (unfortunately :)

You have multiple worker processes/threads pulling records, FIFO, from a table. If one worker 'dies' or fails processing, another worker should retry the same record.

Here are some ideas (partially tested).

mysql> describe q;
+-----------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------------------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| startwork | timestamp | NO | | 0000-00-00 00:00:00 | |
+-----------+---------------------+------+-----+---------------------+----------------+
2 rows in set (0.00 sec)

-- find the first eligible task record

mysql> begin; -- or use AUTOCOMMIT=0
mysql> select min(id) into @id from q where startwork=0 for update;
Query OK, 1 row affected (0.00 sec)

-- mark it as 'being worked on', using a starting timestamp.
-- later, any records that are not completed within a certain time
-- can be re-marked free (startwork = 0) by a regularly scheduled process.

mysql> update q set startwork=now() where id=@id;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select @id;
+------+
| @id |
+------+
| 8 |
+------+

-- this connection now 'owns' that task, and should delete it when done.

mysql> select * from tt order by seq;
+-----+--------+
| seq | amount |
+-----+--------+
| 16 | 1 |
| 17 | 31 |
| 18 | 131 |
| 19 | 50 |
+-----+--------+
4 rows in set (0.01 sec)

mysql> select not exists( select L.seq from tt L join tt R on (R.seq > L.seq and R.amount < L.amount) ) as Sorted;
+--------+
| Sorted |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)

mysql> delete from tt where seq=19;
Query OK, 1 row affected (0.00 sec)

mysql> select not exists( select L.seq from tt L join tt R on (R.seq > L.seq and R.amount < L.amount) ) as Sorted;
+--------+
| Sorted |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

mysql> select * from tt;
+-----+--------+
| seq | amount |
+-----+--------+
| 1 | 5 |
| 2 | 1 |
| 3 | 10 |
| 4 | 6 |
| 5 | 4 |
+-----+--------+
5 rows in set (0.01 sec)

mysql> select L.seq,L.amount,sum(R.amount) from tt L join tt R on R.seq<=L.seq group by L.seq;
+-----+--------+---------------+
| seq | amount | sum(R.amount) |
+-----+--------+---------------+
| 1 | 5 | 5 |
| 2 | 1 | 6 |
| 3 | 10 | 16 |
| 4 | 6 | 22 |
| 5 | 4 | 26 |
+-----+--------+---------------+
5 rows in set (0.00 sec)

mysql> select * from tt L join tt R on R.seq<=L.seq;
+-----+--------+------+--------+
| seq | amount | seq | amount |
+-----+--------+------+--------+
| 1 | 5 | 1 | 5 |
| 2 | 1 | 1 | 5 |
| 2 | 1 | 2 | 1 |
| 3 | 10 | 1 | 5 |
| 3 | 10 | 2 | 1 |
| 3 | 10 | 3 | 10 |
| 4 | 6 | 1 | 5 |
| 4 | 6 | 2 | 1 |
| 4 | 6 | 3 | 10 |
| 4 | 6 | 4 | 6 |
| 5 | 4 | 1 | 5 |
| 5 | 4 | 2 | 1 |
| 5 | 4 | 3 | 10 |
| 5 | 4 | 4 | 6 |
| 5 | 4 | 5 | 4 |
+-----+--------+------+--------+
15 rows in set (0.00 sec)

mysql> select * from player;
+----+-------+
| id | score |
+----+-------+
| 1 | 10 |
| 2 | 20 |
| 3 | 6 |
| 4 | 60 |
| 5 | 16 |
+----+-------+
5 rows in set (0.00 sec)

mysql> select L.id,L.score,count(R.id)+1 as rank
from player L left join player R on R.score > L.score
group by L.id order by rank;
+----+-------+------+
| id | score | rank |
+----+-------+------+
| 4 | 60 | 1 |
| 2 | 20 | 2 |
| 5 | 16 | 3 |
| 1 | 10 | 4 |
| 3 | 6 | 5 |
+----+-------+------+
5 rows in set (0.00 sec)

mysql> select L.id,L.score,count(R.id)+1 as rank from player L left join player R on (R.score,L.id)>(L.score,R.id) group by L.id order by rank;
+----+-------+------+
| id | score | rank |
+----+-------+------+
| 4 | 60 | 1 |
| 2 | 20 | 2 |
| 5 | 16 | 3 |
| 7 | 16 | 4 |
| 1 | 10 | 5 |
| 3 | 6 | 6 |
+----+-------+------+
6 rows in set (0.01 sec)