Re: Large set-based UPDATE

Hi Billy, thanks for taking the time to respond. Comments embedded below.

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<b2dg1v$8oh$1_at_ctb-nnrp2.saix.net>...

> What guarantee do you have if you lock 150k rows, that the web based
> front-end will not still wait as it wants to update one of those locked
> rows?

That is exactly the problem I am encountering. My UPDATE statement selects around 150,000 rows for update and so locks them from the web-based client.

>
> What about data integrity? What happens when your loop update processing
> fails after the 3rd loop iteration? Do you need to roll the updates already
> made back..? (not really possible) How do you determine where to restart
> your update loop? How do you stop the next batch update from running on a
> partially updated table, and making even a bigger mess of the existing
> mess?

Sorry, I should have mentioned that the cursor based solution would be functionally sound. The nature of the update is that it can be re-run without causing problems.

>
> How do the web update and batch update relate to one another? Are they
> updating the same columns? What happens if one updates before the other?

The web layer updates different columns to the bulk update.  

>
> An update should be made to fit the business requirements. It should be made
> to ensure data integrity. *That* is what a database must protect, before
> any consideration is given to making things faster. What will a 3 second
> response mean to the business if its data is suspect and integrity not
> enforced?

The cursor based update would functionally meet the business requirements.

>
> I suggest dealing with this issue first at a higher level, before trying to
> solve it technically.

Sorry, I should have mentioned that this analysis has already been carried out.

>
> You need to look at bussiness issues & requirements such as data intergrity
> first. In particular, what business requirement gave rise to this update
> conflict? In general, it is a Very Bad Idea (tm) to have batch processing
> and OLTP processing on the same table.

I have inherited the current application, and am looking for a short-term solution to the locks.

>
> Only then you can decide how to best address this particular conflict.
Received on Thu Feb 13 2003 - 03:48:04 CST