perform expensive calculation on a number of rows without locking all rows until all complete

From: Martin <martin.j.evans_at_gmail.com>
Date: Wed, 28 Sep 2011 11:55:50 -0700 (PDT)
Message-ID: <4dd69032-bdfb-4136-8363-4b83ac381897_at_q25g2000vbx.googlegroups.com>



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.

my first (working but probably ora 01555 snapshot out of date, error prone) was:

for arow in (select entry.id from entry where recalc = 1) loop

   perform expensive calculation
   update entry set recalc = 0 where entry.id = arow.id;    commit; -- potential snapshot out of date problem end loop

as although the calculation is expensive per entry it is not so bad but I do
not want to lock all entries needing recalc whilst all entries are recalculated.

Ideally I think I need to select the entry columns with recalc = 1 into a list (say a global temp table) for update of the recalc column, reset entry.recalc = 0 for all entries in one go, commit, then perform the calculations but I cannot work out a way to do this e.g. (yes, I know this is invalid sql, just here to demonstrate the problem),

insert into gtt (select id from entry where recalc = 1 for update of recalc);
update entry set recalc = 0 where entry.id in (select id from gtt); commit; -- nothing locked for write now
loop through gtt performing calculation which is independent of the entry

of course, you cannot do this.

or

update all entry.recalc = 0 where entry.recalc=1 returning entry.id (for all ids)

Any ideas?

Thanks Received on Wed Sep 28 2011 - 13:55:50 CDT

Original text of this message