Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC connections in batch; SQLServer 2008;JSQLConnect 5.7; incidentally no connection

 
R Bieringa
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I'm having trouble with pure JDBC-connections to a SQLServer 2008 Instance. A program running from the command-line.
I'm getting the following error:
SQLException in createConnection: Failed Logon:com.jnetdirect.jsql.JSQLException: Failed connection to named instance [SOMSSQL69] [java.net.SocketTimeoutException: Receive timed out] url:jdbc:JSQLConnect://DEVELOP11/instanceName=DEVMSSQL69/domain=EUROPE/databaseName=Testload_MIDO_Config/loginTimeout=3600/user=test1/password=TeStInG

I'm using JSQLConnect driver.
The program can have multiple threads. So more concurrent connections possible. Most of the time there is no problem. But sometimes there is. I've set the loginTimeout on the drivermanager to 3600 seconds.
( DriverManager.setLoginTimeout(3600); )
I try getting connections using a url.
Since there are 2 databases used (2 different url's) AND a jar is also included that uses Spring to instantiate it's own datasource on one of those databases that also incidentally crashes I'm not sure where to look for the error.

Program uses:
A jar that contains Spring a bean with datasource to database A to handle it's own work.
Multiple concurrent connections to database A.
Multiple concurrent connections to database B.
(Database A and B are on the same instance, by the way)
The databases have enough "resources" (connections allowed etc.) to deal with the load.

The total solution is about 53 Mbytes (as a jar).
It spawn's threads, all having their own workload.

I've implemented a class that for each thread handles the requests for connections and closing of the connections.
Very incidentally I get the message mentioned.
Any suggestions as where to look for the problem ?

Thank you in advance.

Ronald








 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd suggest checking database logs for potential error messages.

Are all your threads connecting at the same time? I'd say there is a difference between a database supporting many connections in general as opposed to supporting the same number of simultaneous connection attempts.
 
R Bieringa
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin,
Good suggestion. I'll do that too well, others will have to, I'm not authorized. We've looked "live" at what was happening, but the guys said they couldn't really help since there wasn't a connection.
I think they have not checked the logs (they didn't tell me about that).

Since you ask, there should be no more than 20 connections simultaneous connections at any time. But I've been thinking about it, it could be something similar.
You see, the program starts for example 5 threads (that's configurable). Every thread has one "bean" accessing the database (spring datasource) which is used by the library I need to use. That could lead to multiple connections of course. But maybe the main issue is that also during processing a thread does about this:
process:
for 10 items:
1...do some work (that's where the library is used (and thus the bean)
a number of times (let's say 10)
2--do some more work
3--get connection - do sql things - close connection

So step 3 occurs rather quick (although sequential). Maybe the database get's too much connect/disconnect requests quickly one after the other (which could be influenced by a number of things, like network traffice, program speed etc.) so that it maybe sometimes "stalls" and does not accept new connections in that case.
You think it's worth a shot to try to reuse the connection in the thread itself ? ( a sort of one-connection datapool). If I try to get the connection, verify it's stil there (by a select 1 from table x or somehing) and only throwing away the existing one if it's not good any more and trying to get a new connection only in that case ?

Thank you for your suggestion and I would appreciate your opinion upon what I stated in the lines above.

Best regards,
Ronald


 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm really just guessing, but as I've already hinted before, I'd try to make sure not too much connection attempts happen at the same time. Perhaps make the method which connects to the database synchronized?

On the other hand, 5 simultaneous connection attempts does not sound that bad.

And since you've mentioned the connection pool: why not employing a real connection pool? It can take care of verifying the connections handed out by itself, and you could end up having less connections overall, if the timing of operations in threads does not get very unlucky.
 
R Bieringa
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin,

I think I've solved it (sorry for the late reply). It's the database (of maybe the middleware, but less likely) that gets disturbed by too many connects and disconnects.
First I implemented "connection-reuse": That solved it for MY part of the solution: no more timeouts there.
Then the problem "moved" to the library I must use. I used a DriverManager datasource there. After changing that to a SingleConnections datasource .... the problem disappeared.
I can only "deduct" that the number of connect/disconnect request gives the database too much work. Unless it's the JConnectDirect driver that's not waiting long enough for disconnects to complete.
By the way, it wasn't very difficult to create a "reusable connection object", that checks it's connections validity etc. Yes I know, people always tell me "...better to steal some good stuff than invent something bad yourself....". Might be true, on the other hand, it's even better to invent something good yourself. Besides, one learns from it. But in bigger applications with multiple connections, I go with using "the good stuff from others". The problem than shifts from connection to connection-housekeeping. Threads, synchronized etc. It's better to use 'proven' stuff in such cases.

Thank you anyway!

Regards,

Ronald
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic