This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills and have John Sonmez on-line!
See this thread for details.
The moose likes Servlets and the fly likes java.sql.SQLException: Closed Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "java.sql.SQLException: Closed Connection" Watch "java.sql.SQLException: Closed Connection" New topic
Author

java.sql.SQLException: Closed Connection

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
hi,
i am running online exam application developed using jsp & servlets
it is running correclt with the single user
but it fails to run when 2 or more users run this application cuncurrently

it shows different types of exceptions on each execution
like this
java.sql.SQLException: Closed Connection
java.sql.SQLException: Fail to convert to internal representation

and i 've debugged the application but no chance sombody help me out please for the god sake



A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
I have the feeling that you have connection as an instance variable
in one your jsp's or servlets. This connection would then be shared
by two users with one user stepping on the toes of the other, either by
closing the connection or redefining the variable.
Sri Gnana
Ranch Hand

Joined: Apr 29, 2004
Posts: 166
If you closed connection in your program it will happen. block that and try.


Thanks & Regards
Sri Gnana
Everythings Programmed!...
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
i haven't closed my connection any where except in finnaly block
Charles Lyons
Author
Ranch Hand

Joined: Mar 27, 2003
Posts: 836
Now might be a good time to share an outline of the source code you think might be generating the problem (please replace any non-essential parts with a comment to make it more readable)...


Charles Lyons (SCJP 1.4, April 2003; SCJP 5, Dec 2006; SCWCD 1.4b, April 2004)
Author of OCEJWCD Study Companion for Oracle Exam 1Z0-899 (ISBN 0955160340 / Amazon Amazon UK )
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
More likely that you use class variable for connection or some other db related data. Make sure that you have only method variables.


Tough in space?, <a href="http://tjws.sf.net" target="_blank" rel="nofollow">Get J2EE servlet container under 150Kbytes here</a><br />Love your iPod and want it anywhere?<a href="http://mediachest.sf.net" target="_blank" rel="nofollow">Check it here.</a><br /><a href="http://7bee.j2ee.us/book/Generics%20in%20JDK%201.5.html" target="_blank" rel="nofollow">Curious about generic in Java?</a><br /><a href="http://7bee.j2ee.us/bee/index-bee.html" target="_blank" rel="nofollow">Hate ant? Use bee.</a><br /><a href="http://7bee.j2ee.us/addressbook/" target="_blank" rel="nofollow">Need contacts anywhere?</a><br /><a href="http://searchdir.sourceforge.net/" target="_blank" rel="nofollow">How to promote your business with a search engine</a>
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
ya i am using class variable for creating the connection and i am creating the connection in service method

and never seen such type of exceptions
please some body help me
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
Declare any variables used for connection in service method. Servlet container uses only one copy of your servlet class to service concurrent requests.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
sir , can i create multiple connections and preparedstatements and resultsets for my servlet
is there any fault in doing this???
or shall i create only one connection and one stmt and one rs for all the queries i am using my servlet???
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
Yes you can create multiple connections, but as has been stated, the
variable used to store/create/use the connection should be declared inside
of a method (and passed toi any other that might need it) not as a class
variable, or you get the problems you are getting now. Declaring the
variable for the connection inb the class means you have only one, as the
JSP is compiled and started only once. All connections are handled by the
same instance. So, when you declare it a class variable, then create the
connection in the method you get the following:

user one: creates connection thereby overwriting the class variable
user two: creates connection thereby overwriting the class variable
and destroying everything uer one was trying to do
user three: as user two above but for both user one and two, etc, etc, etc
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

You have a much slower app now.
All requests are going to have to queue up and wait for a single database connection.

In other words, you've just coded out the multithreaded ability of a J2EE app.

You may want to look into connection pooling.
Most containers ship with this capability.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
Originally posted by saikrishna cinux:
hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:


No it does not handle multiple requests, now it only handles one request
at a time not multiple. It can handle multiple users, but only because it
handles them one after the other, and not simultaneously. It is usually a
very bad idea to use class/instance variables in a servlet/jsp unless it is
something that is not going to change during the life of the servlet/jsp
(or at least will only be changed very sparingly and then probably only
through synchronized methods), but synchronizing your doGet/Post methods is
a very poor (design- and performance- wise) solution (at least when done
without a very good reason).
[ May 19, 2006: Message edited by: Martin Simons ]
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by Martin Simons:


but synchronizing your doGet/Post methods is
a very poor (design- and performance- wise) solution (at least when done
without a very good reason).

[ May 19, 2006: Message edited by: Martin Simons ]


Yes Mr Martin, i am synchronizing the doget method because of strong reason i will explain u every thing now.

here i am developing online examination for GRE
so, 10 students will be taking the exams cuncurently
so for this only one servlet will take entire data(questions) from the database so i think this causes verymuch burden to the server so when 10 members access the same servlet at a time then it sudenly FAILS
i dont knwo why tomcat is incapable to handle multiple user requests while fetching huge data from the database
It just throws the exception on 10 members who r taking the exam at same point of time
the exceptions are like this
for member 1-----java.sql.SQLException: Invalid column index
for member 2-----java.sql.SQLException: Closed Connection
for member 3-----java.sql.SQLException: Failed to represent internal data
so comming to my coding in tht servlet i am using lotz of Vectors for storing the data from DB and lots of inner loops and inner loops contains lots of queries

if a single user runs this application it runs perfectly...
now tell me, while developing this webapplication every one follows the same rule tht is "if the application runs well in one system then it must run on multiple systems superbly!!!" but here it fails
it even gives these type of exceptions "Failed to represent internal data"

so now what is the neccesity of Tomcat ???

what should i do now ???
that is the STRONG reason why i am using SYNCHRONIZED doGet method
so now justify....

:roll:
thanking u sir

regards
cinux
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

But there is an important point we've been trying to explain to you and you haven't picked it up yet. It has nothing to do with the load on Tomcat, Tomcat is fine. Your code is not thread safe. This means multiple threads accessing the code will cause it to fail in strange ways - like you've been seeing. A rough but inefficient way of fixing it is to restrict the flow to a single thread at a time by synchronizing the method - which is what you found.

Everything you're saying leads me to believe your code is not thread safe. There is little we can do to really help unless you post a sample. It needs fixing because your code is not working properly.

Dave
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Originally posted by saikrishna cinux:

so i think this causes verymuch burden to the server


Ten simultaneous connections is nothing! Servers are designed to handle a much greater load than that.


i dont knwo why tomcat is incapable to handle multiple user requests while fetching huge data from the database


Tomcat is perfectly capable of handling such situations. It's your code that is causing the problems.

As pointed out, you have written a non-thread-safe applciation. So as soon as more than one thread is kicked off, your application fails. Blaming Tomcat is not the answer. Fixing your code is.

And synchronizing the connection so that only one can execute at a time is a not the answer.

First, re-read the replies, there's a lot of good suggestions there. The most important of which is to adopt the use of a connection pool. Tomcat provides and excellent connection pooling mechanism.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by saikrishna cinux:


i dont knwo why tomcat is incapable to handle multiple user requests while
...
so now what is the neccesity of Tomcat ???
...
what should i do now ???


Tomcat is very capable of handling multiple user requests and does so for thousands of commercial apps.

The problem isn't with the container you're using.
You would have the exact same issue with any other container.
The problem is in your code.
You're declaring your database connection as an instance variable and then initializing it in your service methods. This is not a thread safe approach.

Several people have suggested alternatives.
You might want to look into them before bashing Tomcat (or whatever other conatiner you're using).



[scary klunk]
[ May 19, 2006: Message edited by: Ben Souther ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

David <-> bear <-> Ben BONK!
[ May 19, 2006: Message edited by: Bear Bibeault ]
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
Originally posted by saikrishna cinux:
hey the problem is solved
just by keeping synchronized modified to the doGet method()
now it handles multiple request at a time
is there any wrong in keeping synchronized for the method doGet()

can i follow this method??? :roll: :roll:
Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Originally posted by dema rogatkin:
Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.


Only if you are expecting one visitor at a time. Otherwise you've tied a tourniquet around the throat of your application.

Bad idea. Very bad idea. Not at all a smart solution in my opinion.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by dema rogatkin:
Cool. I should suggest it first. Certainly it's a smart solution and you can use it for your app without fear.


If there are to be only 10 or so users on the system at once, and..
If the database calls are relatively quick, synchronizing the doGet method may be tolerable to the original poster.

In case the last half dozen responses weren't clear enough, let me re-iterate.
Sharing a single database connection by declaring it as an instance variable and then instanciating it in the service methods is a poor idea (a bug actually).
The result is a non-thread-safe architecture.

Synchronizing the service method, is a simple hack that will make the app work but at the expense of scalability. Any long database calls will force all other users on the system to have to wait. This will never be a scalable solution and may (if the database calls take any length of time to execute) result in a sluggish app with as little as two concurrent users.

Since the main goal of Javaranch is to be a "Friendly place for Java Greenhorns" (a place to learn about Java coding), I don't think it's a good idea to encourage people to patch poor code with less than optimal solutions such as this.
[ May 19, 2006: Message edited by: Ben Souther ]
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
For some reason people design software considering thousand concurrent requests, and managing million transaction a day. This is not a requirement for many systems, so what's the reason all time go to the same way? If average time of servicing a request around 50ms and a peek of usage doesn't exceed 10 requests per second, then I can tell that dispatching threads servicing all these request concurrently can take more time than service them sequentially. Why people can't design software accordingly requirements? I do not know.

Edit: not always processing concurrently works faster than sequentially. But it's another topic.
[ May 19, 2006: Message edited by: dema rogatkin ]
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Dema,

Experienced developers make judgment calls all the time.
There are always compromises and trade-offs to be considered.
Expedience often trumps best practice design, scalability, CPU/Memory
efficiency, etc... And it should. It would make no sense for a company
to pay for large n tiered application if they know the
requirements could easily be met with a quick and dirty script written
in a high level language.
That's common sense and I don't think anyone here is disputing that.

In this case someone has written to a forum, trying to understand why
his application isn't working with multiple users. There are obvious
signs that a misunderstanding about the way threading in a servlet app
works has led him to write code that is not thread safe. He has also
demonstrated that he is having trouble understanding the suggestions
made to him by other people on the forum and, in his frustration,
has gone as far as to blame his servlet container for the concurrency
issues.

While working on the problem he has discovered that synchronizing the
service method seems to have fixed his app. (Load testing, even with 10
users may reveal otherwise). He has even specifically asked if there is
any harm in synchronizing the doGet method. A really good question, in
my opinion; one that deserves an honest and thorough answer.

There is a huge difference between an experienced developer making a
'time to market' vs scalability decision and a beginner working to
understand (and asking) how the technology works.

Do you really think you're helping the original poster by congratulating
him and telling him that he's written a 'smart solution that can be used
without fear'? I don't. I would much rather see him walk away
from the experience with an understanding of how threading works in a
servlet app.

This, as I understand it, is the point of JavaRanch.
[ May 20, 2006: Message edited by: Ben Souther ]
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
Ben,
Just after reading of few responses of OP you should get clear idea that he/she has no any experience with multi threading programming. I undertand your desire to help but you should understand that you can't replace a good learning course by few suggestions even from super smart guy. Software engineering requires time for learning and a lot of practice. Some fast knowledge can play bad in future issuing low quality software everyone should deal with. So, giving a simple solution is better than trying to jump over head.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Originally posted by Bear Bibeault:
David <-> bear <-> Ben BONK!



Hopefully the same view from three staff members will carry some weight.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

dema,
while I agree that is in the case in many cases, I don't believe teaching simple solutions is a good idea when the practice is unsafe or something the user will later have to un-learn.

If it was a case of teaching a simpler implementation where the full solution could be integrated later I would support it, but in this case it applies to the whole architecture of the solution and provides the illusion of thread safety without any real guarantee.

I see it as more 'compensating errors' which provide the correct output in the current circumstance and not a real fix to the problem.

Just my thoughts though

Dave
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Originally posted by David O'Meara:

Hopefully the same view from three staff members will carry some weight.


I come in at 235 lbs.

So, giving a simple solution is better than trying to jump over head.


There is a big difference between a simple solution and a poor practice.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
hi to all
ok Mr ben i agree that u r all experienced guy's and u all know how to develop thread safe applications
now the point is i just help me out in this reg i have explained u all abt my application what i am using there and what i am trying to do.

but none of the member is giving me a good suggestion.

ok for now by7 placing synchronized method the application is running well no exception at run time but the problem is no 2 ppl are getting correct answers
one member is getting 26 out of 28 questions while the other is getting 30 out of 28 questions
whatz happenning here ?
i think the sessions are exchaning the questions haaa


some body help me

thanking u all for valubale suggestions
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
Dave,
I do not know who you are by occupation, but believe me I worked as a universtity professor for long time. So teaching is something different than you meet in your profession as a software engineer. You need to concentrate attention a student on something relevant to current topic and keep in shade other details which will be learned after. So, here is a perfect example, if he/she learns servlet, then thread safety can be other topic. On this stage of education adding synchronized is just perfect solution. Later in other courses students can learn how to get thread safety without possible compromisimg performance. Again, without sufficient information about requirements we can't recommend any thread safety solution. BTW If you are work near Oracle we can get lunch together and do more discussion.
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
Originally posted by Bear Bibeault:


There is a big difference between a simple solution and a poor practice.

Bear,
Excellent answer. A simple solution doesn't mean a poor practice at all. A poor practice is without knowledge any requirements are guessing what they could be and try to propose some common practice as a remedy for all cases.
dema rogatkin
Ranch Hand

Joined: Oct 09, 2002
Posts: 294
A little more food for thinking. Just imagine that system is backended by a database which can't proceed concurrent requests.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61760
    
  67

Originally posted by dema rogatkin:

A simple solution doesn't mean a poor practice at all.


No one said a simple solution was poor practice. Masking a problem instead of fixing it is what I consider a poor practice.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459


Can I say I appreciate that you are prepared to discuss the issues without getting personal. This is rare on the internet.

I will admit that some of my statements are opinion, but I still believe that it is the correct way to proceed. While threading is critical to the understanding of servets, if you weren't going to teach threads explicitly I would still introduce a 'pattern' for producing stable code.

eg




This is just a quick sample of how I would approach the issue.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
Originally posted by David O'Meara:






hey i 've used the same patter for creating the connection the problem still exists

:roll:
i dont know why it creates a problem???
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

We've been hoping you can post some sample code, because otherwise we're just guessing at what your problem could be.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
ok boss atlast here i am copying my code
this servlet is for fetching the questions,choices,answer from the database
and i apologize to all for the inconvinence (that the code is very long)

thanking u all
now u must definitely help me
coz i am now displaying my code here so pick out the wrong code(BUGS)
once again i am telling u all that this application runs perfectly with the single user but it fails to run with 2 or more users


[BPSouther: Added UBB Code tags]
[ May 20, 2006: Message edited by: Ben Souther ]
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

While you're at it, can you post the code the superclass "MainServlet.java" so we can see how you've implemented the getConnection method?
Use the UBB code tags as I've done so your indenting is preserved.
Charles Lyons
Author
Ranch Hand

Joined: Mar 27, 2003
Posts: 836
ok boss atlast here i am copying my code
this servlet is for fetching the questions,choices,answer from the database
and i apologize to all for the inconvinence (that the code is very long)

Well long it is... it's no wonder debugging is a nightmare.

I haven't read it all, but I've glossed over it. My main observation is that you use multiple Connection objects, when (I think) all you ever need do here is get a PreparedStatement from each one. You could do this easily from only one Connection without problem. The only time you would want to use multiple Connections is when you're connecting to a different database.

I'm also not sure why you've got as many instance variables as you have. Many (if not most/all) of these would be better as method-locals. Why do you need a RequestDispatcher to be an instance variable? It's far better as a method-local (and indeed every code I've ever seen does it this way).

The reason you are still experiencing problems is that all your ResultSets and Statements (which depend on the Connection) are also instance variables. Making the Connections method-local isn't sufficient.

Might I now suggest an improvement like this:


This tidies the code up somewhat - make the query variable method-local and only use it for conditional constructions. The fact I've wrapped several lines in the query() method means I've saved lots of lines in the implementation (one method call several times is much tidier than several lots of identical code just copied/pasted). This method now catches the exception and returns null if something fails. Of course if you use the ArrayList construction several times, that can be wrapped in a method as well.

I also don't understand why you've executed a Statement in some places and used PreparedStatements in others. PSs are supposed to be re-used over and over again, replacing ? parameters with new values on each execution. Since you do everything once, you're only incurring a performance hit using PSs. Do you understand the difference between the two?

Note that only one Connection is ever required for one client; we create it in the doGet() method and use it in other convenience methods. This is now thread-safe.

If all you're doing is setting up the exam here, you might also be better doing all the configuration logic in a set of separate JavaBeans and not in a servlet (I have an exam simulator which uses a JavaBean model to manage the exams). I notice that you're using some beans anyway. Remember that servlets are invoked for every request, so all this loading needs to be done every time a request is made - highly inefficient if your exam format rarely changes. My JavaBean model is kept in memory (it doesn't use much) and only refreshes itself if some configuration data in the exam changes... this is analogous to the way in which containers treat JSPs - they only translate and compile them once, not on every request, but will retranslate/compile if a change occurs.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Four things:
1.)
Originally posted by saikrishna cinux:
...
ok Mr ben i agree that u r all experienced guy's and u all know how to develop thread safe applications ...

You've been around long enough to know that non-words such as "u r" are
strongly discouraged here. They confound language translation software
and make it very difficult for non-English speaking members to read your posts.

If you have't already, please see:
http://faq.javaranch.com/view?UseRealWords

2.)
Originally posted by saikrishna cinux: ...
but none of the member is giving me a good suggestion.

Quite the opposite..
By making this thread controversial, another ranch hand has caused this
thread to receive a lot more attention than it probably would have
otherwise. You've got two bartenders, a sheriff, an author, and a handful of ranchhands all looking at your situation and offering good suggestions.

Instead of complaining, I would suggest that you take full advantage of the
attention being spent here and use this opportunity to learn about servlet
threading. You might not ever get this level of free help again.


3.)
Originally posted by saikrishna cinux:
ok for now by7 placing synchronized method the application is running well
no exception at run time but the problem is no 2 ppl are getting correct
answers one member is getting 26 out of 28 questions while the other is
getting 30 out of 28 questions

whatz happenning here ?

i think the sessions are exchaning the questions haaa


In addition to the servlet threading issues, you may indeed have other
flaws in your database schema and/or business logic. You may need to
work these out yourself.

My suggestion is that you first, take advantage of the suggestions
made so far and get your database connection handling straightened
out so that you have a proper servlet that is thread safe and that
won't cause memory leaks and/or JDBC lock ups. Once that's done,
it will be easier for you to debug any other issues in your
code.

4.)
Originally posted by saikrishna cinux:
thanking u all for valubale suggestions

You are welcome.

[ May 20, 2006: Message edited by: Ben Souther ]
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689


Here is my MainServlet
for simplification i need to use mainservlet


regards
cinux
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: Closed Connection