This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes New Article: JDBC Connection Pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "New Article: JDBC Connection Pooling" Watch "New Article: JDBC Connection Pooling" New topic
Author

New Article: JDBC Connection Pooling

Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24187
    
  34

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!


[Jess in Action][AskingGoodQuestions]
Carlos Schweizer
Greenhorn

Joined: Sep 19, 2003
Posts: 13
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

Joined: Sep 24, 2004
Posts: 194
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 -
Pj Murray
Ranch Hand

Joined: Sep 24, 2004
Posts: 194
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
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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

Joined: Jan 19, 2006
Posts: 2
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

Joined: Dec 19, 2005
Posts: 2
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

Joined: Sep 29, 2002
Posts: 1683
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.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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

Joined: Jan 19, 2006
Posts: 2
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

Joined: Jan 15, 2006
Posts: 94
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
 
GeeCON Prague 2014
 
subject: New Article: JDBC Connection Pooling