aspose file tools*
The moose likes JDBC and the fly likes JDBC Connections Freezing with Multiple Users Accessing DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Connections Freezing with Multiple Users Accessing DB" Watch "JDBC Connections Freezing with Multiple Users Accessing DB" New topic
Author

JDBC Connections Freezing with Multiple Users Accessing DB

Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
I wrote a fairly basic SQL database-driven Java application that tracks misc. data about different people/businesses. It seems to work OK when there is only one user using the software, but when multiple users are trying to access data the application will freeze. No errors are generated, but the application usually has to be manually killed. Specifically, the user will enter a search term and click a button (which executes the SQL query), and the button will stay down as the application freezes. The same thing happens on multiple different sections of the application with different data and different types of SQL queries.

My only hypothesis so far is that the way I handle my JDBC connection and result set may be preventing an additional user from making a connection. The result set and connection can be open for an indefinite period of time while the user browses the results. Then, when the user selects a result and wants to do another query, the currently open result set and connection are closed.

I've searched everywhere on the repercussions of leaving the connection open, but I really can't find anything definite. If this is indeed the problem, I would have to completely redesign the back-end of the application (which would take me several days). So I want to be completely sure before I redesign the whole system. Thanks for the input!
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Scott,

Just a few questions:
-are you sure it's the back-end(SQL/JDBC - part) that freezes
(do you have any logging to prove this?)
-do you use any framework, connection-pooling, what server/database do you use?
-how many simulteanous users does it take to freeze your application: 2, 5, 10...
-are all your sql-queries select-statements or ...

Herman
PS I always close connections, resultsets (and statements) in my programs,
I'm curious if you had a reason not to.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Also, which database are you using and have you tried any of this diagnostic tools to see if there is some sort of blocking going on?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Herman Scheltinga:
hi Scott,

Just a few questions:
-are you sure it's the back-end(SQL/JDBC - part) that freezes
(do you have any logging to prove this?)
-do you use any framework, connection-pooling, what server/database do you use?
-how many simulteanous users does it take to freeze your application: 2, 5, 10...
-are all your sql-queries select-statements or ...

Herman
PS I always close connections, resultsets (and statements) in my programs,
I'm curious if you had a reason not to.


-I'm sure it's the back end.
-I'm not using any framework, not using connection pooling, and (unfortunately) using Microsoft SQL Server 2000.
-2 users freeze the application.
-I use four different types of SQL statements: select, update, delete, insert. In order to get to a point in the application to use update or delete, you have to first successfully execute a select statement. The select statement is causing the freeze, so I don't know if the update or delete statements would cause the same problem.
-I suppose that leaving the result set and connection open is just sloppy coding, as I should probably copy the result set into a two-dimensional array and close the connections. But based on the way the application is written, this would be a major headache. Hence, I want to be sure this will fix my problem before implementing the change.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

What does SQL Server say is the cause of the block? Are there processes in the WAITING state?
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Paul Sturrock:
What does SQL Server say is the cause of the block? Are there processes in the WAITING state?


Here's all I'm getting in the log entries for two freezes by both users:

2007-04-26 10:05:26.25 logon Login succeeded for user 'KSHRC\ch3649'. Connection: Trusted.
2007-04-26 10:05:53.89 logon Login succeeded for user 'KSHRC\sms0123'. Connection: Trusted.


So I'm guessing the error isn't coming from the SQL server, but something in the ODBC bridge. I really don't know.
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Scott,

can you post (some)your code?
Do you use any locking? (maybe a deadlock)

Herman
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
I'm not using threads, so I don't think it's a deadlock. Here is the code for the query that is freezing. Thanks so much for looking at this!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Here's all I'm getting in the log entries for two freezes by both users:

2007-04-26 10:05:26.25 logon Login succeeded for user 'KSHRC\ch3649'. Connection: Trusted.
2007-04-26 10:05:53.89 logon Login succeeded for user 'KSHRC\sms0123'. Connection: Trusted.

What log is this? Is this the SQL Server log? Have you run sp_who (or sp_who2)
to check the state? Remeber that SQL server has deadlocks, from which it should recover, and blocks, from which it can't (at least quickly since the block may be in the client)


but something in the ODBC bridge.

Why are you using the JDBC-ODBC bridge?
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
It's the SQL Server error log. I'm not too familiar with stored procedures, so I'm not sure how to do an sp_who. Can you give me the syntax I need to create that stored procedure?

I'm using the JDBC ODBC driver because it's my understanding that you have to have a custom driver written (by the DBMS vendor) to query the database directly with Java. If this isn't correct, please let me know how to bypass it! Thanks a bunch.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


It's the SQL Server error log. I'm not too familiar with stored procedures, so I'm not sure how to do an sp_who. Can you give me the syntax I need to create that stored procedure?

When the freeze occurs, try:

from your SQL window and you should get a snapshot of all the processes active on the instance. If the block is on SQL Server you should be able to find the clocking spid from here.


I'm using the JDBC ODBC driver because it's my understanding that you have to have a custom driver written (by the DBMS vendor) to query the database directly with Java.

Correct. MS supply one (check MSDN). jTDS supply a better one. The JDBC-ODBC bridge is not really worth persisting with, no pun intended...*



(* OK, maybe it was)
[ April 26, 2007: Message edited by: Paul Sturrock ]
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
OK, here is the SP_WHO2 generated by me while the application is frozen during a SQL query:



I'm not sure how to decipher it, but I'm guessing that the fact that is says "sleeping" isn't good. Also, when I run the SP_WHO2 again, nothing changes...not even the CPU time. Any ideas?

I notice the the BLKBY column shows SPID 54, which is the query of the other user currently in the application. So apparently that user is blocking me? Here's SPID 54:


[ April 26, 2007: Message edited by: Scott Florez ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

A sleeping status is fine. It just means that particular spid has nothing to do right now.

The blkby column shows which spid is blocking your process. spid 54 is awaiting a command which means it is waiting for the client to do something, not another process in the server.

What it is waiting for is harder to say. I'd change to using a proper type-4 driver and look to change how you handle connections. Both are guesses, but since SQL Server is telling you that it is the client that is the problem - possibly the server is waiting for the client to free up some resource? You might get away with just closing the result set even.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

My experience suggests this happens when you are not closing your statements and/or resultsets. The JDBC driver will just keep creating new ones until it reaches its max limit. Then it will start blocking because it has run out. The code will have to wait for the GC to run before the JDBC driver realizes it can close some of those old statements/resultsets.

You should be sure to close them in a finally block on your own every time, if you are not.
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
So if I am going to change my application to immediately close connections after the query is made, what is the recommended way to allow iteration through the results? Apparently if you close the connection, the result set is closed too. Should I copy the entire result set into a two-dimensional string array to allow iteration through the results? Or is there a better way? Thanks.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

No matter what you do, you more than likely should be reading the data in the ResultSet immediately. If you can use it immediately then do. Otherwise, definitely copy it into an array or list or something. You cant use result data directly anyway. You need to turn that data into a class representation don't you?

You don't need to close the 'connection' right away. Just the ResultSet and/or the statement. Close everything ASAP.

For starters, the method that you posted returns without having explicitly closed the ResultSet or the statement. Just be sure to close them both before that method returns. Thats a good starting point. Use try-finally.
[ April 28, 2007: Message edited by: Mr. C Lamont Gilbert ]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42275
    
  64
The JDBC/ODBC bridge is not safe to use with concurrent accesses. If you need to support simultaneous accesses, switch to jTDS.


Ping & DNS - my free Android networking tools app
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
I've decided to try jTDS before I completely rewrite my application. However, I can't figure out the URL I need to use to make this thing work. I've been trying to hours, and I've gotten a whole range of errors. Here's what I'm currently trying:



I've gotten a whole range of errors depending on how I configure the URL when I try to connect to the database. Can anyone help me get the URL right? I've read and re-read the documentation and can't figure it out. The information is as follows:

server name: khrcs2/bkupexec
database name: Mail_List
port: 1622
[ April 30, 2007: Message edited by: Scott Florez ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

If you're using SQL Server 2005, verify if you have enabled TCP/IP protocol. It's not enabled by default.
Here are the instructions.

I'm unsure if khrcs2/bkupexec is a valid server name. For testing purposes, could you replace this with the server ip address?
Regards, Jan
[ April 30, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Jan Cumps:
If you're using SQL Server 2005, verify if you have enabled TCP/IP protocol. It's not enabled by default.
Here are the instructions.
Regards, Jan


It's SQL Server 2000 and I have verified that TCP/IP is enabled.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

And when you replace khrcs2/bkupexec by ip address?
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Jan Cumps:
And when you replace khrcs2/bkupexec by ip address?


Still the same problem. The error I'm currently getting is:

Network error IOException: Connection refused: connect
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

There's a troubleshooting guide on the jTDS site for this problem.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Or (I'm still puzzled by the forward slash in your connect string. Is bkupexec by any chance the sqlserver instance name?), try this url

jdbc:jtds:sqlserver://khrcs2:1622/Mail_List;instance=bkupexec
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Jan Cumps:
Or (I'm still puzzled by the forward slash in your connect string. Is bkupexec by any chance the sqlserver instance name?), try this url

jdbc:jtds:sqlserver://khrcs2:1622/Mail_List;instance=bkupexec


I'm not entirely sure what the terminology for the bkupexec is. I asked the guy who administers the DB, and he said that it's a part of the server name. But he's just a tech support guy who does a bit on the DB.

Anyway, I tried that string and I get a different error:

I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

And regarding the troubleshooting guide: I've been through that several times. It doesn't appear to be a network configuration issue, as this same implementation works fine with the JDBC-ODBC driver.

Thanks for all of your help!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

We're close. Your last error is because you do not include user name and password. The driver wants to do nt security.

This person has documented the solution:
Change your connect string to:
jdbc:jtds:sqlserver://khrcs2:1622/Mail_List;instance=bkupexec;user=XXX;password=YYY

or set user/pwd in con = DriverManager.getConnection(dbURL,"XXX","YYY");
[ April 30, 2007: Message edited by: Jan Cumps ]
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Jan Cumps:
We're close. Your last error is because you do not include user name and password. The driver wants to do nt security.

This person has documented the solution:
Change your connect string to:
jdbc:jtds:sqlserver://khrcs2:1622/Mail_List;instance=bkupexec;user=XXX;password=YYY

[ April 30, 2007: Message edited by: Jan Cumps ]


I saw similar posts about including the username and password in the URL string, but what I read said you didn't have to do that if you include the username and password in the following statement:



When I used the JDBC-ODBC driver, leaving the username and password blank caused the authentication to be done based on the username and password of the user currently logged into the domain. That's what I want to do. Can that be done with jTDS so I don't have to hard code a username and password?

By the way, it is working now!!! Thanks so much for all of the help. Now if I can just get my authentication down the way I want it, I can check to see if the jTDS solution solves my original problem (25 replies or so ago). :-)
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

To make windows authentication work, You need to put the ntlmauth.dll (included in the jTDS zip, subdir SSO) on your system:

from README.SSO:

In order for Single Sign On to work, jTDS must be able to load the native
SPPI library (ntlmauth.dll). Place this DLL anywhere in the system path
(defined by the PATH system variable) and you're all set.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

Originally posted by Scott Florez:
I've decided to try jTDS before I completely rewrite my application. However, I can't figure out the URL I need to use to make this thing work. I've been trying to hours, and I've gotten a whole range of errors. Here's what I'm currently trying:
...


Your application is written incorrectly.
Lessons will follow...


You can rewrite it now, or rewrite it later after you add even more improper JDBC code.
[ April 30, 2007: Message edited by: Mr. C Lamont Gilbert ]
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Mr. C Lamont Gilbert:


Your application is written incorrectly.
Lessons will follow...


You can rewrite it now, or rewrite it later after you add even more improper JDBC code.

[ April 30, 2007: Message edited by: Mr. C Lamont Gilbert ]


Could you enlighten me as to how it should be written? Should I copy the entire result set into a two-dimensional string array and then close the connections? Or is there a better way?
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

You can, but thats up to you. Depends on how soon you intend to use the data.

Your problem here is you are exiting the method without closing the resultset and/or statement.

Here is an example from some of my code


Its strictly not necessary to close the ResultSet if you are going to close the Connection the ResultSet came from so soon. I do it just for consistency and habit. Most of my code though you will see a 'statement' where you see the connection above. So typically you may have the connection open for a long time, but you will open and close statements all the time. In any event, you should never exit the method without explicitely closing a connection/statement/resultset IF that object is not useable after the method exits. Close them as soon as they are no longer practically useable.

As you can see the connection is closed and the result set is closed, no matter what, when this method exist. Even if an exception is thrown, the statement and resultset are cleaned up first.
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Thanks for the explanation. I've heard that before. However, is there any particular reason why you have to close the connection immediately? I've never been able to find a concrete reason why. The point of me leaving my connection open is so that I can manipulate the result set for awhile. Then I later close the connection.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

No, you don't have to close it immediately. You close it when your done with it. My connections typically stay open for almost the lifetime of the application. My statements though are closed much sooner. And the resultsets closed very soon.


I think the fundamental issue is dangling statements. If you just keep creating new statements the JDBC driver will eventually get stuck without the ability to create more. So you want to ensure your statements get closed.

As I said, the flaw in your code above is you do not close the resultset ever after you leave the method. There is no way you can still use the resultset after the method exits so as a minimum it should have been closed before the method exited.
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Mr. C Lamont Gilbert:
No, you don't have to close it immediately. You close it when your done with it. My connections typically stay open for almost the lifetime of the application. My statements though are closed much sooner. And the resultsets closed very soon.


I think the fundamental issue is dangling statements. If you just keep creating new statements the JDBC driver will eventually get stuck without the ability to create more. So you want to ensure your statements get closed.

As I said, the flaw in your code above is you do not close the resultset ever after you leave the method. There is no way you can still use the resultset after the method exits so as a minimum it should have been closed before the method exited.


I actually do use the result set after the method is exited in several different classes. Then, I will eventually close the result set from another class when I am done with the result set.

Basically what I have is a single SQL select query that returns several pages worth of results. I open the results page and show the first x records from the result set. Then the user can page through the results, which causes the application to continue to increment through the result set. Finally, the user will select a single result to view or edit, at which point I close the result set.

So, if I am to close the result set immediately after the query, what is the proper way of coding my application to do what I just described? Should I copy the entire result set into a two dimensional array and manipulate it from there? Or should I copy the result set into a temporary table? I've done lots of research on this, and apparently there isn't any good way of doing it.

I really appreciate the input of everyone else who has helped with this topic. At this point, my application is working properly with the jTDS driver. Changing from the JDBC-ODBC driver to the jTDS driver corrected the problem that caused me to originally create this thread. Thanks everyone!
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

In a purely OO sense it is not likely you want to be passing around the resultset anyway. Probably you want just a few classes to process that. Else your going to have SQLExceptions all over the place. And other maintainance difficulties.

I would certainly copy the data from the resultset into a useable array of some form. Then close the resultset and use the array at your leisure. Otherwise you are unnecessarily holding onto JDBC resources. Eventually it will bite you.

As a strategy I would try to put all the JDBC processing inside a single package, and all the useage of the data retrieved from the database in a seperate package. There should be no JDBC classes outside of the JDBC package .

Right now what is happening is your data is stored in the resultset. Even after your done using it, its still stored there, and it only exits when the JVM decides to do a GC and sees that. In the mean time you are opening new resultsets and using them and never closing them. JDBC is running out of resources. It has no way to reclaim them. JDBC should not be used as a cache or a holdover for data. get the data out of the resultset right away.

And steer clear of those terrible patterns that have the GUI classes reading from the JDBC resultset that you see so often in those quick database GUI building tools...
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42275
    
  64
I'm not sure if jTDS supports this, but state-of-the-art drivers allow you to get a RowSet instead of a ResultSet, which can be "decoupled" from a statement or connection, and transported around the application.

If not, go with Lamonts suggestion and copy the data into separate data structures.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

There's also an article on MSDN on how to do pagination in SQL Server.

It's not as straightforward as in MySQL, but it works. Look for the section User-Specific Records.

For advantages and disadvantages for this approach, there is a nice overview on JavaRanch.
Regards, Jan
[ May 02, 2007: Message edited by: Jan Cumps ]
Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
Originally posted by Jan Cumps:
There's also an article on MSDN on how to do pagination in SQL Server.

It's not as straightforward as in MySQL, but it works. Look for the section User-Specific Records.

For advantages and disadvantages for this approach, there is a nice overview on JavaRanch.
Regards, Jan

[ May 02, 2007: Message edited by: Jan Cumps ]


I've actually seen this overview on the advantages and disadvantages of each method all around the web. The problem is that none of the methods seem to be ideal; they all have major drawbacks. Also, this overview doesn't consider the option of copying the result set into a local data structure such as a two-dimensional array. I'm leaning towards this route, but I wanted to make sure that is a good way of doing things. The initial copy into the local data structure might be a little slow, but after that I think things should run rather smoothly. What do you guys think?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Also, this overview doesn't consider the option of copying the result set into a local data structure such as a two-dimensional array

The point with pagenation is it is a solution when you are working with result sets that are too large to sensibly load into memory in one go, so instead you break the single query into a number of queries and work with each collection of results on demand. Whether you work with the data in a ResultSet, RowSet of some Collection (a 2D array would work but is probably not the easiest structure to work with) is less significant.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Connections Freezing with Multiple Users Accessing DB