How to handle queuing of "select for update" jobs in a mysql innodb table -

i have constant stream of 'select ...for update' queries on inndob table.

the main condition selects fields 'column1' < 100.

in background there constant inserts going on, can involve inserts 'column1' < 100, isn't problem. if first select update misses because happens whilst query being performed, or whilst results array being fetched, next 1 catch , happy mark down first query missing because 'too late'.

if have 10 of these 'select update' queries waiting, because of inndob field locking, should handling queuing of them myself or let database sort out? presume correct way handle queue queries myself?

so when script reaches

$sql = "select * ... udpate" 

beforehand, check queue array (?), if queue array not empty put script call in queue array @ end , check queue array every few milliseconds until reaches number 1 in queue?

am thinking along right lines here... important correct rather come later

edit: there can add increase likelihood of response :)

innodb should handle queueing you. if there's transaction in progress locks on relevant rows, second transaction trying acquire same locks select update statement, second statement wait until first transaction commits.

you can test out yourself:

1. open 2 bash shell windows, , run mysql client in each window.
2. run start transaction in each window.
3. issue select ... update in first window.
4. then same in second window, clause should overlap same rows.

5. in first window, run show engine innodb status , observe transactions , locks. should see output includes following:

   list of transactions each session: ---transaction 3b17, active 4 sec starting index read mysql tables in use 1, locked 1 lock wait 2 lock struct(s), heap size 376, 1 row lock(s) mysql thread id 2, os thread handle 0x7ff27ae2d700, query id 28 192.168.56.1 root sending data select * foo id < 100 update ------- trx has been waiting 4 sec lock granted: record locks space id 266 page no 3 n bits 72 index `primary` of table `test`.`foo` trx id 3b17 lock_mode x waiting ------------------ table lock table `test`.`foo` trx id 3b17 lock mode ix record locks space id 266 page no 3 n bits 72 index `primary` of table `test`.`foo` trx id 3b17 lock_mode x waiting ---transaction 3b16, active 70 sec 2 lock struct(s), heap size 376, 2 row lock(s) mysql thread id 1, os thread handle 0x7ff27ae6e700, query id 29 192.168.56.1 root show engine innodb status table lock table `test`.`foo` trx id 3b16 lock mode ix record locks space id 266 page no 3 n bits 72 index `primary` of table `test`.`foo` trx id 3b16 lock_mode x 

notice in above, transaction 3b16 holds locks, , transaction 3b17 waiting locks.

transaction 3b16 shows it's running "show engine innodb status" still holds locks acquired in previous select ... update. though statement finished, transaction not, , locks released when transaction finishes.

if transaction 3b17 waits longer lock_wait_timeout seconds, , first transaction still hasn't committed or rolled back, waiting statement gives , yields error:

error 1205 (hy000): lock wait timeout exceeded; try restarting transaction