You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by chris derham <ch...@derham.me.uk> on 2011/04/29 18:57:42 UTC
Problem with connection pool
All,
I am having a weird problem with an app. We were having concurrency issues,
so we setup some simple pessimistic locking - we lock a single table using
select for update. We have developed a jmeter script that allows us to test
the app when loaded. Repeatedly after different intervals, but less than say
2 mins, the app locks up. By locks up, any attempt to access a webpage that
connects to the database doesn't respond.
When looking in the database, I can see several threads waiting to lock the
row, and one thread having the lock. Thing is that thread with the lock is
inactive as far as the database is concerned. So what I deduce is that a
connection goes to server, obtains the lock, and then somehow swapped out
and so never commits transaction, hence lock is never released, hence issue.
What I would like to know is how to track down what is causing this. I have
tried taking stack traces - happy to share but they are long not sure if
relevant. ThreadDumpAnalyzer reports
Overall Thread Count 151
Overall Monitor Count 209
Number of threads waiting for a monitor 0
Number of threads locking a monitor 127
Number of threads sleeping on a monitor 105
Number of deadlocks 0
Number of Monitors without locking threads 0
69% of all threads are sleeping on a monitor.
This might indicate they are waiting for some external resource (e.g.
database) which is overloaded or not available or are just waiting to get to
do something (idle threads). You should check the sleeping threads with a
filter excluding all idle threads.
Connection to the db configured like this
<Resource name="jdbc/xts"
auth="Container"
type="javax.sql.DataSource"
username="username
password="password"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:xe"
maxActive="20"
maxIdle="2"
minIdle="1"
initialSize="2"
/>
Windows 7 64 bit
java version "1.6.0_24"
Java(TM) SE Runtime Environment (build 1.6.0_24-b07)
Java HotSpot(TM) 64-Bit Server VM (build 19.1-b02, mixed mode)
tomcat 7.0.11
OracleXe database
I tried to use org.apache.tomcat.jdbc.pool.DataSourceFactory - broadly same
problem happens, but it gives up after 30 seconds saying "Pool empty. Unable
to fetch a connection in 30 seconds, none available[20 in use]."
So any ideas as to what to look into next? I appreciate that this app stack
is java, tomcat, spring, hibernate, oracle. Not sure that this is a tomcat
specific issue, but hoping someone might have hit this before, or be able to
point me in the direction to go. Had a quick look through the dbcp source -
can't see how to make it log when it does things. Any pointers
Thanks for any help
Chris
Re: Problem with connection pool
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Chris,
On 4/30/2011 6:58 AM, chris derham wrote:
> Finally managed to track the problem down to nested transactions -
> apparently spring doesn't support them. Once removed the problem went away
Glad you got it figured out. What is a "nested transaction"? Just
something like this:
BEGIN;
BEGIN;
UPDATE;
COMMIT;
DELETE;
COMMIT;
?
JDBC4 actually does support something like this: they are called
"savepoints". Read the JavaDocs for java.sql.Connection and search for
that string: it sounds like the capability you are trying to use there.
Then, of course, you'll have to figure out how to use them with Spring.
But, if Spring provides you with a java.sql.Connection object, you can
use savepoints directly. If not, maybe there's an API that you can
use... now that you know what the term is called.
Good luck,
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk2+wr4ACgkQ9CaO5/Lv0PDqAQCggFZDRdvD/SwG9lpYQN0Zvfgc
gcoAn36LsZH1PPRGgpRWWN1+nfHxiwBB
=VArQ
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Problem with connection pool
Posted by David Kerber <dc...@verizon.net>.
On 4/30/2011 6:58 AM, chris derham wrote:
> All,
>
>> What do you mean by "swapped out"?
>>
> Idle speculation in the face of a problem that I didn't really understand
> :-(
>
>>> 69% of all threads are sleeping on a monitor.
>> Fully expected, since that's what they do when waiting for work to show up.
>>
>>> This might indicate they are waiting for some external
>>> resource (e.g. database) which is overloaded or not
>>> available
>> You don't need to speculate;
>>
> I wasn't speculating - both lines were a quote from thread dump analyzer.
>
>> Using removeAbandoned and logAbandoned might be of interest.
>> ...
>> More evidence of a broken app, not returning connections to the pool when
>> it's done with them.
>>
> Our code uses hibernate. We never touch connections directly. We ask
> hibernate to execute some SQL, and it handles all the get/return connection.
> Hence I was some what sceptical of this advice. However having followed this
> user group for a few months now, I get that you know what you are talking
> about. So I turned on the logging, and sure enough at one point during
> initialization, we obtained a connection and didn't return it. So thanks for
> pointing out that setting.
>
> Finally managed to track the problem down to nested transactions -
> apparently spring doesn't support them. Once removed the problem went away
>
> Thanks for all your help
>
> Chris
Glad you got it sorted out, and thanks for telling us what the problem
was; it may save somebody else from the same headaches in the future.
D
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Problem with connection pool
Posted by chris derham <ch...@derham.me.uk>.
All,
>
> What do you mean by "swapped out"?
>
Idle speculation in the face of a problem that I didn't really understand
:-(
>
> > 69% of all threads are sleeping on a monitor.
>
> Fully expected, since that's what they do when waiting for work to show up.
>
> > This might indicate they are waiting for some external
> > resource (e.g. database) which is overloaded or not
> > available
>
> You don't need to speculate;
>
I wasn't speculating - both lines were a quote from thread dump analyzer.
>
> Using removeAbandoned and logAbandoned might be of interest.
> ...
> More evidence of a broken app, not returning connections to the pool when
> it's done with them.
>
Our code uses hibernate. We never touch connections directly. We ask
hibernate to execute some SQL, and it handles all the get/return connection.
Hence I was some what sceptical of this advice. However having followed this
user group for a few months now, I get that you know what you are talking
about. So I turned on the logging, and sure enough at one point during
initialization, we obtained a connection and didn't return it. So thanks for
pointing out that setting.
Finally managed to track the problem down to nested transactions -
apparently spring doesn't support them. Once removed the problem went away
Thanks for all your help
Chris
RE: Problem with connection pool
Posted by "Caldarale, Charles R" <Ch...@unisys.com>.
> From: cjderham@gmail.com [mailto:cjderham@gmail.com] On Behalf Of chris derham
> Subject: Problem with connection pool
> So what I deduce is that a connection goes to server,
> obtains the lock, and then somehow swapped out and so
> never commits transaction, hence lock is never released,
> hence issue.
What do you mean by "swapped out"? Do you think the two systems are conspiring to arbitrarily deactivate one of your threads for no reason? Nothing in what you've posted shows the likelihood of anything other than an application bug (as David K pointed out).
> 69% of all threads are sleeping on a monitor.
Fully expected, since that's what they do when waiting for work to show up.
> This might indicate they are waiting for some external
> resource (e.g. database) which is overloaded or not
> available
You don't need to speculate; the stack trace shows exactly what each thread is waiting on.
> <Resource name="jdbc/xts"
> auth="Container"
> type="javax.sql.DataSource"
> username="username
> password="password"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@localhost:1521:xe"
> maxActive="20"
> maxIdle="2"
> minIdle="1"
> initialSize="2"
> />
Using removeAbandoned and logAbandoned might be of interest.
> I tried to use org.apache.tomcat.jdbc.pool.DataSourceFactory -
> broadly same problem happens, but it gives up after 30 seconds
> saying "Pool empty. Unable to fetch a connection in 30 seconds,
> none available[20 in use]."
More evidence of a broken app, not returning connections to the pool when it's done with them.
- Chuck
THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Problem with connection pool
Posted by David kerber <dc...@verizon.net>.
On 4/29/2011 12:57 PM, chris derham wrote:
> All,
>
> I am having a weird problem with an app. We were having concurrency issues,
> so we setup some simple pessimistic locking - we lock a single table using
> select for update. We have developed a jmeter script that allows us to test
> the app when loaded. Repeatedly after different intervals, but less than say
> 2 mins, the app locks up. By locks up, any attempt to access a webpage that
> connects to the database doesn't respond.
>
> When looking in the database, I can see several threads waiting to lock the
> row, and one thread having the lock. Thing is that thread with the lock is
> inactive as far as the database is concerned. So what I deduce is that a
> connection goes to server, obtains the lock, and then somehow swapped out
> and so never commits transaction, hence lock is never released, hence issue.
This smells like an application problem to me, not releasing the
connection when it's done with it.
>
> What I would like to know is how to track down what is causing this. I have
> tried taking stack traces - happy to share but they are long not sure if
> relevant. ThreadDumpAnalyzer reports
>
> Overall Thread Count 151
> Overall Monitor Count 209
> Number of threads waiting for a monitor 0
> Number of threads locking a monitor 127
> Number of threads sleeping on a monitor 105
> Number of deadlocks 0
> Number of Monitors without locking threads 0
>
> 69% of all threads are sleeping on a monitor.
> This might indicate they are waiting for some external resource (e.g.
> database) which is overloaded or not available or are just waiting to get to
> do something (idle threads). You should check the sleeping threads with a
> filter excluding all idle threads.
>
> Connection to the db configured like this
>
> <Resource name="jdbc/xts"
> auth="Container"
> type="javax.sql.DataSource"
> username="username
> password="password"
> driverClassName="oracle.jdbc.OracleDriver"
> url="jdbc:oracle:thin:@localhost:1521:xe"
> maxActive="20"
> maxIdle="2"
> minIdle="1"
> initialSize="2"
> />
>
> Windows 7 64 bit
>
> java version "1.6.0_24"
> Java(TM) SE Runtime Environment (build 1.6.0_24-b07)
> Java HotSpot(TM) 64-Bit Server VM (build 19.1-b02, mixed mode)
>
> tomcat 7.0.11
>
> OracleXe database
>
> I tried to use org.apache.tomcat.jdbc.pool.DataSourceFactory - broadly same
> problem happens, but it gives up after 30 seconds saying "Pool empty. Unable
> to fetch a connection in 30 seconds, none available[20 in use]."
>
> So any ideas as to what to look into next? I appreciate that this app stack
> is java, tomcat, spring, hibernate, oracle. Not sure that this is a tomcat
> specific issue, but hoping someone might have hit this before, or be able to
> point me in the direction to go. Had a quick look through the dbcp source -
> can't see how to make it log when it does things. Any pointers
>
> Thanks for any help
>
> Chris
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org