locking - How to perform a MySQL row lock and delete rows using PHP? -

lamp stack - code php

i have innodb table - called queuetable

there cron job runs every 15 minutes queries db figure out if records need put queuetable. there no records added queuetable, , other times there may on 1,000 records added queuetable.

what needs happen every row in queuetable needs read, process needs performed, , row needs deleted.

currently, have 2 daemon jobs nothing query queuetable every 5 seconds.

one job reads odd row records, other reads row records. there 2 jobs in order processing done (two jobs better one).

i'd create 20 daemons go @ queuetable - cutting time row lives in queuetable minimum.

think of mail queue (it isn't example works).

i need empty queue possible.

i can transaction , lock row, doesn't prevent daemon trying lock same row. happen if daemon tries read locked row - wait it. daemon locked row going delete it. not sure second daemon @ point?

what best way accomplish i'm trying do?

i hope makes sense.

thanks!

btw - have tried pthreads - , found doesn't in terms of throughput. ie. process reads record , processes before get's next record - if in multi-threaded mode (ie each process new thread) time empty queue same doing each row 1 @ time. @ least in testing that's been case.

my mantra implementing queues in mysql: "don't queue it, it!"

multi-processing php not easy achieve. perl (also part of lamp) better. furthermore, since background task, keep web server , browser (apache + ie) out of picture; lot of overhead.

how long take process 1 "item"? if takes few seconds, begin; select 1 item update; process it; commit;

if takes longer that, not put begin...commit around everything; instead like:

set autocommit=1

1. $id = select id ... process_id null order ... limit 1; (or grab 10 , have inner loop of steps 2..5.)
2. update ... set process_id = $pid id = $id , process_id null; , check see if worked. note select , update deliberately not in transaction; minimize interference. however, without transactions, there modest chance thread grab it. if so, rows_affected 0; go step 1.
3. process item
4. update (or delete) ... set process_id = null id = $item; -- release lock
5. sleep(1) -- delay little -- avoid artificially swamping system. may need tuning.
6. go step 1.

you can have number of threads doing that.