Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

New Article: JDBC Connection Pooling

 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24211
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A rootin', tootin' new article entitled "JDBC Connection Pooling", written by David Murphy of JNetDirect, appears in the latest issue of the JavaRanch Journal. You kin check out that there article here. Y'all have anything to say about it? Say it right in this thread!
 
Carlos Schweizer
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, what about JBoss, Tomcat, etc. connection pool, why did i implement a new connection pool ?
A matter of concern is when the scope of my pool is when the application is running in different context than the pool, so we may use RMI or implement a new connection pool ?
Any sugestions ?
 
Pj Murray
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Ernest Friedman-Hill:
A rootin', tootin' new article entitled "JDBC Connection Pooling", written by David Murphy of JNetDirect, appears in the latest issue of the JavaRanch Journal. You kin check out that there article here. Y'all have anything to say about it? Say it right in this thread!



Rootin' tootin' is an understatement.

This article is a good demonstration of why all the focus on ORM and other Java persistence tools are a way of avoiding really understanding how JDBC and databases really work.

Well done to David Murphy!



.
 
Pj Murray
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Carlos Schweizer:
Hi, what about JBoss, Tomcat, etc. connection pool, why did i implement a new connection pool ?
A matter of concern is when the scope of my pool is when the application is running in different context than the pool, so we may use RMI or implement a new connection pool ?
Any sugestions ?



This is a thread about the article and its contents. I think you need to post a separate question with your problem?
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the section about the life cycle of JDBC connections, there is an example of creating a connection in a servlet's init() method and closing it in the servlet's destroy() method. The following text says

"However the database connection is kept open for the entire lifetime of the servlet and that (sic) the database will have to retain an open connection for every user that is connected to your application."

I don't think this is correct. There is normally one instance of the servlet, and hence one instance of the connection. Those single instances will be shared by all users of the servlet.

There may be other problems associated with this pattern (such as race conditions when two users access the servlet at the same time) but it will not lead to scaling failures because of the number of users.
 
David P Murphy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul - you are correct since init() is called only once when the servlet is loaded.

The final code shows a correct usage of init() using it to perform the JNDI lookup only once at servlet load time.

public void init(ServletConfig config) throws ServletException {
try {
// Look up the JNDI data source only once at init time
Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
datasource = (DataSource) envCtx.lookup("jdbc/MyDataSource");
}
catch (NamingException e) {
e.printStackTrace();
}
}
 
kevin maloney
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi --

I'm relatively new to Java, for the last few months I've been grappling with an issue related to connection pooling. Every example of pooling or tutorial on pooling that I've seen (including your own) requires that any database connection that you want to pool must be defined in configuration files that are bound and deployed with the application. (I'm thinking of files such as your "DBCPConfiguration.xml", or my own environment's "WEB-INF/web.xml" and "META-INF/data-sources.xml" files, although I understand that the specific names and locations may vary depending on the server environment chosen.)

In my app incorporating the database definitions (such as database name, instance, connect strings, etc.) inside these source files just isn't appropriate for a large number of reasons -- the most significant of which is that everything (including the number of database my app needs to talk to) needs to be configurable at run time by the app's administrators from within the application. The process of adding new databases has to be made easy for them, and can't require altering configuration files in source code and redeploying the application.

My approach has been to just dynamically create conventional connections, and assign connect strings at run time (the connect strings are retrieved from a core control database that I do pool), and forego the benefits of pooling. It works fine, and I'm happy with it, until I read articles like yours and start to think about it. I understand the benefits of connection pooling -- and in fact ran a bunch of metrics that tell me how much better my app would perform if I could use it.

After all that intro, my question is fairly simple: is there any way to use connection pooling without knowing in advance the number of databases you'll have to connect to, and all of their various connection parameters?

TIA!
 
Roger Chung-Wee
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On possible solution is to dynamically create a connection pool. This is server dependent, so your server may not support this. An example is WebLogic Server which enables you to use the JMX API to dynamically create and configure a connection pool (and the associated data source) from within a Java application. Parameters such as database server and username can be read in at runtime and are stored in a Properties object.

I haven't actually done this, so I can't comment on how well it works.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the referenced article, David P Murphy wrote:
This fact also illustrates an important characteristic of a best practices pooling implementation -- your application is not even aware it's using it! Your J2EE application simply acquires JDBC connections from the datasource, does some work on the connection then closes the connection.


Having worked with some very, uh, shall we say "inventive" programmers over the years, I've rather come to dislike overloading the semantics of Connection.close() in this way, wherein in some contexts it actually closes the underlying connections and in some contexts it invisibly returns it to a pool.

The core problem is that there are SQL statements that can permanently alter the state of a connection; using "drop in" pooling can allow unanticipated side-effects to ripple out in ways that can be hugely difficult to debug. For example, consider a servlet with a block like:



This code can run for years, and pass all the unit tests, and then when you drop in "transparent" pooling, then BOOM, errors start happening somewhere else.

Now that particular example wouldn't be too hard to run down once the light goes off in your head, but imagine instead a more subtle side-effect, such as altering the default sort order, only under very rare circumstance; things could be subtly "flaky" for quite some time before developers get convinced that there's actually a bug.

Or imagine an application that normally uses READ_COMMITTED isolation levels but has a few critical spots where SERIALIZABLE is required...

Of course, any pooler is vulnerable to having a "poisoned" connection returned to it, wether it's returned via conn.close() or something like myPool.returntToPool(conn), but in the latter case, the programmer gets full and fair warning that side-effects are going to get carried back into the pool...

I do understand the immense convenience of the overloading approach, and in many environments it may be the right approach; I just think the types of bugs it allows are so painful that dispensing with that convenience in order to make programmers think about the context is more important.

I doubt I'll convince y'all of the rightness of my opinion, but I hope you think about it... Maybe we'll get some good discussion.
 
David P Murphy
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Replying to the issue Kevin M raised: Another possible approach is to construct JDBC datasources programatically (rather than instantiating them from JNDI). A JNDI datasource needs a config file of some sort to specify datasource properties outside the app.

Some JDBC vendors supply datasources that support pooling transparently. The datasource can be new()ed then the app sets properties programmatically -

MyVendorsDataSource ds = new MyVendorsDataSource();
ds.setServerName("server");
ds.setUser("user");
ds.setPassword("password");
ds.setCustomProp1("someValue1");
ds.setCustomProp2("someValue2");
..
Connection con = ds.getConnection();
..
con.close();

In response to Stu D's comments: Its the connection pool implementation's job to always issue connections in a known state to applications. Whether the connection it issues is a new physical connection or a recycled connection is immaterial - the state must be consistent. JDBC connection states such as auto commit, isolation level, transactional state etc etc should all be initialized as per the JDBC spec.

If that is the case then the developer does not need to be aware of (or care about) the possible different actions that a close() will perform and every new connection the application aquires is in the same known state.
 
George Stoianov
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,

First I wanted to say thank you to David P Murphy for the very nice article on connection pooling. I have been using connection pooling for some time and have been running into some problems, connection pool gets overloaded and MySQL refuses connections.

I am instantiating a pool programatically for all my applications and it is managed individually not through JNDI, but I am know ready to try it with JNDI so that the application container can manage the pool, I think my problem maybe with the pool management, and I can enjoy the benefits in every webapp without db connection specific code (I am using Tomcat BTW two different versions).

One thing I wanted to add is that even though the article does not cover the aspect of configuration of the specific application container, and rightfully so, I personally had some major problems getting the configuration right due to version differences and syntax and would have greatly benefitted from some guidance in that area.

At the end of it all I configured MySQL with a very simple table, which may explain my 10 times or higher response times, and my JMeter results show such a difference between pooled and non-pooled it is absolutely obvious what a tremendous benefit pooling gives you.

Thanks again,
George
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic