From: miaemp@my-deja.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 17:02:27 GMT
Organization: Deja.com
Lines: 91
Message-ID: <915lmt$lnh$1@nnrp1.deja.com>
References: <913s5g$9dm$1@nnrp1.deja.com> <9158ni$a6n$1@nnrp1.deja.com>
NNTP-Posting-Host: 12.13.177.194
X-Article-Creation-Date: Tue Dec 12 17:02:27 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt)
X-Http-Proxy: 1.0 x65.deja.com:80 (Squid/1.1.22) for client 12.13.177.194
X-MyDeja-Info: XMYDJUIDmiaemp


The table you are updating, does it have any Foreign keys?  If it does,
are the columns of the foreign key on the parent table indexed?  If
they are not, it will then lock the entire parent table and then in
turn block the second update, so, what you need to do then is index
those columns on the parent table, then the table lock should become a
row lock.

HTH
EP

The views I may have expressed are solely my own and not that of my
employer.


In article <9158ni$a6n$1@nnrp1.deja.com>,
  steveee_ca@my-deja.com wrote:
> Hi Susan,
>
> What about this..
>
> SQL> select xidusn,object_id,session_id,locked_mode
>   2  from v$locked_object;
>
>    XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
> --------- --------- ---------- -----------
>         3      2207         11           3
>         0      2207          9           3
>
> SQL> select object_name from dba_objects
>   2  where object_id = 2207;
>
> OBJECT_NAME
> ------------------------------------------------------
> EMPL
>
> I set up an artificial situation on a table I have called EMPL..the
 idea
> is that if the value of XIDUSN is 0, the session_id is requesting and
> waiting for a lock held by the session_id with the non-zero value..
>
> I know that you're aware of the table involved, but could you work
> backwards with v$session when your app hangs to isolate the session
> causing the problem and see what's up?
>
> Just a thought,
>
> Steve
>
> 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.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>


Sent via Deja.com
http://www.deja.com/