| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Please Help: table locking
In article <9142a6$ebk$1_at_nnrp1.deja.com>,
susana73_at_hotmail.com wrote:
> In article <913tjl$aks$1_at_nnrp1.deja.com>,
> Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> > In article <913s5g$9dm$1_at_nnrp1.deja.com>,
> > susana73_at_hotmail.com wrote:
> > > I have an app that open 2 database connections concurrently. It
hangs
> > > couple times a week due to table lock. The following is a report
shows
> > > what happen:
> > >
> > > USERNAME SID SERIAL# TY HELD REQUEST
> > > ------------------------------------------------------------
> > > SQL
> > > ------------------------------------------------------------
> > > PROD_USER 13 124 TX Exclusive None
> > > UPDATE object set flag = 1 WHERE plan = 'A' AND Id = 3466629
> > >
> > > PROD_USER 29 9034 TX None Share
> > > UPDATE object set flag = 1 WHERE plan = 'B' AND Id = 3466631;
> > >
> > > The two sessions(SID 13,29) are updating different rows so I have
no
> > > idea how this can generate locks. Session 13 is held as
EXCLUSIVE and
> > > Session 29 are requesting as SHARE. What does that mean and would
this
> > > be a hint to solve this problem?
> > >
> > > I do not specify any locking feature explicitly in my application
at
> > > all.
> > >
> > > Please Help!! Thanks.
> > >
> > > Susan
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Needs more information. It is unlikely that based on the above
> > statements and no other users on-line that the database will hang.
> > How big are the tables (rows/extents/tablespace)? Are there any
> > indexes? Are there any ohter users on line? If you openned two
> > connectinos and executed the above statements with no there users,
the
> > system hangs? Or does it just take alot of time?
> >
> > --
> > Michael Krolewski
> > Rosetta Inpharmatics
> > mkrolewski_at_rosetta.org
> > Ususual disclaimers
>
> 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.
>
>
>
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.
--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
Ususual disclaimers
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Dec 11 2000 - 20:37:37 CST
 |
 |