Re: update takes table level lock

"S.A." <a_at_a.com> wrote in message news:<a1mtkh$r1i18_at_kcweb01.netnews.att.com>...
> Friends,
>
> We running 8.1.7.0 on sun E-10000.
>
> An update statement takes about 7 minutes to update 6000 rows in a 50,000
> rows table.
> The statement is:
>
> UPDATE <table_name> SET amount=:V001
> WHERE col1=:V002 AND col2=:V003 AND col3=:V004 AND col4=:V005 AND col5=:V
> 006 AND col6=:V007 AND col7=:V008 AND col8=:V009 AND col9=:V010
> AND col10=:V011 AND col11=:V012 AND col12=:V013 AND col13=:V014 AND
> col14=:V015 AND col15=:V016 ;
>
> I see following during the update activity:
>
> SELECT object_name, object_type, v$lock.type
> FROM dba_objects, v$lock
> WHERE object_id = v$lock.id1
>
> OBJECT_NAME OBJECT_TYPE TY
> ---------------------- ------------ --
> table_name TABLE TM
>
> I saw 'TM' - table level lock throughout the update activity. Does it mean
> that all 6000 updates are done serially?
> Is there anyway I can force a row level lock?
>
> The statspack report also shows high log file sync and log file parallel
> write waits.
>
> thanks for any help.

S.A., All DML takes a table level lock to prevent another session from altering the object structure while the update is in progress. The presence of such does not necessarily prevent object level sharing with another updater unless it is an exclusive lock. You did not display the lock mode values. Rows that did not meet the where clause condition you displayed were available for update by another session.

Also you might be interested to know that the row level locks are maintained in the Oracle data blocks and cannot be viewed in the SGA in any v$ table. In the case of a row level lock conflict the waiting session will show the rowid information in v$session.

I this post helps you understand Oracle locking a little better.

• Mark D Powell --