Re: Large set-based UPDATE

From: Andrew Allen <andrew.allen_at_handleman.com>
Date: Wed, 12 Feb 2003 17:37:22 GMT
Message-ID: <3E4A7939.4030707@handleman.com>

Sybrand Bakker wrote:

> f1fteen_at_hotmail.com (Paul) wrote in message news:<7b7286ec.0302120402.3a3818c8_at_posting.google.com>...
> 

>>Hi all,
>>
>>I am reviewing an Oracle stored procedure (BULK_UPDATE) that makes a
>>large set-based update to a large table (ACCOUNTS, 5 million rows).
>>
>>The problem we are encountering with this is that, because the UPDATE
>>statement applies changes to a large number of rows at a time to the
>>ACCOUNTS table (around 150,000), a web layer that also allows updates
>>to this table is being regularly locked out for an unacceptable length
>>of time.
>>
>>To summarise the code:
>>
>>An UPDATE statement is constructed within a stored procedure and
>>executed using EXECUTE IMMEDIATE
>>
>>This UPDATE statement affects around 150,000 rows of a 5,000,000 row
>>table
>>
>>I was thinking a possible solution to this is to break the 150,000 row
>>set based update into 150,000 updates to single rows by processing
>>them through a cursor. I would then COMMIT within the cursor every
>>1000 rows.
>>
>>Is this a good way to go?
>>
>>Am I right in thinking that only 1000 rows at a time will be locked
>>using this method?
>>
>>Is this likely to be much slower given that I am now updating 1 row at
>>a time through a cursor?
>>
>>All help is gratefully received.
>>
>>Paul.

> 
> 
> 
> As you are not issuing SELECT FOR updates, or issuing an explicit LOCK
> TABLE in row share mode, your lock will automatically escalate to a
> table level lock.
> Setting up smaller transactions won't help, you need to have a LOCK
> TABLE in row share mode prior to your EXECUTE IMMEDIATE
> You may also want to review the execution path of the statement,
> checking whether or not it has a full table scan on the table to be
> updated.
> 

Mr. Bakker,

Please point to where in Oracle documentation we can find anything that tells us that Oracle (in default operation) escalates locks to the table level.
Oracle will only lock transactions that are attempting to update a row which has already been updated but not yet committed. I suggest that Mr. f1fteen_at_hotmail.com (Paul) has an application that is attempting to update rows currently being updated or has some other concurrency issue.

I further suggest that committing in a cursor loop is a sure way to get a snapshot too old error.

--
regards,
Andrew Allen

Received on Wed Feb 12 2003 - 11:37:22 CST