SqlServer wedged connection due to network drop

We have two directly related problems that can be reduced to a very simple piece of code. Suppose machine 1 hosts the application, and machine 2 hosts the database, connected by ethernet via a hub. We are going to simulate network problems by unplugging network cables from the hub.

You would probably say "make your network reliable". So do I. I need to prove it's not first by explicit capture of the problem before the customers will believe.

We cannot solve this problem with timeouts as we have some /very/ long running queries and non-queries. Yes, some of them really do take an hour or more, and users won't put up with staring at a frozen session long enough to get the real error. They kill it instead.

using System.Data;
using System.Data.SqlClient;

public class test {
    public static void hang1()
    {
        using SqlConnection oConnection = applib.getConnection() // returns an open connectin
        {
            using SqlCommand oCmd = new SqlCommand("WAITFOR DELAY 00:01:00", oConnection)
                oCmd.ExecuteNonQuery(); // unplug cable between hub and database server when in this call and this call never returns
        }
     }

     public static void hang2()
     {
            using SqlCommand oTCmd = new SqlCommand("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", oConnection)
                oCmd.ExecuteNonQuery();

            using oTransaction = new SqlClient.SqlTransaction
            {

            using SqlCommand oCmd = new SqlCommand("SELECT max(id) FORM Table1")
            {
               oCmd.Transaction = oTransaction;
               oCmd.ExecuteScaler();
               System.Threading.Thread.Sleep(60 * 1000);
               // Disconnect the cable between the hub and the application server here
               // Now table Table1 is locked and will remain locked until the transaction
               // is manually killed from the database server.
               oCmd.ExecuteScaler();
            }
            }
      }
 }

We are seeking a solution to detect the transaction is stuck without having to set a timeout on it. If I were designing my own TCP/IP protocol, I'd design a heartbeat into it so that no response for a sufficient number of seconds = conclude dead, bail out, clean up. I'd like a way to accomplish the same idea, that is turn the quiet hang into a noisy crash so the cleanup code can clean it up.

Answers


This already exists. it is called keep-alive.

See the following for information:

SQL Server will probe connections with KeepAlive packets and should detect quickly within a few minutes if the client is no longer present.

However this doesn't help you - you want the client to send keepalives to make sure the server is still beavering away.

There seems to be no supported way of doing this. If you wanted to enable TCP KeepAlives on your client socket you would have to use Reflection or unsafe code to locate the actual TCP Socket and use WSAIoctl to enable keepalives. A probably better solution is given in my other answer.


Need Your Help

How can I handle onTap events on path based map overlays?

android path overlay android-mapview

I'm currently making an android map based app. The purpose of this app is to display complex overlays on a mapview. To do this I'm using custom overlays and overriding draw() function.

jquery array of checkboxes contains the last one checked

php jquery

I have a group of checkboxes that I'm trying to pass through POST via AJAX/PHP. When I fill the array it's only picking up the last one.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.