Re: Please Help: table locking

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_at_nnrp1.deja.com>,   steveee_ca_at_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_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.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Dec 12 2000 - 11:02:27 CST