Does SQL server automatically rollback the changes on connection lost?

Question

I have a sql server (server1) and webservice server (server2). server2 has this below code. which get a result request from client and update the database.

 try
        {
            AppLogger.DebugFormat("Entered into save result - [{0}]", result.ID);
            int retry = 0;
            while (++retry <= 5)
            {
                try
                {
                    using (var oConnection = new SqlConnection("Connection string"))
                    {
                        oConnection.Open();
                        AppLogger.Debug("Saving data into db");
                        oConnection.Execute("storedproc1", new
                        {
                            param1 = Convert.ToInt32(result.value1),
                            param2 = Convert.ToInt32(result.value2),
                            param3 = result.value3=="Success",
                            param4 = result.vaue4
                        }, commandType: CommandType.StoredProcedure); 
                        AppLogger.DebugFormat("Save done with [{0}] try", retry);
                        break;
                    }
                }
                catch (SqlException sx)
                {
                    if (retry == 5)
                    {
                        AppLogger.Debug("sql exception occured");
                        throw;
                    }
                    else
                    {
                        AppLogger.ErrorFormat("Exception occurred [{0}] time(s), going to retry again after a minute", sx, retry);
                        System.Threading.Thread.Sleep(1000 * 60);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            AppLogger.Error("Unable to save result", ex);
            throw;
        }

webservice server (Server2) happend to face a blue screen error and died. We restarted it and found the below log information from the application.

 10:32:41.046 Entered into save result - 100023
 10:32:41.062 Saving data into db
 10:32:41.062 Save done with 0 try

 10:32:45.233 Entered into save result - 100024
 10:32:41.248 Saving data into db
 10:32:41.248 Save done with 0 try

But the sql server (server1) doesn't have this update.

Below is my stored procedure

Alter Procedure storedproc1
@Param1 int,
@Param2 int,
@Param4 varchar(2000),
@Param3 bit
AS
SET NOCOUNT ON

BEGIN   
     Declare @param5 varchar(30)
     select @param5=col1 from table1 where col2=@param1 and col3=@param2
     UPDATE table1 set col4=@param3, col5=@param4 where col2=@param1 and col3=@param2

     IF not exists (select 1 from table1 where col1 = @param5 and col5 is null and col4 is null)
     BEGIN
        UPDATE table2 set col2='statuschange'
        where col1 in (select distinct col6 from table1 where col1=@param5)
     END
END  

Could some one point me why the application server say save done and the sql server doesn't have the update?

Does sql server rollback the change on connection lost?

By the way, I use dapper to talk to my database. Log4net common logging for log.

Thanks for your time, Esen

Answer 1

In general, if at the time the connection is lost a transaction is open, that transaction will be rolled back completely. On the other hand, if a transaction was committed, its changes will survive even a server crash right after the commit.

Your code is not showing any transaction handling. If SQL code is executed without explicit transactions, each statement runs inside its own automatic transaction. So each statement that finished will be preserved.

The behavior you are seeing points to transactions being used and not cleaned up properly. That, together with transaction pooling can lead to unexpected behavior.

One way to track this would be to execute a SELECT @@TRANCOUNT; at the beginning of your using(var oConnection) block. If that ever comes back higher then expected, you have a problem. It either needs to be always 0, or if dapper is set to execute code inside of a transaction it needs to be always 1. Any value greater then the "default" points to a transaction leak.