Re: Please Help: table locking
Date: Tue, 12 Dec 2000 02:37:37 GMT
Message-ID: <91431g$es9$1_at_nnrp1.deja.com>
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.
>
> Thanks!
>
> Susan
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
[Quoted] [Quoted] wow. A puzzler. Best guess is that you have corrupted indexes on the [Quoted] [Quoted] table. You must be adding data to the system quite regularly, so I am [Quoted] assuming that this table has a lot of transactions against it. Are you [Quoted] doing any deletes? or other updates? How often? How many? Are there any [Quoted] other indexes on the table?
[Quoted] [Quoted] I am thinking you could drop all indexes for this table and rebuild [Quoted] them. With 800K rows, this could take some time.
[Quoted] Another idea is run explain plan on the query. It should pick up the [Quoted] 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 Tue Dec 12 2000 - 03:37:37 CET