SQL Server是否会自动回滚连接丢失的更改？

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.

我有一个sql server（server1）和webservice server（server2）。 server2有以下代码。从客户端获取结果请求并更新数据库。

 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.

webservice服务器（Server2）发生面对蓝屏错误而死亡。我们重新启动它，并从应用程序中找到以下日志信息。

 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.

但是sql server（server1）没有此更新。

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?

有人可以指出为什么应用服务器说保存完成并且sql server没有更新？

Does sql server rollback the change on connection lost?

sql server是否回滚连接丢失的变化？

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

顺便说一句，我用dapper与我的数据库交谈。 Log4net常用日志记录。

Thanks for your time, Esen

谢谢你的时间，Esen

1 个解决方案

#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.

您的代码未显示任何事务处理。如果在没有显式事务的情况下执行SQL代码，则每个语句都在自己的自动事务中运行因此，将保留完成的每个语句。

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.

跟踪这个的一种方法是执行SELECT @@ TRANCOUNT;在您使用（var oConnection）块的开头。如果这回到了预期的高位，你就会遇到问题。它需要始终为0，或者如果dapper设置为在事务内部执行代码，则需要始终为1.任何大于“default”的值都指向事务泄漏。