From: susana73@hotmail.com
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools
Subject: Re: Please Help: table locking
Date: Tue, 12 Dec 2000 02:25:14 GMT
Organization: Deja.com - Before you buy.
Lines: 61
Message-ID: <9142a6$ebk$1@nnrp1.deja.com>
References: <913s5g$9dm$1@nnrp1.deja.com> <913tjl$aks$1@nnrp1.deja.com>
NNTP-Posting-Host: 209.0.11.254
X-Article-Creation-Date: Tue Dec 12 02:25:14 2000 GMT
X-Http-User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; m18) Gecko/20001108 Netscape6/6.0
X-Http-Proxy: 1.0 x72.deja.com:80 (Squid/1.1.22) for client 209.0.11.254
X-MyDeja-Info: XMYDJUIDsusana73


In article <913tjl$aks$1@nnrp1.deja.com>,
  Mike Krolewski <mkrolewski@rosetta.org> wrote:
> In article <913s5g$9dm$1@nnrp1.deja.com>,
>   susana73@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@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.