Oracle stored procedure hanging when called via jdbc

I have a web application which executes oracle stored procedures and shows the results on a browser. The technology stack is as follows: Browser <-> spring mvc <-> [(tomcat)jboss] <-jdbc-> oracle. The stored procedures have an out cursor for the results and the java cod开发者_运维技巧e uses that cursor to retrieve the resultset.

Everything was running fine until a new stored procedure was added, which although terminates fast enough in SQL Developer, it freezes when it gets called from the application. The java debugging showed that the code freezes at the OracleCallableStatement.execute. Initially I thought there was something wrong with the procedure but it does run successfully on sql developer, so I am now pointing more towards a jdbc problem...

I guess this has to do with some sort of deadlock while reading the out cursor, or could it be a bug in the jdbc driver (version I am using: ojdbc6 - 11.1.0.7.0)? Any ideas?

Thanks

Make sure there's no row lock that your session is waiting on. The following SQL*Plus script could give you hints about how to do that. Or have a look in Oracle Enterprise manager (the Oracle web GUI) under Blocking Sessions.

BTW a deadlock is something different that the database would take care of by killing one of the two sessions...

REM  Purpose
REM  -------
REM  Display locks currently held and requested. Displays which session a
REM  blocked lock is waiting for.
REM
REM  Ver  Who  When       What
REM  ---  ---  ----       ----
REM  1.0  DrB  12-Dec-97  Initial version
col uname     head "Username"  form a12
col sid       head "SID"       form 999
col ltype     head "Type"      form a4
col lmode     head "Mode"      form a10
col blocked   head "Wait"      form a4
col details   head "Details"   form a40
set verify off
set pause on
accept user prompt  "Username [%]: "
select SubStr('alter system kill session ''' || s.sid || ',' || s.serial# || ''';', 1, 40) as kill, s.username uname, 'DML' ltype,
  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,
  decode (l.request,0,'No','Yes') blocked,
  u.username||'.'||o.name details,
  Nvl(s.Program, s.Module) What
from v$session s, v$lock l, sys.obj$ o, all_users u
where s.username like nvl(upper('&user'||'%'),'%')
and s.sid = l.sid
and l.id1 = o.obj#
--and l.type = 'TM'
and o.owner# = u.user_id(+)
union all
select SubStr('alter system kill session ''' || s.sid || ',' || s.serial# || ''';', 1, 40) as kill, s.username uname,
  decode (l.type,'TX','TX',
                 'UL','USR',
                      'SYS') ltype,
  decode (l.lmode,1,'Null',
                  2,'Row-S',
                  3,'Row-X',
                  4,'Share',
                  5,'S/Row-X',
                  6,'Exclusive') lmode,
  decode (l.request,0,'No','Yes') blocked,
  decode (l.request,0,null,'Waiting on session '||to_char(b.sid)) details,
  Nvl(s.Program, s.Module) What
from v$session s, v$lock l, v$lock b
where s.username like nvl(upper('&user'||'%'),'%')
and s.sid = l.sid
and l.type != 'TM'
and l.id1 = b.id1(+)
and b.request(+) = 0
order by 5 desc,3 desc,2,1;
set verify on
REM  End of file

Cool. I reproduced the setup (but did not get a freeze), so this is still a work in progress.

I am fairly certain that the issue is related to the XA transaction in Java since that is the significant difference between executing the stored proc from Java and from SQLDeveloper. Since the call freezes (indefinitely ?), can you reproduce and scoot into JMXConsole and swipe a thread dump from the jboss.system:type=ServerInfo MBean ? Seeing a full stack trace will give a better idea of what the thread is waiting on.

==== Addendum ====

Are there any pragmas, DDL or any other transaction affecting clauses in the stored procedure or any of it's dependencies ?

==== Update ====

The thread is runnable so we know the client is simply waiting on a response from the server. I thought about the DDL potentially affecting the Transaction, but based on your pseudo-code, the temporary tables are all created implicitly using the WITH statement, which I verified does not trigger a commit. Can you confirm that there are no CREATE TEMPORARY TABLE statements ?