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

From: Fmhabash <fmhabash_at_gmail.com>
Date: Wed, 17 Jun 2009 16:10:29 -0400
Message-ID: <4a394da8.c5c2f10a.7b8d.0a5f_at_mx.google.com>

One more thing to add. We have just finished our own battle with this same wait event. In our case, it resulted from 2 sessions inserting same value into a UK constraint. This scenario will also post the very same event. We disabled the constraint and the event totally disappeared from sql perf profile.  

---

Thank You.

-----Original Message-----
From: dd yakkali <dd.yakkali_at_gmail.com> Sent: Wednesday, May 27, 2009 4:07 PM
To: Tanel Poder <tanel_at_poderc.com>
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 s

[The entire original message is not included]

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

Received on Wed Jun 17 2009 - 15:10:29 CDT