RE: Tx - row lock contention after implementing transaction management in application server
- From: Fmhabash <fmhabash@xxxxxxxxx>
- To: <dd.yakkali@xxxxxxxxx>, Tanel Poder <tanel@xxxxxxxxxx>
- Date: Wed, 17 Jun 2009 16:10:29 -0400
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@xxxxxxxxx>
Sent: Wednesday, May 27, 2009 4:07 PM
To: Tanel Poder <tanel@xxxxxxxxxx>
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
> --
> //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]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
