List:General Discussion« Previous MessageNext Message »
From:Karen Abgarian <abvk Date:February 25 2012 5:37am
Subject:Re: exclusive write lock for innodb tbl
View as plain text  

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.  


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

exclusive write lock for innodb tblbruce25 Feb
  • Re: exclusive write lock for innodb tblKaren Abgarian25 Feb