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

From: Tanel Poder <tanel_at_poderc.com>
Date: Tue, 26 May 2009 22:22:55 +0300
Message-ID: <077032966FE644949C0F075A489785DB_at_porgand>

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_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 Received on Tue May 26 2009 - 14:22:55 CDT