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

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

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dd yakkali Sent: Wednesday, May 27, 2009 4:07 PM
To: Tanel Poder
Cc: martin.a.berger_at_gmail.com; oracle-l_at_freelists.org 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_at_poderc.com> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
> Sent: 26 May 2009 21:58
> To: dd.yakkali_at_gmail.com
> Cc: oracle-l_at_freelists.org
> 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