Any plans to add socket timeout option in JDBC driver??

Question

 

I have come across several threads stuck with the stack below and they are literally stuck. Other drivers (MySQL and Oracle) have options to timeout from a socket read but MS SQL driver does not seem to have that. Though the root cause of the issue might be environmental (network packet loss, firewalls etc), I would like to timeout the thread and handle it in our application (trying to make the application more resilient to environmental issues.)

 java.lang.Thread.State: RUNNABLE

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@ffd8069
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4433)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:386)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
- locked java.lang.Object@4bf975b2
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

Answer 1

Hi ashish79,

What version of JDBC driver are you using? You can use Microsoft SQL Server JDBC Driver 4.0 which is the latest version. Please see this article for more information: Microsoft JDBC Driver for SQL Server.

Base on your description, you can specify the connection properties “lockTimeout” or “loginTimeout” in the connection URL. The value of “loginTimeout” indicates that the number of seconds the driver should wait before timing out a failed connections. It’s specified as 15 seconds by default. You can increase its value to make the driver wait for more longer time before timing out a failed connection. For more details, please refer to this article: Setting the Connection Properties.

The Microsoft JDBC Driver supports Java EE connection pooling which you also can use. JDBC application code should always close connections explicitly to derive the most benefit from pooling. When the application explicitly closes a connection, the pooling implementation can reuse the connection immediately. Please see this article for your reference: Using Connection Pooling.

You can see this article for your further understanding on JDBC Driver: Connecting to SQL Server with the JDBC Driver.

---

Best Regards, Ray Chen

Answer 2

Hi Chen, thanks for the reponse, but that is not what we are looking for. It is not a login, query or lock that is causing the issue. If you see the stack, the JDBC client is waiting to read from socket (basically looking for response) from Server. This is a blocking read. If I run profiler, sp_who commands on the Server itself, there are NO queries in active state (running, waiting etc) on the Server side. So the Server basically thinks it has already processed the request from client but client however is still waiting.

The issue here is probably environmental (packet loss in the network or something like that). The impact of this is very high in our application as these threads will be holding Java locks which are required by other threads or these threads are associated to MDBs and our queue processing gets stuck.

We want to set read-timeout as a parameter to JDBC connection similar to other parameters you have mentioned http://docs.oracle.com/javase/6/docs/api/java/net/Socket.html#setSoTimeout(int)

We can do the needful to catch the exception and do remediation if we can. At least this will not cause the thread to be stuck forever and processing ing other threads will continue.

We use MySQL and Oracle drivers for the same application and both drivers provide a way to set this. jDTS driver for SQL Server also provides the similar option. We can unfortunately not move to jTDS for supportability / licensing perspective.

Without knowing the JDBC code much, I feel like this is a very simple add-on to a problem that is causing griefs to a lot of people.

Bottom line is, we understand the root cause might be network issues but we want a way to be resilient over network glitches. Simple option to set socketTimeout would really help.

I rest my case :)

PS: Look for description of socketTimeout in these drivers...

MySQL JDBC

http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

jTDS SQL Server Driver

http://jtds.sourceforge.net/faq.html

Search for READ_TIMEOUT for this

http://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtblsh.htm

Answer 3

Hello ashish79,

It seems that Shulei Chen asked what is the version of the Microsoft JDBC driver that you are using to connect. It is an important information because your request could be satisfied ( potentially, no modification can be done for discontinued or depreciated version and it is logical and easily understandable ) only for the most recent version( s).

It would be interesting for to know the version(s) of SQL Server you are using.

Have a nice day  

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Hi Papy, at this point I will take any driver :) I have quite a few "hotsites" with this issue.

We are currently using JDBC Driver 2.0 in older version of our product and JDBC Driver 3.0 in newer version of our product.

We have seen issues with both SQL Server 2005 and 2008. Since this is happening at customer sites, I dont have exact patch level information.

Thanks again for your prompt response.

Answer 5

Hello,

I am fearing that only a person working for the JDBC driver Team could help you.

In fact, jdbc is relying on SqlConnection class and i have never heard something about sockect timeout with SQL Server. It is possible that it is a feature which is not implemented for SQL Server.It is only what i am thinking , no relations with Microsoft.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

Hello,

I have done some search about your problem and i found only one link which could be related :

http://channel9.msdn.com/forums/TechOff/41602-TcpClient-or-Socket-Connect-timeout/

I have seen that no timeout  exists on the level of sockets ( just at the beginning of the article ). It is possible that it is the "hidden" reason of this absence.

If you have a look at http://msdn.microsoft.com/en-us/library/system.data.oracleclient(VS.90).aspx ( related to the ORACLE driver provided by Microsoft ), you will get the same result than me : nothing related to socket timeout. It seems that it is a notion which is unknown or useless for Microsoft .

You may use http://connect.microsoft.com/SQLServer to create a request about the absence of socket timeout. It is the only one method i know to obtain a solution to your problem.

Have a nice day

PS : i hope you will excuse me not to have replied before but i was on holiday. Moreover, i am really posting rarely about JDBC questions ( only to ask more informations to make easier the work of the JDBC specialists or to give some links which seems me related to the thread, that's classical for moderators posting in threads where they are not specialized  ).

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 7

Has a solution for this problem been found yet? A way to set the SO_TIMEOUT on the JDBC driver?

Answer 8

Hi,

we have investigated this issue on Oracle and MsSql. There was a connection initially, but it died some time and was dismantled completely on database side whereas the client still waited gentle for data.

From what i understood, the connection is using a socket to stream data from or to the database connector. This should be the same for Oracle and Microsoft. May be that some system processes (e.g. firewall) cut the socket due to inactivity (e.g. network instability, drop of packages, ...) after a while.

In this case the socket will not get an exception and waits forever on Windows systems. Under Linux there is something like SO_TIMEOUT.

In my opinion, Oracle solved this problem with an additional property 'oracle.jdbc.ReadTimeout' which can be provided as property during DriverManager.getConnection(...). The driver monitors the stream and raises an exception after a while of inactivity. Its the responsibility of the application to set a reasonable time fro this.

This is what we expect from Microsoft: Some similar option to limit maximum wait in not longer responding database connections.

Best regards

Answer 9

We also have the same issue. Our application runs against Oracle, MySQL and SQL Server. For Oracle and MySQL we make use of the socket timeout to make sure connections never get stuck and hang for ever. The jTDS driver for SQL Server supports this and one of our customers switched to it for the timeout reason.

Can you please support this int eh JDBC driver for SQL Server?

Last night there was a power failure that affected some machines, the database server for one. This morning when the server was started back up, some of our java threads remained in that socket read state and never came out of it causing problems. We had to restart the application as a result. A socket timeout option for the JDBC connections would have solved the problem as our code handles database exceptions. We cannot handle threads hanging forever on socket reads though :)

On the other 2 database servers this problem would have been resolved due to the socket timeout options.

Answer 10

This question is from 2013 but i want to share our workaround to set socket timeout for this issue. 

This solution changes the default socket timeout from infinite(0) to your value. You must call Socket.setSocketImplFactory method before creating any socket.

If you call setSoTimeout method for a socket, only its timeout value will change, default timeout value will stay as your value.

This trick doesn't change only connection's socket value, it also affects all sockets default value..

Socket.setSocketImplFactory(new SocketImplFactory() {			
				@Override
				public SocketImpl createSocketImpl() {
					try {
						Class c = Class.forName("java.net.SocksSocketImpl");
						Class c2 = Class.forName("java.net.PlainSocketImpl");
						final Constructor constructor = c.getDeclaredConstructor();
						constructor.setAccessible(true);
						SocketImpl sckImpl = (SocketImpl) constructor.newInstance();
						Field f = c2.getDeclaredField("timeout"); //modifying default read timeout 
					        f.setAccessible(true);
					        f.setInt(sckImpl, 1000); //set timeout to 1000 ms
						return  sckImpl;
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				}
			});

Answer 11

This question is from 2013 but i want to share our workaround to set socket timeout for this issue. 

This solution changes the default socket timeout from infinite(0) to your value. You must call Socket.setSocketImplFactory method before creating any socket.

If you call setSoTimeout method for a socket, only its timeout value will change, default timeout value will stay as your value.

This trick doesn't change only connection's socket value, it also affects all sockets default value..

Socket.setSocketImplFactory(new SocketImplFactory() {			
				@Override
				public SocketImpl createSocketImpl() {
					try {
						Class c = Class.forName("java.net.SocksSocketImpl");
						Class c2 = Class.forName("java.net.PlainSocketImpl");
						final Constructor constructor = c.getDeclaredConstructor();
						constructor.setAccessible(true);
						SocketImpl sckImpl = (SocketImpl) constructor.newInstance();
						Field f = c2.getDeclaredField("timeout"); //modifying default read timeout 
					        f.setAccessible(true);
					        f.setInt(sckImpl, 1000); //set timeout to 1000 ms
						return  sckImpl;
					} catch (Exception e) {
						throw new RuntimeException(e);
					}
				}
			});

Thanks for posting this workaround. I was able to get it to work with the following modification:

        Socket.setSocketImplFactory(new SocketImplFactory() {
            @Override
            public SocketImpl createSocketImpl() {
                try {
                    // Construct an instance of PlainSocketImpl using reflection
                    Constructor constructor = Class.forName("java.net.PlainSocketImpl").getDeclaredConstructor();
                    constructor.setAccessible(true);
                    SocketImpl socketImpl = (SocketImpl) constructor.newInstance();

                    // Set the private "timeout" member using reflection
                    Field timeoutField = Class.forName("java.net.AbstractPlainSocketImpl").getDeclaredField("timeout");
                    timeoutField.setAccessible(true);
                    timeoutField.setInt(socketImpl, timeoutMs);

                    return socketImpl;
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        });

It's a shame that after all this time Microsoft still has not provided a way to set the socket timeout in the SQL JDBC driver, knowing the very serious production issues this has caused.

The following blog post does a good job explaining how all the JDBC timeouts work, and why the higher-level timeouts like transaction timeout rely on the lower-level timeouts like socket timeout: http://www.cubrid.org/blog/dev-platform/understanding-jdbc-internals-and-timeout-configuration/

It also shows that virtually every other major JDBC implementation provides a way to configure the socket timeout.

Answer 12

Hi, 

Socket timeout has been added in the JDBC Driver 6.1.2 preview. It was implemented in this pull request. To learn more about how to get the preview driver, please view this blog. 

Thanks,

Andrea Lam