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!
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 ?
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.
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?
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.
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?
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.
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
Joined: Jan 19, 2006
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.
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.