none
How can I set SO_TIMEOUT on the sockets used by the JDBC 2005 driver?

    Question

  • Hello,

     

    One of our customers is experiencing a problem that makes me think there is a network problem, because we make a request and never return from the driver, but the transaction is still open on the server awaiting the next command (and holding locks blocking other processes).  I see a number of other driver writers have described similar problems, and their "workaround" is to set SO_TIMEOUT on the sockets (setSoTimeout) they create which is a last defense so that the transaction won't block forever.

     

    I do not see this in the API documentation- how can I set this?  Many other drivers use a connection string parameter, do you offer the same?

     

    Is there any logging I can enable that can show me for sure that the driver is waiting indefinately on socket.read (thats not terribly expensive)?

     

    Thanks,

     

    Steve

    Monday, July 09, 2007 8:01 PM

Answers

All replies

  • Hi Steve,

     

    From the problem description above it sounds like you are looking for a way to set the timeout on a query execution.  This can be done using the "setQueryTimeout" API on the SQLServerStatement object.  For additional information please refer to the following MSDN topic: http://msdn2.microsoft.com/en-us/library/ms378081.aspx

     

    If you are requiring a connection timeout setting, you can specify "loginTimeout" on the connection URL or on the SQLServerDataSource object.  For additional information on the connection URL properties please refer to http://msdn2.microsoft.com/en-us/library/ms378988.aspx.  If you are using the SQLServerDataSource object, please refer to http://msdn2.microsoft.com/en-us/library/ms379035.aspx for additional info.

     

    The Microsoft SQL Server 2005 JDBC driver has built-in tracing using the java.util.logging API.  For additional information about the different levels and categories, please refer to the following topic: http://msdn2.microsoft.com/en-us/library/ms378517.aspx

     

    HTH,

    Jimmy

    Tuesday, July 10, 2007 12:28 AM
    Moderator
  • Thanks but I need clarification-

     

    The description of this method states that this is the amount of time the query will be allowed to execute.  Is this enforced by the client or by the server? 

     

    Also- I see in the connection string properties there is a lock timeout specified as:

     

    The number of milliseconds to wait before the database reports a lock time-out. The default behavior is to wait indefinitely. If it is specified, this value is the default for all statements on the connection. Note that Statement.setQueryTimeout() can be used to set the time-out for specific statements. The value can be 0, which specifies no wait.

     

    So this makes me think that setQueryTimeout and lock timeout connection property are the same thing, but they are NOT the same thing.  In my case, our customer is experiencing some kind of network condition that is not being propegated back to our code.  The result is we have a transaction that is open (blocking) on the database that is waiting for a commit or rollback, but the driver never returns to our code.  So this transaction ends up blocking other transactions.  We have set lock timeout on the connection and this results in the second, third, etc. transactions to eventually fail with a lockTimeout exception, but the original transaction stays open still blocking.  So what we want is a socket timeout (ex. SO_TIMEOUT in win32 api or socket.setSoTimeout in java) to be able to set this.

     

    If setQueryTimeout will accomplish then great, but if this just ends up adding a lock timeout then this wont solve the problem.  Note as well that whatever this magical network condition is seems to be a problem for many database drivers running in windows (jtds, ibm db2, etc) and they all implement a socket timeout as a workaround.

     

    Thanks,

     

    Steve

    Tuesday, July 10, 2007 1:22 AM
  • Thank-you for the feedback that the description of the lockTimeout property makes it sound like the same thing as queryTimeout.  We will look into how we can better call out the distinction in future documentation updates.

     

    The lockTimeout property is used to notify SQL Server how long to wait before sending an object lock error back to the client.  So, the timeout is driven by SQL Server.

     

    The queryTimeout property on the other hand is used to tell the JDBC driver itself, how long it should wait for a query before timeout error occurs.  This is a client side driven timeout.

     

    So, in your scenario, you will likely want to use queryTimeout instead.

     

    HTH,

    Jimmy

    • Proposed as answer by Kaity Monday, January 04, 2010 7:58 PM
    Tuesday, July 10, 2007 6:38 PM
    Moderator
  • I hava the same problem. my server 's os is  windows 2003 server  ,my database is sql server 2005,my cilent's os is linux,

    I simulated a network outage , I unplug the network cable server , and then quickly plug the Internet cable , the client may hang database connection , I found that the client does not know the connection has been interrupted , socket there is no set SO_TIMEOUT, ask how to solve this problem, I have appeared many times in practice this issue is very upset

    socket read is blocking, queryTimeout does not work
    Monday, February 07, 2011 3:28 AM
  • I am seeing similar issue with database waiting for a transaction to commit, where as client is stuck on socket read :-

     

        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(SocketInputStream.java:129)
        at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1525)
        at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3274)
        - locked com.microsoft.sqlserver.jdbc.TDSReader@1ff4b8c
        at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4437)
        at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4389)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(SQLServerConnection.java:1457)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

     

    Has anybody been able to at least timeout the socketRead so we can catch that and work around that?

    Thursday, April 28, 2011 9:05 PM
  • We experienced the same problem with client running on Linux and MSSQL Server 2005. JDBC connections from multiple processes on these Linux host got stuck in

    at java.net.SocketInputStream.socketRead0(Native Method)

    at java.net.SocketInputStream.read(Unknown Source)

    at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:1525)

    at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3274)

    ...

     

    Is the any way to workaround the problem instead of attempts to get underlying socket from Connection implementation via reflection and calling setSoTimeout() there?

    Wednesday, June 15, 2011 9:48 PM
  • Can somebody from MS advice pl?

    We have now a couple of clients reporting this issue and we see no way to get around this other than recycling the application itself, which is NOT AN ACCEPTABLE SOLUTION in a production type environment. 

    I have gone through several forums with a lot of people experiencing this issue but no solution mentioned anywhere. Is MS SQL 4.0 Driver going to help in this case? I agree this could be an issue in the network (packet loss etc) but the blocking read thread CANNOT stop for ever. In our application that thread is holding Java lock which is needed by other threads and application comes to a dead halt because of this.

    We will appreciate some way to set socket timeout ..... 

    Thursday, March 29, 2012 1:29 PM
  • Has this bug been addressed yet? This bug has been known for > 5 years.
    Tuesday, January 08, 2013 10:17 PM
  • Sheesh! Looks like MS doesn't care about it or us. Come on and fix this problem as it can cause severe problems to applications like ours. Oracle & MySQL both have this feature. Last night we had issues again because of a power failure and we had several Java threads all stuck in socket read forever since no socket timeout option exists for SQL Server. We had to restart the application which the customer was not happy about. Maybe we should convince them to move to another database vendor.
    Thursday, September 12, 2013 12:14 PM