Re: Table Lock on Select for Update?

BJSIEBEN_at_bcsc02.gov.bc.ca wrote:
: If anyone can confirm what I've heard about Select for Update. What I
: heard was: when the select for update is issued, Oracle locks the row(s)
: in the select, but when the actual update occuer (ie. update....) Oracle
: will lock the entire table while the actual update is happening.
:
: Has anyone else heard this? I find it hard to believe the entire table
: is locked for the brief time the actual update occurs. We are using
: Oracle7 on AIX.
:
: .....Barry Sieben

No - Oracle will not escalate locks to table level, - hey, this isnt Ingres, for goodness sake ....

You will get a share lock at table level, the same as when you do a straight query, to stop the table scheme changing during your read-consistent time frame.

Why dont you monitor this with the lock monitor? If you have 7.1, use the Server Manager, it's got a superior monitor to sqldba. Failing that, figure out the object id from dba_objects, then look at v$lock where id1=<objectid>.

Regards,

-- 
******************* :-) *******************************************************
Jon Pope email:	jpope_at_mojo.europe.dg.com
European Database Competence Centre Manager
Data General UK Ltd.