Hello, Unless I misunderstood the task, the exclusive lock would be one way to solve it. What you want to do, is have both parent and children start their activities with locking the table in exclusive mode and then performing their operations. The parent and children will then all serialize their operations and can do pretty much anything with the table in their own turn.
The exclusive lock is not, however, a requirement for a task like this. The same serialization could be achieved by using transactions for each operation and starting each operation with a write. The subtle requirement for that is disabling autocommit and of course using Innodb tables. For example, each parent could insert rows with the status of PENDING and each child would then do something like "update tblA set status = 'READ' where status = 'PENDING' limit 1". What this does, it picks the first unprocessed row and locks it. After the row is locked, the child can read whatever it wants from the row, knowing that all other children will lock on the same row and be suspended. The idea above could further be extended and enhanced depending on the requirement. The ordering by timestamp could be added, the parallel processing of records and so on. Peace, Karen On Feb 24, 2012, at 6:27 PM, bruce wrote: > hi. > > trying to get my head around how to create an exclusive read/write lock for > a tblA. tblA is updated by a parent process. > > the test system then has multiple children who on a 1st come basis attempt > to read the 1st "unread" row. > > > something like:: > > parentApp >>>> (writes/updates)>>> tblA > ^ > ^ > childA ---->>>>>---------------------------------^ > childB ---->>>>>---------------------------------^ > childC ---->>>>>---------------------------------^ > > so when childA reads, childB/childC need to wait, so they can't read the > same row as childA. childA updates the tblA row with a uniqueID so the > other tbls don't read the row, after childA releases the tblA... > > as i understand it, this should be doable using innodb, but none of the > articles i've seen describe how to accomplish this. > > the "shared mode" refers to the write process > > the exclusive mode might be useful, but i seem to be missing something.. > > thoughts on this would be useful. > > thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
