Re: deadlock in database connection pooling?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mitch, On 12/10/2009 7:43 PM, Mitch Claborn wrote: poolPreparedStatements=true driverClassName=com.mysql.jdbc.Driver validationQuery=select * from operator limit 1 FWIW, this is a non-ideal validation query: 1. It uses SELECT *, which is not particularly efficient. 2. It uses a real table, which means that real data will be selected. 3. It uses a LIMIT, which means that you know that too much data is going to come back. Might I suggest: validationQuery=/* ping */SELECT 1 MySQL Connector/J drivers after a certain version recognize the /* ping */ at the beginning of the query and will execute a low-level connection check that does not involve a query at all! So, you get your validation that the connection is properly connected without all that silly work on the server. If the version of the MySQL driver is NOT new enough, it will execute the SELECT 1 query which consults no tables and executed super fast. You win both ways. testWhileIdle=true testOnBorrow=false testOnReturn=false That's an odd configuration... why test while idle but not when the connection is needed? I suspect that if you set testOnBorrow=true and testWhileIdle=false, you will avoid your deadlock problem. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAksiav8ACgkQ9CaO5/Lv0PDpMgCfU2gk24uX4Dbis4mj+89Nxo8t vq0AoKxl0RC1AmU72MNCDbVhK4yf2SCl =JUuv -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: deadlock in database connection pooling?
Excellent information! I will try those immediately. mitch Christopher Schultz wrote: Mitch, On 12/10/2009 7:43 PM, Mitch Claborn wrote: poolPreparedStatements=true driverClassName=com.mysql.jdbc.Driver validationQuery=select * from operator limit 1 FWIW, this is a non-ideal validation query: 1. It uses SELECT *, which is not particularly efficient. 2. It uses a real table, which means that real data will be selected. 3. It uses a LIMIT, which means that you know that too much data is going to come back. Might I suggest: validationQuery=/* ping */SELECT 1 MySQL Connector/J drivers after a certain version recognize the /* ping */ at the beginning of the query and will execute a low-level connection check that does not involve a query at all! So, you get your validation that the connection is properly connected without all that silly work on the server. If the version of the MySQL driver is NOT new enough, it will execute the SELECT 1 query which consults no tables and executed super fast. You win both ways. testWhileIdle=true testOnBorrow=false testOnReturn=false That's an odd configuration... why test while idle but not when the connection is needed? I suspect that if you set testOnBorrow=true and testWhileIdle=false, you will avoid your deadlock problem. -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
deadlock in database connection pooling?
I'm seeing several occurrences of deadlocks in Tomcat like the following. Any clues? Definition of the Resource is below. Found one Java-level deadlock: = http-8081-56: waiting to lock monitor 0x08f50bd0 (object 0x560511f8, a org.apache.tomcat.dbcp.pool.impl.GenericObjectPool), which is held by Timer-0 Timer-0: waiting to lock monitor 0x08f9cea4 (object 0x5669f358, a org.apache.tomcat.dbcp.dbcp.PoolableConnection), which is held by pool-3580-thread-5 pool-3580-thread-5: waiting to lock monitor 0x08f50bd0 (object 0x560511f8, a org.apache.tomcat.dbcp.pool.impl.GenericObjectPool), which is held by Timer-0 Java stack information for the threads listed above: === http-8081-56: at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:916) - waiting to lock 0x560511f8 (a org.apache.tomcat.dbcp.pool.impl.GenericObjectPool) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96) at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) at com.csc.mm.db.DBUtil.getDBConnection(DBUtil.java:89) (truncated - application code) Timer-0: at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.addTrace(AbandonedTrace.java:175) - waiting to lock 0x5669f358 (a org.apache.tomcat.dbcp.dbcp.PoolableConnection) at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.init(AbandonedTrace.java:92) at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.init(AbandonedTrace.java:82) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.init(DelegatingStatement.java:61) at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.createStatement(DelegatingConnection.java:224) at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:331) at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.validateObject(PoolableConnectionFactory.java:312) at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.evict(GenericObjectPool.java:1217) - locked 0x560511f8 (a org.apache.tomcat.dbcp.pool.impl.GenericObjectPool) at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool$Evictor.run(GenericObjectPool.java:1341) at java.util.TimerThread.mainLoop(Unknown Source) at java.util.TimerThread.run(Unknown Source) pool-3580-thread-5: at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.addObjectToPool(GenericObjectPool.java:1136) - waiting to lock 0x560511f8 (a org.apache.tomcat.dbcp.pool.impl.GenericObjectPool) at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.returnObject(GenericObjectPool.java:1076) at org.apache.tomcat.dbcp.dbcp.PoolableConnection.close(PoolableConnection.java:87) - locked 0x5669f358 (a org.apache.tomcat.dbcp.dbcp.PoolableConnection) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181) at com.csc.mm.db.DBUtil.executeQuery(DBUtil.java:408) (truncated - application code) Resource name=jdbc/main url=jdbc:mysql://blahblah/blahblah?profileSQL=falseamp;zeroDateTimeBehavior=convertToNull username=ddd password=dd auth=Container type=javax.sql.DataSource initialSize=0 minIdle=0 removeAbandoned=false removeAbandonedTimeout=60 logAbandoned=true maxActive=150 maxIdle=100 maxWait=1 poolPreparedStatements=true driverClassName=com.mysql.jdbc.Driver validationQuery=select * from operator limit 1 testWhileIdle=true testOnBorrow=false testOnReturn=false timeBetweenEvictionRunsMillis=1 numTestsPerEvictionRun=100 minEvictableIdleTimeMillis=60 / - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: deadlock in database connection pooling?
From: Mitch Claborn [mailto:mi...@claborn.net] Subject: deadlock in database connection pooling? I'm seeing several occurrences of deadlocks in Tomcat like the following. Any clues? Definition of the Resource is below. Want to give us a hint about what Tomcat version you're using? Or is Pid's Internet Telepathy required (again)? I believe there's a known deadlock in Commons DBCP 1.2.x levels that is supposed to be fixed in 1.3. Tomcat 6.0.20 uses Commons DBCP 1.2.2, so you /may/ have run into that. - 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.
Re: deadlock in database connection pooling?
Sorry - I was going to put that in and just forgot. Old age strikes again. It is 6.0.20. Can I simply delete the tomcat version of dbcp (jar file) and drop in the 1.3 version from commons? Mitch Claborn 972-954-7341 mi...@claborn.net Caldarale, Charles R wrote: From: Mitch Claborn [mailto:mi...@claborn.net] Subject: deadlock in database connection pooling? I'm seeing several occurrences of deadlocks in Tomcat like the following. Any clues? Definition of the Resource is below. Want to give us a hint about what Tomcat version you're using? Or is Pid's Internet Telepathy required (again)? I believe there's a known deadlock in Commons DBCP 1.2.x levels that is supposed to be fixed in 1.3. Tomcat 6.0.20 uses Commons DBCP 1.2.2, so you /may/ have run into that. - 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-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: deadlock in database connection pooling?
From: Mitch Claborn [mailto:mi...@claborn.net] Subject: Re: deadlock in database connection pooling? Can I simply delete the tomcat version of dbcp (jar file) and drop in the 1.3 version from commons? No, Tomcat renames the classes in the jar to avoid collisions with any webapps using their own copies. Also, I don't think 1.3 has been released yet. There is an alternative JDBC pool that will be shipped with some future release of Tomcat; you might want to try that: http://people.apache.org/~fhanik/jdbc-pool/v1.0.7.1/ - 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.