RE: Tx - row lock contention after implementing transaction management in application server
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
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
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=05value=1234567890
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
Subject: Re: Tx - row lock contention after implementing transaction management in application server
maybe you want to start with
Am 26.05.2009 um 20:41 schrieb dd yakkali:
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?
http://www.freelists.org/webpage/oracle-l Received on Tue May 26 2009 - 14:22:55 CDT