mysql - PDO table locking on a table not specified? -

words hard today, , i'm trying write can me. overkill exists below, ymmv.

i'm migrating 48m records out of table1 table2, both innodb. i'm able identify records culled table1 via user_id ("any record entered user_id=3 moves out of table1 , table2"). i've got first pass @ working code in single instance setup need extend able run migration process via amqp server can run job via multiple consumers (ie i'm running consumers/jobs in parallel in 2 instances of same job @ same time) in round robin setup consumers running @ same time. i'd able scale 10ish consumers knock job out quickly, else 15 days single instance complete job.

the code selects oldest 2000 records match query criteria per run. problem if have 2 instances of code running , selecting records instance1 going grab id's 1-2000 , instance2 started @ same time going grab 1-2000 since instance1 hasn't finished moving 0-2000 out of table1 , id's still present in table1.

ok, no problem. add control table note records exclude - allows instance1 "i have records 1-2000" when instance2 fires , goes query says "what id's should exclude?" based on querying control table part of "give me next 2000 records" instance2 grab id's 2001 4001 , update control table let know instance2 has ids 2001 4001. if there instance3 know omit id's 1 4001 when fires asking next 2k records.

when instance writes entry locked control table writes "[first_id_found] , [last_id_found]", or "5671 , 7671". use ' , ' since text goes sql between clause.

select id table1 id not between 1565 , 1567 , id not between 1568 , 1570 , created_by=3 order id asc limit 0, 2000

the problem:

the table need lock on control table i'm assured once instance has access control table able both find records omit write own omit record next instance accesses control table once lock released find date data within.

but need leave lock in place until query of table1 finishes , can determine first , last id in result set can add these control table next instance fired know exclude.

note control file neither table1 nor table2, , don't specify table1 or table2 since don't need lock on either of those, nor want 1 because other processes accessing tables , don't want impede access other running processes table1 or table2.

so psuedo code looks this:

// lock control table // find control table records (multiple workers = possible multiple records) indicating id ranges omit // build "table1.id not between x , y" strings each control record found // query table1 "not between" array via pdo::fetchall() ids of n records no other instance working  <<-- problem // find first , last ids in result set // add control table record `params` field value = "[first_id_found] , [last_id_found]" // unlock control table // process array 

when query table1 no records returned. not correct, i've got 48m records. checking errorinfo() shows me error condition with:

"table 'table1' not locked lock tables"

i don't want lock table1, yet looks because been queried after lock tables has been put place on control , before unlock tables i'm in sort of transaction mode tables used have have lock level assigned. wtf, see nothing in docs.

why getting problem table1 when isn't within locking scope? i'm not married strategy, came mind, if there different pattern yield same functionality i'm game. workarounds? suggestions?