Re: perform expensive calculation on a number of rows without locking all rows until all complete
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Thu, 29 Sep 2011 18:43:44 +0200
Message-ID: <4e84a00f$0$5032$ba620e4c_at_news.skynet.be>
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
Date: Thu, 29 Sep 2011 18:43:44 +0200
Message-ID: <4e84a00f$0$5032$ba620e4c_at_news.skynet.be>
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. Received on Thu Sep 29 2011 - 11:43:44 CDT