Re: update takes table level lock

Mark and Thomas,

Thanks for giving me a better understanding of oracle locking. I was following the 8i tuning training manual from oracle which states that you should see TX lock_type while row level locks are being taken and TM locks imply table level locks. I will increase the log_buffer to see if it helps. I could not see any contention on the redo log device itself.

thanks again
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:a1nii8017f7_at_drn.newsguy.com...
> In article <a1mtkh$r1i18_at_kcweb01.netnews.att.com>, "S.A." says...
> >
> >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.
> >
>
> the individual rows are locked, the TM ? (DML Enqueue) Lock:
>
> These locks are used to protect a table from being altered while you are
in the
> middle of modifying the contents of it. For example, if you have updated a
> table, you will acquire a TM lock on that table. This will prevent someone
else
> from DROPping that table, or ALTERing it in some fashion. If they attempt
to
> perform certain DDL on the table while you have a TM lock on it, they will
> receive the following:
>
> drop table dept
> *
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> which is a confusing message at first, since there is no method to specify
> NOWAIT or WAIT on a drop table at all. It is just the generic message you
get
> when you attempt to perform an operation that would be blocked but the
operation
> does not permit blocking. It is the same message you would get if you
SELECTed
> from the table with the FOR UPDATE NOWAIT clause, and hit a locked row.
> .....
>
> during a large update like that, you would expect lots of log writing as
the
> redo log buffer is flushed frequently. You may be experiencing contention
on
> your log devices if you have very high waits on these or your log buffer
might
> be sized a tad too small. something to look into.
>
>
>
> >thanks for any help.
> >
> >
> >
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Sun Jan 13 2002 - 16:54:32 CST