如何等待SQL Server数据库回滚完成？

Question

在使用ADO.NET SqlClient类时，有几种方法可以回滚事务。通过显式调用SqlTransaction.Rollback，或具有命令超时，或事务范围内的事务超时等。

但是，在使用SQL Server (2012或更高版本)时，如何检测此回滚何时完成？对于长时间运行的事务，回滚可能需要很长时间，而且通常会占用大量的数据库IO，因此立即重试该事务可能不是明智之举。在我们的情况下，我们希望等待回滚完成。

(在我们的特定情况下，回滚是由于使用TransactionScope类的事务超时而发生的，但我更喜欢一种适用于任何类型的回滚的方法)。

我看过sys.dm_exec_requests，它一开始看起来很有前途。它最初的命令类型为KILL/ROLLBACK，并报告了进度百分比。(如果我从原始事务中捕获了SPID，我就能够轮询这个表并等待它完成)。然而，大约进行到一半时，我注意到它改为AWAITING COMMAND (同时仍在执行回滚)。

我还查看了KILL <SPID> WITH STATUSONLY。如果没有进行回滚，这似乎会生成一个错误，因此它也可以用于轮询方法，但是我注意到，如果我从SSMS运行BEGIN TRAN ... ROLLBACK TRAN批处理，则会报告一个错误，指出回滚时没有进行回滚。在本例中，dm_exec_requests表将ROLLBACK TRANSACTION报告为命令类型。

那么，如何才能以可靠的方式等待回滚完成呢？

Answer 1

您可以使用基于spid的sp_who查看事务活动。列status将告诉您事务的当前状态，并在完成时从rollback或disapear更改。

如果您希望获得有关.NET的反馈，请使用SqlChangeMonitor(example)缓存您的结果，并等待您的SqlChangeMonitor通知来自rollback的更改。

如果你解决了超时的问题，你可以看看：

使用IsolationLevel.ReadCommitted.这样，您的.NET代码将等待提交或回滚的完成。

(它看起来像带有类型为SqlCommand的cmd的cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted) )

Answer 2

SqlRollback.cs 

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

namespace Events
{
    public class SqlRollback
    {
        private string _connString = "Server=(localdb)\\mssqllocaldb;Database=QueryIt.EmployeeDb;Trusted_Connection=true";
        public event EventHandler TransactionRolledBack;

        public void Transact()
        {
            using (SqlConnection conn = new SqlConnection(_connString))
            {
                conn.Open();

                var command = conn.CreateCommand();
                SqlTransaction transaction;
                transaction = conn.BeginTransaction("Transaction");

                command.Connection = conn;
                command.Transaction = transaction;

                try
                {
                    transaction.Save("checkpoint");
                    command.CommandText = "INSERT INTO Employees (name, discriminator) VALUES ('Dimitar', 'Bastun')";
                    command.ExecuteNonQuery();
                    throw new Exception();

                } catch (Exception ex)
                {
                    transaction.Rollback("checkpoint"); //Rolling back to the checkpoint
                    Thread.Sleep(5000); //Simulating some time
                    transaction.Commit();
                    this.OnTransactionRollingBack(EventArgs.Empty);
                }
            }
        }

        private void OnTransactionRollingBack(EventArgs e)
        {
            EventHandler handler = TransactionRolledBack;
            if(handler != null)
            {
                handler(this, EventArgs.Empty);
            }
        }
    }
}

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Events
{
    class Program
    {
        static void Main(string[] args)
        {
            var rollBack = new SqlRollback();
            rollBack.TransactionRolledBack += RollBack_TransactionRolledBack;

            try
            {
                rollBack.Transact();
            } catch (Exception e)
            {

            }
        }

        private static void RollBack_TransactionRolledBack(object sender, EventArgs e)
        {
            Console.WriteLine("Rooled!");
        }
    }
}