It was suggested to me in another forum that I could change my ResultSet Type flags from TYPE_SCROLL_SENSITIVE to TYPE_FORWARD_ONLY in order to speed up performance. I did that. Then my code broke all over the place. Generally it broke because I was calling ResultSet.first() in a lot of the code. I fixed that. Once I did, I got errors in my code stating that Connection is busy with results of another hstmt.
I tracked through the code and found a few places where I call the DB and ask for some ResultSet which is now TYPE_FORWARD_ONLY and then did not close my ResultSet and Statement. The reason I have not been concerned about closing them was due to the fact that I use a Singleton Connection object which returns a single Connection for a thread and then closes that Connection just before the Thread dies. Still, it's better to close them so I'm glad this change made me aware that I was keeping so many open. As I closed those ResultSets I made it further and further in the execution of my code until I got stuck.
In one part of my code, I open a Connection and create a ResultSet. From that ResultSet, I build an array of information to be returned to the caller. Some of the information I have in that array needs to come from unrelated queries so I call the methods that will return me the data I need from those queries. This is where I run into a problem with the Connection being busy:
As you can see, there are a lot of ResultSet objects in all these methods I'm calling which use the same Connection (returned by my Singleton for this sessionID) I'm using in this ResultSet. I can't close the ResultSet because I'm looping over it in my while. Is there an easy way I can solve this?
I found a blog that says you can switch to the SQL Native drivers and it will solve the problem I'm having. I removed my ODBC data source and then set up a new one with SQL Native selected as the driver. Then I tested my data source and all is well. I then ran my java code and now I get: java.sql.SQLException [Microsoft] [SQL Native Client] Shared memory provider: No process is on the other end of the pipe.
There is a socket (PlatformServer) which accepts a connection from a client (PlatformClient). The server then creates a thread for the client. The thread calls an object (PlatformController) which calls many objects. Those object all call CPAWinsDataConnection cd = CPAWinsDataConnection.getInstance() and they create a Connection as Connection con = cd.getConnection("session_id"); So, it's the same connection being used by a single thread until the thread dies - but a different Connection for each thread.
Any thoughts on what I can change to make this work and not sacrifice the speed increases I get from using the best ResultSet.TYPE for my needs?
Firstly you should try to get a better Driver that the JDBC-ODBC bridge. It isn't designed for production use.
Secondly the synchronisation only ensures one thread gets a connection at a time, it doesn't ensure that the connnection is used by one thread at a time. Multiple threads could be serving requests from the same sessionID concurrently and try to run queries on the same connection at the same time.
Using a ConnectionPool would be much better, and allow you to ignore the sessionID
There are plenty of connection pools available such as the popular DBCP and would result in the same performance but open (potentially) a fraction of the connections.
Thanks for the insight. I am happy to change the driver and had intended to before we go live.
It is not possible for more than one Thread to have more than one Connection at this time. The Server "PlatformServer" starts the Socket connection. When the socket is started, the caller asks for a new iframe by sending a command to the PlatformServer. Once that happens, PlatformServer adds the sessionID passed by that caller to a Hashtable PlatformServer manages. Every time a new process is asked for by the Client Socket, PlatformServer checks the Hashtable to see if there's already a PlatformController for this sessionID. PlatformController is created once for each Thread and all Threads have to have a unique sessionID.
This all worked and has been in testing for several weeks as we have been setting up the GUI (web based) which works with this Server. That is, it worked until I changed my flags to TYPE_FORWARD_ONLY.
FYI I'm still focusing on the possibility of a Connection inadvertently being shared between threads at the same time.
So does this mean that the session ID is actually a request ID? otherwise it could still be associated with multiple requests from a single client and cause this problem.
I'm not sure where your sessionID comes from, but if it is not unique then this could also cause threads to collide.
I am still wondering why the connection needs to be stored against the sessionID. ConnectionPooling is the standard solution and omits this problem (assuming connections aren't allowed to leak)
In your initial post, you have the ResultSet open between lines 2 and 75. Between those lines you don't have any explicit references to another Statement or another ResultSet, but you're calling a lot of methods which might do that. So, are they? Some JDBC drivers won't allow you to have more than one ResultSet open per Connection. Others don't mind as long as each ResultSet came from a different Statement. It would be helpful to find out whether that difference makes a difference to your code.
Paul, I put in my comments in the code that I posted that each of those methods do in fact call the same Connection object with the same sessionID and therefore get back the same Connection. Each of those methods then creates a ResultSet. When the ResultSet flag was set to TYPE_SCROLL_INSENSITVE, this was not a problem. But, when I changed to TYPE_FORWARD_ONLY, I had the hang up.
I fixed it by changing the data driver. I am now using JTDS which took care of everything.