Re: Please Help: table locking

From: Geoffrey Bray <gbray1_at_my-deja.com>
Date: Tue, 12 Dec 2000 20:09:35 GMT
Message-ID: <9160lo$3a$1@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 Tue Dec 12 2000 - 14:09:35 CST