From: Geoffrey Bray Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools Subject: Re: Please Help: table locking Date: Tue, 12 Dec 2000 20:09:35 GMT Organization: Deja.com Lines: 134 Message-ID: <9160lo$3a$1@nnrp1.deja.com> References: <913s5g$9dm$1@nnrp1.deja.com> <913tjl$aks$1@nnrp1.deja.com> <9142a6$ebk$1@nnrp1.deja.com> <91431g$es9$1@nnrp1.deja.com> <914421$fmm$1@nnrp1.deja.com> <9147lh$idq$1@nnrp1.deja.com> NNTP-Posting-Host: 139.60.210.5 X-Article-Creation-Date: Tue Dec 12 20:09:35 2000 GMT X-Http-User-Agent: Mozilla/4.74 [en] (Win95; U) X-Http-Proxy: 1.0 x72.deja.com:80 (Squid/1.1.22) for client 139.60.210.5 X-MyDeja-Info: XMYDJUIDgbray1 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@nnrp1.deja.com>, susana73@hotmail.com wrote: > In article <914421$fmm$1@nnrp1.deja.com>, > Mike Krolewski wrote: > > In article <91431g$es9$1@nnrp1.deja.com>, > > Mike Krolewski 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@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/