Re: Having Deadlock Problem

From: VC <boston103_at_hotmail.com>
Date: Wed, 09 Jun 2004 16:57:47 GMT
Message-ID: <fkHxc.8376$0y.1815@attbi_s03>

Andy,

You'd want to determine exactly why the transaction everybody else is waiting on takes so much time. The cause may be as simple as the application issuing SELECT FOR UPDATE and the person who initiated the transaction left to get a cup of tea.

You'd need to determine whether it's a SQL issued by MTS or by your application or by some other unrelated user. By joining v$session , v$lock and v$sql, you can determine who's doing what during this unfortunate scenario. Something like:

select /*+ rule */ a.sid|| ' is blocking '|| b.sid||' with '||sql.sql_text from

  v$lock a,
  v$lock b,
  v$session sess,
  v$sql sql

where a.id1=b.id1

and a.id2=b.id2
and b.request>0
and a.block=1

and sess.sid=a.sid
and sql.address=sess.prev_sql_addr
and sql.hash_value=sess.prev_hash_value;

Before playing with inittarns and such, one has to know the exact reason of what's going on.

VC

"andy" <anandv81_at_rediffmail.com> wrote in message news:9bfbb3fd.0406090441.42dcfacb_at_posting.google.com...
> Sessions can & do go quietly into enqueue wait state & will wait
> forever
> to acquire a needed ITL slot for an object.
>
>
> Hello everyone,
>
> Thanks for your responses, I have tried analyzing the suggestions that
> you made and found the following.
>
>
> We have MDAC 2.7 installed on the application server and the
> application server is hosted on windows 2000 advanced server with SP4.
> Any idea if there are any known issues with this configuration?.
>
> I checked the parameters for the two tables facing this locking
> problem. They are
>
> STOCKBALANCE:
> --------------------
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> INITIAL 512K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
>
>
>
> SERIALNUMBERTRACK:
> ---------------------------------
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> INITIAL 902640K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
>
>
> As you can see the INITRANS value is set to 1 , should I increase this
> value, as the number of concurrent users updating this table is more
> than 1.
>
>
> I have already tried increasing the distributed lock timeout value and
> this had a reverse effect, as the first transaction itself was stuck
> for unknown reason now the other transactions now waiting for locks to
> be released , take a longer time to rollback.
>
> Thanks in advance
>
> Andy
Received on Wed Jun 09 2004 - 11:57:47 CDT