Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
Message-ID: <4D876119.4060809@christopherschultz.net>
Date: Mon, 21 Mar 2011 10:30:49 -0400
From: Christopher Schultz <chris@christopherschultz.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
 rv:1.9.2.15) Gecko/20110303 Thunderbird/3.1.9
MIME-Version: 1.0
To: Tomcat Users List <users@tomcat.apache.org>
Subject: Re: tomcat issue with DBCP connection pool
References: <31199494.post@talk.nabble.com>
In-Reply-To: <31199494.post@talk.nabble.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To whom it may concern,

On 3/21/2011 6:59 AM, the_biswa wrote:
> I've written a thread program where in the run method I'm opening an
> HTTPURLConnection and feeding a valid input and reading the server response.

Do you launch this thread during normal request processing? If so, do
you wait on this thread to complete before allowing the request
processing thread to continue?

I'm asking because if you launch a thread and then complete the request
without waiting, there is a high likelihood that you will
inappropriately re-use request-scoped objects and create havoc with the
container. If you are waiting on the secondary thread anyway, then it's
not really necessary at all.

> Running the program for as many as 600 threads with no delay inbetween
> thread-calls I can get all the 600 requests processed without any issue.
> 
> However, when I run the wget command (wget --post-file=ac.xml
> --header=Content-Type:text/xml
> http://10.227.30.204:9090/ABCService/ABCService) in a loop of 300 iteration
> to load-test the application I see that the application is failing after
> request 297 with the following error
> 
> java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
> ORA-01000: maximum open cursors exceeded

Sounds like one of two problems:

1. You are not properly closing your database resources when you are
   finished with them (see
http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/)

2. You need more database resources allocated on the server
   (see Oracle documentation for configuring server-side resources)

> BTW, my application calls a stored procedure which in turn uses an oracle
> SYS_REFCURSOR. As I know ref cursor can not be closed through code.

How are you supposed to close that cursor, then? Did you mean you can't
close it through Java code, or you can't even close it through PL/SQL?

> Once the error is encountered the application is not recovering until the
> application is restarted. 

Sounds like closing the connection and re-initializing it would probably
do the job, too. Is it possible for you to detect the kinds of requests
that will allocate these SYS_REFCURSORs and use a one-time connection
instead of going through the pool?

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2HYRkACgkQ9CaO5/Lv0PAOpQCfWY5OKJoRo1C8Ld//bOTpCC3x
t/QAn2qdGxo0uXDU01rf6A+IElsrLDTq
=40df
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org