Re: perform expensive calculation on a number of rows without locking all rows until all complete
From: Andreas Leitgeb <avl_at_gamma.logic.tuwien.ac.at>
Date: 28 Sep 2011 19:54:55 GMT
Message-ID: <slrnj86usf.6gl.avl_at_gamma.logic.tuwien.ac.at>
Martin <martin.j.evans_at_gmail.com> wrote:
> I have a table entry which contains a column called recalc (value 0 or
> 1). Sometimes code sets the recalc column to 1 indicating to a job
> which runs at regular intervals it should update another table history
> with expensive calculations for the entry. When the calculation is
> complete (or when the rows with entry.recalc=1 are selected so we know
> which entries to perform the calculation on) the entry.recalc column
> needs to be reset to 0. As the calculation is expensive I do not want
> to keep the rows in the entry table locked whilst the calculation is
> performed on each entry although keeping one entry locked whilst its
> calculation is performed is not so bad. Additionally, other code might
> want to set entry.recalc to 1 at any time.
end loop
Date: 28 Sep 2011 19:54:55 GMT
Message-ID: <slrnj86usf.6gl.avl_at_gamma.logic.tuwien.ac.at>
Martin <martin.j.evans_at_gmail.com> wrote:
> I have a table entry which contains a column called recalc (value 0 or
> 1). Sometimes code sets the recalc column to 1 indicating to a job
> which runs at regular intervals it should update another table history
> with expensive calculations for the entry. When the calculation is
> complete (or when the rows with entry.recalc=1 are selected so we know
> which entries to perform the calculation on) the entry.recalc column
> needs to be reset to 0. As the calculation is expensive I do not want
> to keep the rows in the entry table locked whilst the calculation is
> performed on each entry although keeping one entry locked whilst its
> calculation is performed is not so bad. Additionally, other code might
> want to set entry.recalc to 1 at any time.
Some idea:
let the job run something like the following pseudocode:
loop
update ... set recalc=0 where recalc=1 AND ROWNUM=1 returning id
commit
if none found then exit from loop
perform longish task for id
end loop
It will then find only one row, process it, then search anew for another such row. From your description, it seems like the longish task would run long enough, that the cost of redoing the select for recalc=1 from scratch each time doesn't really matter all that much. (you do have an index on recalc, don't you?) Received on Wed Sep 28 2011 - 14:54:55 CDT