RE: Tx - row lock contention after implementing transaction management in application server

Well done.


And thanks for the follow-up/summary explaining what you did to solve the 
problem.

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of dd yakkali
Sent: Wednesday, May 27, 2009 4:07 PM
To: Tanel Poder
Cc: martin.a.berger@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Tx - row lock contention after implementing transaction management 
in application server

Tanel, etal

Thanks a lot guys, It worked great on the child insert session. I
could not get the bind values for the parent insert session.

proactive in production is tough as we have a big connection pool size
and we do not know when this issue occurs and we do not have a way of
reproducing it. This way they can look at the bind values and figure
out what data is causing this to happen and troubleshoot it.

As people already said, I do not think it has any thing to do with not
having an index on the FK column.

Also I do not know why transaction  mgt uisng different oracle
sessions in one Java transaction.

Thanks every one for their replies.

Here is what I did.


1) select spid, pid from gv$process where addr = (select paddr from
gv$session where sid = 1037);
9510 45

2) oradebug setospid 9510

3) oradebug dump errorstack 2

Here is what I see in the trace file

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=264 off=0
  kxsbbbfp=ffffffff7b9d6258  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=24
  kxsbbbfp=ffffffff7b9d6270  bln=22  avl=03  flg=01
  value=2008
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=48
  kxsbbbfp=ffffffff7b9d6288  bln=22  avl=05  flg=01
  value=26821.62
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=72
  kxsbbbfp=ffffffff7b9d62a0  bln=22  avl=02  flg=01
  value=12
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=96
  kxsbbbfp=ffffffff7b9d62b8  bln=22  avl=00  flg=01
 Bind#5
  oacdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=120
  kxsbbbfp=ffffffff7b9d62d0  bln=32  avl=00  flg=01
 Bind#6
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=152
  kxsbbbfp=ffffffff7b9d62f0  bln=22  avl=00  flg=01
 Bind#7
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=176
  kxsbbbfp=ffffffff7b9d6308  bln=22  avl=00  flg=01
 Bind#8
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=200
  kxsbbbfp=ffffffff7b9d6320  bln=11  avl=00  flg=01
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=216
  kxsbbbfp=ffffffff7b9d6330  bln=22  avl=04  flg=01
  value=425377
 Bind#10
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=240
  kxsbbbfp=ffffffff7b9d6348  bln=22  avl=04  flg=01
  value=939865

Thanks
Deen






On Tue, May 26, 2009 at 3:22 PM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
>
> v$sql_bind_capture may not give you the right bind variable value as you 
> can't really control when exactly the capture happens (you may see an "old" 
> bind value or someone else's bind there).
>
> sql_trace would be the proactive approach if you can rerun your query and 
> reproduce the problem. If the problem has already happened to you, you can 
> attach to target with oradebug and run "oradebug dump errorstack 2" on the 
> target process and search for "value=" or "bfp=" in the tracefile.
>
> Differend Oracle versions might show the output differently but in my case it 
> showed the bind value 1234567890 ok:
>
> cursor instantiation=fffffd7ffdae6ac8 used=1243347090
>  child#0(3ab6906a0) pcs=39c8b1718
>   clk=3a20672a8 ci=3a51a1a18 pn=3a420c2f0 ctx=3a4518978
>  kgsccflg=0 llk[fffffd7ffdae6ad0,fffffd7ffdae6ad0] idx=0
>  xscflg=c0110476 fl2=5200009 fl3=42222008 fl4=100
>  Bind bytecodes
>   Opcode = 2   Bind Twotask Scalar Sql In (may be out) Copy
>   oacdef = 3a6c353c0   Offsi = 48, Offsi = 0
> kkscoacd
>  Bind#0
>   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
>   oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
>   kxsbbbfp=fffffd7ffdae5e28  bln=22  avl=06  flg=05
>   value=1234567890
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
>
>
>
> ________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Martin Berger
> Sent: 26 May 2009 21:58
> To: dd.yakkali@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Tx - row lock contention after implementing transaction 
> management in application server
>
> Deen,
> maybe you want to start with
> http://laurentschneider.com/wordpress/2007/05/vsql-and-bind-variable.html
> hh,
>  Martin
> --
> http://www.freelists.org/webpage/oracle-l
> Am 26.05.2009 um 20:41 schrieb dd yakkali:
>
> Hello everyone,
>
> After our application folks implemented transaction management in the app, I 
> am seeing a bunch of seesions waiting with "Tx - row lock contention" on an 
> insert statement. we found that the parent table insert is not commited and 
> hence the child record insert is hanging as both these statements are using 
> different oracle sessions for some reason. This continues for eternity, until 
> the app server is killled and restarted.
>
>
> Sun Java Enterprise Server, hibernate, oracle 10.2.0.4 RAC.
>
>
> Now here is the question: Our java app server folks are asking me to give 
> them bind variable values of the statement that is hanging. We have a 
> connection pool which is 132 connections size. Is there any way to get the 
> bind variable values after the fact, i.e while it is waiting for the parent 
> to commit?
>
>
>
> Thanks
> Deen
--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Other related posts: