RE: Tx - row lock contention after implementing transaction management in application server
- From: "Tanel Poder" <tanel@xxxxxxxxxx>
- To: <martin.a.berger@xxxxxxxxx>, <dd.yakkali@xxxxxxxxx>
- Date: Tue, 26 May 2009 22:22:55 +0300
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/> 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 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
Other related posts:
