Re: Please Help: table locking

Do you have a foreign key on the table at all ? If you do and it doesn't have an index this will cause a table lock on the referenced table. So updating two different rows on this table would try to acquire the same table lock and cause probs.

Jacqui

"Geoffrey Bray" <gbray1_at_my-deja.com> wrote in message news:9160lo$3a$1_at_nnrp1.deja.com...
> Oracle does row level locking so from the looks of your statements it
> seems a bit unlikely that these two statements are locking each other
> out. You should also check v$session_wait to figure out what your
> session is waiting on. It will show an 'enqueue' wait if it is indeed
> waiting on the other row. If one is indeed waiting on the other then
> I'm betting with the earlier comment that you have a corrupted index.
>
> The following query will show you if you the 'blocker' if you do have
> one query blocking another.
>
> column lock_type format a24
> column blocking_others format a20
> column mode_held format A20
>
> select session_id,
> lock_type,
> mode_held,
> blocking_others
> from sys.dba_lock
> where blocking_others != 'Not Blocking'
> /
>
>
> The following query will show those being blocked:
>
> column lock_id1 format a8
> column lock_id2 format a8
> break on lock_id1 skip
>
> select lock_id1,
> lock_id2,
> session_id
> from sys.dba_lock
> where blocking_others = 'Not Blocking'
> and lock_type = 'Transaction'
> group by lock_id1, lock_id2, session_id
> /
>
> That should take care of your problem.
>
> Geoffrey Bray
>
>
>
> In article <9147lh$idq$1_at_nnrp1.deja.com>,
> susana73_at_hotmail.com wrote:
> > In article <914421$fmm$1_at_nnrp1.deja.com>,
> > Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> > > In article <91431g$es9$1_at_nnrp1.deja.com>,
> > > Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> > >
> > > > > There are 800k rows. Tablespace is 20GB. Extents of the
 'object'
 table
> > > > > is 2000 extents(i know this is not optimized and I'll fix this
 later).
> > > > > There is a combined PK on object(id,plan). There are no other
 users
 on
> > > > > line. To update one row should take a second, so I am sure it
 hangs.
> > > > >
> > > > > Thanks!
> > > > >
> > > > > Susan
> > > > >
> > > > > Sent via Deja.com http://www.deja.com/
> > > > > Before you buy.
> > > > >
> > > >
> > > > wow. A puzzler. Best guess is that you have corrupted indexes on
 the
> > > > table. You must be adding data to the system quite regularly, so I
 am
> > > > assuming that this table has a lot of transactions against it. Are
 you
> > > > doing any deletes? or other updates? How often? How many? Are
 there
 any
> > > > other indexes on the table?
> > > >
> > > > I am thinking you could drop all indexes for this table and
 rebuild
> > > > them. With 800K rows, this could take some time.
> > > >
> > > > Another idea is run explain plan on the query. It should pick up
 the
> > > > index. If it does not use the index, try analysing the table.
> > > >
> > > > --
> > >
> > > Here is a strange idea. You start each of these updates and then the
> > > application hung, so you quit the application on PC/desktop. Then
 you
> > > tried this again, etc. Check the users active in you system. You may
> > > have a whole bunch of users all logged in still. Often when a user
> > > terminates his program in a less than orderly fashion, Oracle
 maintains
> > > the connection for some time. If you find these users, try killing
> > > these sessions.
> > >
> > > --
> > > Michael Krolewski
> > > Rosetta Inpharmatics
> > > mkrolewski_at_rosetta.org
> > > Ususual disclaimers
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Hi Michael,
> >
> > Thanks for the ideas. However, I think it's a locking problem as the
> > query on v$lock table clearly shows a lock. Would a bad index cause
> > table lock?
> >
> > The other strange thing is it doesn't hang all the time, only couple
> > times a week. My application does update data on 'object' table all
 day
> > long. The same update statements run pretty well couple thoudsand
 times
> > until it got hang. So it's pretty weird.
> >
> > Thanks,
> > Susan
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Fri Dec 15 2000 - 12:40:47 CET