I have few servlet and Action classes. In each servlet or action I need to access database so I need JDBC Connection. Instead of opening a new one everytime, I want to create in the first time and then store it in the "session" so it can be carried from one servlet to another, or another Action class, etc. Is this safe and is it a good way to save the time for opening a connection ? I mean, temporarily ignore other optimization methods like pooling, etc. I want to see if I think makes sense.
Originally posted by Bear Bibeault: No, it is a very bad idea. By doing so you run the risk of having separate threads compete for the same connection.
You should be using container-managed connection pooling and obtain a connection from the pool for each request, releasing the connection when the request is done with it.
Thannks. Could you help me clarify the following things --
1. Don't we have risk of having separate threads compete for any other objects stored in the "session" ? what if they compete to do "setAttribute" on those objects at the same time ? Why is only "Connection" object specially concerned ?
2. I use an application server and it seems I do NOT need to code anything for connection pooling. All I have to do is check a box "use connection pool". Then I simply write
1. If you create a Connection just for that Session, then no, you don't have that threading issue, per say. But is bad design because you have no control over how many Connection objects end up getting created, and since you won't be using it 100% of the time, it is wasted resources that a Connection Pool handles.
2. Yes, it handles all that for you. And is that simple.
If you create a Connection just for that Session, then no, you don't have that threading issue
I disagree. It is quite possible for the same session to have multiple active requests competing for the same session data when multiple browser windows are opened using Cntl-N, or when using framesets, popup windows, or iframes. [ July 05, 2005: Message edited by: Bear Bibeault ]
Yes, but be sure to get the connection within the scope of a request, and to release it within the scope of that same request. If you take the pooled connection and stuff it into the session, you introduce the same multi-threading problems I alluded to earlier.
Why is only "Connection" object specially concerned
It's not special. All session data is vulnerable to threading issues. For read-only attributes it's usually not an issue. [ July 05, 2005: Message edited by: Bear Bibeault ]
Joined: Jun 21, 2005
Originally posted by Bear Bibeault: [QB]
It's not special. All session data is vulnerable to threading issues. For read-only attributes it's usually not an issue.
This is bit scary, especially for session beginner like me. My application includes multiple JSP pages that are connected by servlet and action classes(struts). Eventually it is the result display page using what user has selected in the previous pages. quite common application.. So, basically for each page I have an action class and in it I use "getParamter()" sort thing to grab what user selects, and set it into session using session.setAttribute(). I understand this object is not thread safe, but since everytime user clicks "submit" button my Action class "reset" its value, so it should be fine. Am I right ?
Secondly, bear, you said I should take care of the connection within the request scope. Do you think I should explicitly do "connection.close()" after I am done with the query ? I am not sure about that because it seems I should just "return" the open connection to the pool without closing it so it can be reused. right ? But how to "return" an open connection to pool ?
A little story as to why keeping database resources reserved for specific sessions is a bad idea. This happened a few years ago at a major customer in the financial sector and involved a very large database server. The database was Oracle 8i with several thousand licenses running on the largest available HP6000 series hardware, so an order of magnitude larger than your typical webapplication.
A new application for users accessing the database had been introduced. From that moment on the database server would suddenly start refusing requests every day at around 10AM. People were baffled, the only thing that would cure the problem was for the server to be rebooted, it seemed completely locked up every day at about the same time (mind the database itself hadn't changed, only the applications accessing it).
As I'd seen (on a smaller scale) behaviour like that before I suggested checking the new application for improper release of database resources and indeed it was found that the application opened a new database connection for every request and failed to release that connection again after use. This way the few dozen users of the new application could completely lock up (inadvertently) the database server in under an hour of use every morning. The application was removed from service to be repaired and the problems were immediately gone.
Keeping your database connection in your session has a similar effect. If there are only a few simultaneous sessions (such as during testing) you'll never notice anything. But when the application starts to get used for real with people coming in and leaving without properly logging out (we have this even during testing despite hammering the point home to our testers every day that they MUST log out properly) connections will accumulate quickly. Now a web application on a successful site may have several thousand active users (as in, non-expired sessions) at any time. Unless your database server can handle that many open connections at once you'd better use a system in which connections are open for the shortest possible time (which means for the duration of an HTTP request or better yet only for the duration of the SQL query that is to be performed). By using a connection pool you can save the cost of creating and disposing of database connections constantly while retaining a system in which servlets don't hold onto open connections needlessly.