Re: perform expensive calculation on a number of rows without locking all rows until all complete
From: Martin <martin.j.evans_at_gmail.com>
Date: Thu, 29 Sep 2011 10:00:23 -0700 (PDT)
Message-ID: <3165b8b7-d6ba-4825-87ae-8260b40f6deb_at_n12g2000yqh.googlegroups.com>
On Sep 29, 5:43 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Martin wrote:
> > 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
>
> You've never heard of PL/SQL ? Any other 3GL?
Date: Thu, 29 Sep 2011 10:00:23 -0700 (PDT)
Message-ID: <3165b8b7-d6ba-4825-87ae-8260b40f6deb_at_n12g2000yqh.googlegroups.com>
On Sep 29, 5:43 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> Martin wrote:
> > 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
>
> You've never heard of PL/SQL ? Any other 3GL?
?
> Fetch a list of all entries needing recalculation, store that list in memory, start
> recalculating one by one.
Cannot do that as each entry needs updating to set recalc = 0.
If I fetched the list for update of recalc field all rows are locked until the processing is complete.
If I update all the rows so recalc = 0 I can no longer find which rows to operate on since update does not return multiple rowids.
If I select all the rows with recalc = 1 "into memory" as you say then run an update to set recalc = 0 I could be clearing recalc on an entry which has since been re-set to 1 since there was no lock between the select and the update.
Martin Received on Thu Sep 29 2011 - 12:00:23 CDT