65.9K
CodeProject 正在变化。 阅读更多。
Home

如何防止事务意外覆盖已修改的数据

Wendelius
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.91/5 (15投票s)

2017年3月23日

CPOL

14分钟阅读

viewsIcon

25680

downloadIcon

635

本文将解释一个问题：当另一个事务（并发或非并发）意外覆盖先前修改的数据时，如何防止这种情况发生。

引言

本文解释了事务意外覆盖已修改数据时出现的问题以及如何防止它。

目录

本文仅针对 SQL Server。如果您使用的是 Oracle 环境，请参阅 Oracle 替代方案。

引言，问题所在

在创建使用数据库的程序时，人们常常认为数据库已充分考虑了并发性。这在一定程度上是真的，但并非全部。

实际上发生的是 SQL Server 负责锁定。换句话说，它会按顺序访问相同资源的事务。例如，如果您尝试修改正在被另一个会话修改的行，您将被“挂起”。这是因为另一个会话对该行拥有排他锁。一旦锁被释放，您就可以随意更新该行。如果阻塞锁的持续时间很短，您可能甚至不会注意到这种情况的发生，而这种情况经常发生。

虽然行锁定可以保证修改行的操作不能同时进行，但它不能保证您正在修改的行仍然包含在您执行语句时一样的数据。请考虑以下情况：

在上例场景中，两个会话都从数据库读取了订单行。起初，该行的订购数量为 10，两个会话都将根据客户信息对其进行更新。

第一位销售人员收到信息，客户想多订购五件商品，因此会话将订购数量从 10 更新为 15。同时，另一位销售人员收到信息，客户想多订购一件商品，因此该行将再次更新，这次从 10 更新为 11。

由于该行被锁定，后者更新将不得不等待。第一个会话成功更新了记录，新的订购数量为 15。现在，当第一个会话提交事务时，第二个会话可以继续更新。结果，该行被更新，现在的订购数量为 11。

发生的情况是，第二个会话在不知情或未调查的情况下进行了更新，即该行在从数据库读取后已被另一个事务更新。在事务内部，这种情况称为“丢失更新”。

因此，本文的目的是解释如何使用行版本检查来防止发生丢失更新，该检查将数据版本检查扩展到事务范围之外。

演示程序

为了测试不同的场景，您可以使用附加的项目来模拟对现有行的更新。它实现了本文中解释的所有变体。

关于演示程序的几个警告： 演示程序中的代码不适用于实际情况。我故意打破了一些数据库编程的黄金法则。有一点是，我保持连接打开，但程序中最主要的缺陷是我允许用户在事务中间进行交互。在实际情况中，您永远不应该这样做。然而，在这个演示程序中，它使测试不同的场景更容易。

基本更新

话虽如此，确保您更新的是正确的行其实很简单，只需基于唯一键更新记录即可。让我们看看程序中的基本更新。

假设您有如下表：

CREATE TABLE Concurrency1 (
   Id          int          identity (1,1) NOT NULL,
   TextColumn  varchar(100)                NULL,
   ValueColumn decimal                     NULL
);

您可以将表中的行提取到网格中，一旦您选择网格中的一行，您就可以使用以下代码对其进行更新。

提取是通过以下代码完成的：

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.IData> FetchData() {
   try {
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn
FROM     Concurrency1";
         using (SqlDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.IData>();
            while (reader.Read()) {
               this.CurrentDataCollection.Add(new Data.TheData() {
                  Id = (int)reader["Id"],
                  TextValue = reader["TextColumn"] != System.DBNull.Value 
                              ? (string)reader["TextColumn"] : null,
                  NumberValue = reader["ValueColumn"] != System.DBNull.Value 
                                ? (decimal?)reader["ValueColumn"] : null
               });
            }
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}

''' <summary>
''' Fetch the data from the database And return as a collection
''' </summary>
''' <returns>Collection of data items</returns>
Public Overrides Function FetchData() As ObservableCollection(Of Data.TheData)
    Dim data As Data.TheData

    Try
       Using command As SqlCommand = New SqlCommand()
          command.Connection = Me.connection
          command.CommandText = "
SELECT   Id,
         TextColumn,
         ValueColumn
FROM     Concurrency1"
          Using reader As SqlDataReader = command.ExecuteReader()
             Me.CurrentDataCollection = New ObservableCollection(Of Data.TheData)()
             While (reader.Read())
                data = New Data.TheData()
                With data
                   .Id = DirectCast(reader("Id"), Integer)
                   .TextValue = If(Not reader.IsDBNull(1), reader("TextColumn").ToString(), Nothing)
                   .NumberValue = If(Not reader.IsDBNull(2), _
                                     DirectCast(reader("ValueColumn"), Decimal?), Nothing)
                End With
                Me.CurrentDataCollection.Add(data)
             End While
          End Using
       End Using
    Catch exception As System.Exception
       System.Windows.MessageBox.Show(exception.Message, _
                                      exception.Source, _
                                      System.Windows.MessageBoxButton.OK, _
                                      System.Windows.MessageBoxImage.Error)
       Return Me.CurrentDataCollection
    End Try

    Return Me.CurrentDataCollection
End Function

更新如下完成：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction(); 
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency1
SET   TextColumn  = @TextColumn,
      ValueColumn = @ValueColumn
WHERE Id = @Id";
         command.Parameters.AddWithValue("@TextColumn", data.TextValue);
         command.Parameters.AddWithValue("@ValueColumn", data.NumberValue.HasValue 
                                                         ? (object)data.NumberValue.Value 
                                                         : System.DBNull.Value);
         command.Parameters.AddWithValue("@Id", data.Id);
         rowsAffected = command.ExecuteNonQuery();
         if (rowsAffected != 1) {
            throw new Exception(string.Format("Wrong number of rows ({0}) affected", rowsAffected));
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }

   return true;
}

''' <summary>
''' Saves a single data item
''' </summary>
''' <param name="data">Data to save</param>
''' <returns>True if succesful</returns>
Public Overrides Function SaveItem(data As Data.TheData) As Boolean
    Dim rowsAffected As Integer

    Try
       If (Me.transaction Is Nothing) Then
          Me.transaction = Me.connection.BeginTransaction()
       End If
       Using command As SqlCommand = New SqlCommand()
          command.Connection = Me.connection
          command.Transaction = Me.transaction
          command.CommandText = "
UPDATE Concurrency1
SET   TextColumn  = @TextColumn,
      ValueColumn = @ValueColumn
WHERE Id = @Id"
          command.Parameters.AddWithValue("@TextColumn", data.TextValue)
          command.Parameters.AddWithValue("@ValueColumn", _
              If(data.NumberValue.HasValue, data.NumberValue.Value, System.DBNull.Value))
          command.Parameters.AddWithValue("@Id", data.Id)
          rowsAffected = command.ExecuteNonQuery()
          If (rowsAffected <> 1) Then
             Throw New System.Exception(String.Format("Wrong number of rows ({0}) affected", _
                                                      rowsAffected))
          End If
       End Using
    Catch exception As System.Exception
       System.Windows.MessageBox.Show(exception.Message, _
                                      exception.Source, _
                                      System.Windows.MessageBoxButton.OK, _
                                      System.Windows.MessageBoxImage.Error)
       Return False
    End Try

    Return True
End Function

代码非常简单：创建一个命令，设置参数，执行命令，最后检查受影响的行数。`WHERE` 子句中的主键值用于确保您更新的是正确的行。

您可以在很多地方找到此类代码。虽然代码是正确的（只要忽略连接和事务的生命周期），但它会在不检查数据版本的情况下覆盖数据库中的行。

为了演示这个问题，请执行以下操作：

  • 打开程序的两个实例
  • 实例 1
    • 提取数据
    • 选择第一行
  • 实例 2
    • 提取数据
    • 选择第一行
  • 实例 1
    • 将 number 字段修改为 `123`
    • 按保存按钮
  • 实例 2
    • 将 number 字段修改为 `456`
    • 按保存按钮。请注意，程序会冻结，因为它正在等待锁。
  • 实例 1
    • 按提交按钮，实例 2 的更新现在继续。
  • 实例 2
    • 按提交按钮
  • 实例 1
    • 提取数据
  • 实例 2
    • 提取数据

当查看网格中的数据时，您会注意到，实例 2 中更新的数据仍然是 number 字段的值 `456`。实例 1 中更新的数据被完全覆盖了。

更新时检查行版本

解决问题的方法是确保您更新的行正确且行的版本也正确。SQL Server 有一种特殊的数据类型称为 rowversion（以前称为 `timestamp`）。尽管数据类型名称如此，但它不包含任何有关时间的信息，也不是实际的版本号。此类型的唯一目的是在行发生更改时自动更改，因此您可以使用此类型来观察更改。现在表可能看起来像这样：

CREATE TABLE Concurrency2 (
   Id             int          identity (1,1) NOT NULL,
   CurrentVersion rowversion                  NOT NULL,
   TextColumn     varchar(100)                NULL,
   ValueColumn    decimal                     NULL
);

您可以看到一个名为 `CurrentVersion` 的附加列。此列现在存储当前的行版本。

在更新行之前，需要进行几行代码的检查。行版本的值是二进制数据，长度为 8 字节。为了方便观察和处理，我使用了一个自定义的 Rowversion 类。它重写了 ToString 方法，以便可以轻松地在网格中显示行版本的值。该类还确保数据的长度正好是 8 字节。

   /// <summary>
   /// Class to hold the rowversion value
   /// </summary>
   public class Rowversion {

      private byte[] theValue;

      /// <summary>
      /// The actual byte array
      /// </summary>
      public byte[] TheValue {
         get {
            return this.theValue;
         }
         set {
            if (value == null || value.Length != 8) {
               throw new System.Exception("Invalid rowversion value");
            }
            this.theValue = value;
         }
      }

      /// <summary>
      /// Default constructor
      /// </summary>
      public Rowversion() {
         this.TheValue = new byte[8];
      }

      /// <summary>
      /// Rowversion value in hexadecimal format
      /// </summary>
      /// <returns></returns>
      public override string ToString() {
         StringBuilder sb = new StringBuilder("0x");

         
         foreach (byte item in this.TheValue) {
            sb.Append(item.ToString("X"));
         }
         return sb.ToString();
      }
   }

    ''' <summary>
    ''' Class to hold the rowversion value
    ''' </summary>
    Public Class Rowversion

        Private _theValue As Byte()

        ''' <summary>
        ''' The actual byte array
        ''' </summary>
        Public Property TheValue() As Byte()
            Get
                Return Me._theValue
            End Get
            Set
                If (Value Is Nothing Or Value.Length <> 8) Then
                    Throw New System.Exception("Invalid rowversion value")
                End If
                Me._theValue = Value
            End Set
        End Property

        ''' <summary>
        ''' Default constructor
        ''' </summary>
        Public Sub New()
            Me.TheValue = New Byte(7) {}
        End Sub

        ''' <summary>
        ''' Rowversion value in hexadecimal format
        ''' </summary>
        ''' <returns>The row version</returns>
        Public Overrides Function ToString() As String
            Dim sb As StringBuilder = New StringBuilder("0x")

            For Each item As Byte In Me.TheValue
                sb.Append(item.ToString("X"))
            Next
            Return sb.ToString()
        End Function
    End Class

在提取数据时，代码与之前基本相同，但 `rowversion` 列的值是通过 `GetBytes` 方法检索的，并放入自定义行版本类中。

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.IData> FetchData() {
   Data.TheData data;

   try {
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn,
         CurrentVersion
FROM     Concurrency2";
         using (SqlDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.IData>();
            while (reader.Read()) {
               data = new Data.TheData();
               data.Id = (int)reader["Id"];
               data.TextValue = reader["TextColumn"] != System.DBNull.Value 
                                                     ? (string)reader["TextColumn"] : null;
               data.NumberValue = reader["ValueColumn"] != System.DBNull.Value 
                                                     ? (decimal?)reader["ValueColumn"] : null;
               data.CurrentRowVersion = new Data.Rowversion();
               reader.GetBytes(3, 0, data.CurrentRowVersion.TheValue, 0, 8);

               this.CurrentDataCollection.Add(data);
            }
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}
''' <summary>
''' Fetch the data from the database And return as a collection
''' </summary>
''' <returns>Collection of data items</returns>
Public Overrides Function FetchData() As ObservableCollection(Of Data.TheData)
    Dim data As Data.TheData

    Try
        Using command As SqlCommand = New SqlCommand()
            command.Connection = Me.connection
            command.CommandText = "
SELECT   Id,
         TextColumn,
         ValueColumn,
         CurrentVersion
FROM     Concurrency2"
            Using reader As SqlDataReader = command.ExecuteReader()
                Me.CurrentDataCollection = New ObservableCollection(Of Data.TheData)()
                While (reader.Read())
                    data = New Data.TheData()
                    With data
                        .Id = DirectCast(reader("Id"), Integer)
                        .TextValue = If(Not reader.IsDBNull(1), _
                                        reader("TextColumn").ToString(), Nothing)
                        .NumberValue = If(Not reader.IsDBNull(2), _
                                       DirectCast(reader("ValueColumn"), Decimal?), Nothing)
                        .CurrentRowVersion = New Data.Rowversion()
                        reader.GetBytes(3, 0, .CurrentRowVersion.TheValue, 0, 8)
                    End With

                    Me.CurrentDataCollection.Add(data)
                End While
            End Using
        End Using
    Catch exception As System.Exception
        System.Windows.MessageBox.Show(exception.Message, _
                                       exception.Source, _
                                       System.Windows.MessageBoxButton.OK, _
                                       System.Windows.MessageBoxImage.Error)
        Return Me.CurrentDataCollection
    End Try

    Return Me.CurrentDataCollection
End Function

重要的是，现在程序中对于您在用户界面中显示的所有行，您始终拥有 `rowversion` 的值。当您想要更新行时，可以使用以下代码：

   /// <summary>
   /// Saves a single data item
   /// </summary>
   /// <param name="data">Data to save</param>
   /// <returns>True if succesful</returns>
   override public bool SaveItem(Data.IData data) {
      int rowsAffected;

      try {
         this.BeginTransaction();
         using (SqlCommand command = new SqlCommand()) {
            command.Connection = this.connection;
            command.Transaction = this.transaction;
            command.CommandText = @"
UPDATE Concurrency2
SET   TextColumn  = @TextColumn,
      ValueColumn = @ValueColumn
WHERE Id             = @Id
AND   CurrentVersion = @CurrentVersion";
            command.Parameters.AddWithValue("@TextColumn", data.TextValue);
            command.Parameters.AddWithValue("@ValueColumn", data.NumberValue.HasValue 
                                            ? (object)data.NumberValue.Value : System.DBNull.Value);
            command.Parameters.AddWithValue("@Id", data.Id);
            command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue);
            rowsAffected = command.ExecuteNonQuery();
            if (rowsAffected != 1) {
               throw new System.Exception("Row versions do not match.");
            }
         }
      } catch (System.Exception exception) {
         System.Windows.MessageBox.Show(exception.Message, 
                                        exception.Source, 
                                        System.Windows.MessageBoxButton.OK, 
                                        System.Windows.MessageBoxImage.Error);
         return false;
      }
      return true;
   }
''' <summary>
''' Saves a single data item
''' </summary>
''' <param name="data">Data to save</param>
''' <returns>True if succesful</returns>
Public Overrides Function SaveItem(data As Data.TheData) As Boolean
    Dim rowsAffected As Integer

    Try
        If (Me.transaction Is Nothing) Then
            Me.transaction = Me.connection.BeginTransaction()
        End If
        Using command As SqlCommand = New SqlCommand()
            command.Connection = Me.connection
            command.Transaction = Me.transaction
            command.CommandText = "
UPDATE Concurrency2
SET   TextColumn  = @TextColumn,
      ValueColumn = @ValueColumn
WHERE Id             = @Id
AND   CurrentVersion = @CurrentVersion"
            command.Parameters.AddWithValue("@TextColumn", data.TextValue)
            command.Parameters.AddWithValue("@ValueColumn", If(data.NumberValue.HasValue, _
                                                data.NumberValue.Value, System.DBNull.Value))
            command.Parameters.AddWithValue("@Id", data.Id)
            command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue)
            rowsAffected = command.ExecuteNonQuery()
            If (rowsAffected <> 1) Then
                Throw New System.Exception("Row versions do not match.")
            End If
        End Using
    Catch exception As System.Exception
        System.Windows.MessageBox.Show(exception.Message, _
                                       exception.Source, _
                                       System.Windows.MessageBoxButton.OK, _
                                       System.Windows.MessageBoxImage.Error)
        Return False
    End Try

    Return True
End Function

这段代码与之前非常相似，但在此代码中，您还检查 `CurrentRowversion` 列是否仍保持与我们从数据库读取记录时相同的值。

重要！ 如果数据库中的底层行版本已更改，则语句不会报错。这是因为在这种情况下，语句是完全有效的，它只是不更新任何行。因此，检查更新的行数是否正确至关重要。如果您更新单行，更新应影响一行且仅一行。

现在，如果您使用此代码版本重复第一个示例中的相同测试，您将收到类似以下的错误消息：

当您再次将数据提取到网格并重复修改时，它将成功保存，因为程序现在知道当前的行版本。

在存储过程中进行检查

许多人使用存储过程来更改数据。在这种情况下，原理相同，但您只需将行版本的当前值传递给过程即可进行检查。该过程可能如下所示：

------------------------------------------
-- Procedure for saving to concurrency 2
------------------------------------------
IF OBJECT_ID ( 'procConcurrency2', 'P' ) IS NOT NULL   
    DROP PROCEDURE procConcurrency2;  
GO
CREATE PROCEDURE procConcurrency2(
   @Id               int,
   @CurrentVersion rowversion,
   @TextColumn     varchar(100),
   @ValueColumn    decimal
) AS
BEGIN
   UPDATE Concurrency2
   SET   TextColumn  = @TextColumn,
         ValueColumn = @ValueColumn
   WHERE Id             = @Id
   AND   CurrentVersion = @CurrentVersion;

   IF @@ROWCOUNT != 1 BEGIN
      RAISERROR('Row versions do not match.', 16, 1);
   END
END;
GO

更新行的语句与上一示例相同，检查更新的行数也相同。使用此过程，调用方可能如下所示：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.CommandText = @"procConcurrency2";
         command.Parameters.AddWithValue("@Id", data.Id);
         command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue);
         command.Parameters.AddWithValue("@TextColumn", data.TextValue);
         command.Parameters.AddWithValue("@ValueColumn", data.NumberValue.HasValue 
                                      ? (object)data.NumberValue.Value : System.DBNull.Value);
         rowsAffected = command.ExecuteNonQuery();
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}
''' <summary>
''' Saves a single data item
''' </summary>
''' <param name="data">Data to save</param>
''' <returns>True if succesful</returns>
Public Overrides Function SaveItem(data As Data.TheData) As Boolean
    Dim rowsAffected As Integer

    Try
        If (Me.transaction Is Nothing) Then
            Me.transaction = Me.connection.BeginTransaction()
        End If
        Using command As SqlCommand = New SqlCommand()
            command.Connection = Me.connection
            command.Transaction = Me.transaction
            command.CommandType = System.Data.CommandType.StoredProcedure
            command.CommandText = "procConcurrency2"
            command.Parameters.AddWithValue("@Id", data.Id)
            command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue)
            command.Parameters.AddWithValue("@TextColumn", data.TextValue)
            command.Parameters.AddWithValue("@ValueColumn", If(data.NumberValue.HasValue, _
                                                   data.NumberValue.Value, System.DBNull.Value))
            rowsAffected = command.ExecuteNonQuery()
        End Using
    Catch exception As System.Exception
        System.Windows.MessageBox.Show(exception.Message, _
                                       exception.Source, _
                                       System.Windows.MessageBoxButton.OK, _
                                       System.Windows.MessageBoxImage.Error)
        Return False
    End Try

    Return True
 End Function

一个很大的区别是，您不再在客户端进行更新行数的检查。如果过程更新的行数不正确，它将引发错误，客户端将收到一个可以显示的异常。

使用触发器强制进行行版本检查

现在您知道如何检查行版本，但如何确保始终进行检查。一个复杂的系统可能有大量的数据库操作，那么如何保证其中每一个都进行检查呢？答案是，您无法对所有代码做出这样的保证，但您可以在数据库中强制进行检查。

其思想是，我们在一个表中维护两个行版本列，一个存储当前行版本值，另一个在更新时由客户端提供。

第一个问题是，单个表不能有两个 rowversion 类型的列。如果您尝试创建一个具有两个 rowversion 列的表，您将收到如下错误：

Msg 2738, Level 16, State 2, Line 1

A table can only have one timestamp column. 
Because table 'table name' already has one, the column 'column name' cannot be added.

即使该表可以容纳两个 rowversion 列，这也没有意义，因为它们都会被自动更新。

因此，您实际需要的是一个二进制列。由于该列可以包含 `null` 值，因此需要将其定义为 `varbinary(8)`。表脚本可能如下所示：

CREATE TABLE Concurrency3 (
   Id              int          identity (1,1) NOT NULL,
   CurrentVersion  rowversion                  NOT NULL,
   PreviousVersion varbinary(8)                NULL,
   TextColumn      varchar(100)                NULL,
   ValueColumn     decimal                     NULL
);

现在，为了强制执行行版本检查，无论客户端做什么，我们都需要一个在更新时触发的触发器。触发器如下所示：

------------------------------------------
-- Trigger to enforce rowversion check
------------------------------------------
CREATE TRIGGER Concurrency3_RowversionCheck
ON Concurrency3
FOR UPDATE AS 
BEGIN
   DECLARE @ErrorCount int;

   SELECT @ErrorCount = COUNT(*)
   FROM inserted i
   WHERE NOT EXISTS (SELECT 1 
                     FROM deleted d
                     WHERE d.Id = i.Id
                     AND   d.CurrentVersion = i.PreviousVersion);

   IF @ErrorCount > 0 BEGIN
      ROLLBACK;
      RAISERROR('The given row versions do not match old row versions.', 16, 1);
   END;
END;

要理解触发器，您需要知道旧的、已更新的行存储在名为 `deleted` 的虚拟表中，而行的新版本存储在 `inserted` 虚拟表中。因此，在发生更新时，您可以使用这些虚拟表中的数据来检查更新的旧值和新值。

当更新发生时，触发器期望客户端代码已将行版本的值提供给 `PreviousVersion` 列。触发器检查 `inserted`（新）行中的此值是否与 `deleted`（旧）行中的 `CurrentVersion` 列的值匹配。如果在 `deleted` 表中找不到相应的行，则会引发错误。

检查方式与之前在 `WHERE` 子句中使用的检查方式略有不同。原因是触发器每次执行只触发一次，所以如果您同时更新多行，所有已更新的行都需要被检查。

现在，如果您查看客户端代码：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency3
SET   TextColumn        = @TextColumn,
      ValueColumn       = @ValueColumn,
      PreviousVersion   = @CurrentVersion
WHERE Id             = @Id";
         command.Parameters.AddWithValue("@TextColumn", data.TextValue);
         command.Parameters.AddWithValue("@ValueColumn", data.NumberValue.HasValue 
                                         ? (object)data.NumberValue.Value : System.DBNull.Value);
         command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue);
         command.Parameters.AddWithValue("@Id", data.Id);
         rowsAffected = command.ExecuteNonQuery();
         if (rowsAffected != 1) {
            throw new Exception(string.Format("Wrong number of rows ({0}) affected", rowsAffected));
         }
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}
''' <summary>
''' Saves a single data item
''' </summary>
''' <param name="data">Data to save</param>
''' <returns>True if succesful</returns>
Public Overrides Function SaveItem(data As Data.TheData) As Boolean
    Dim rowsAffected As Integer

    Try
        If (Me.transaction Is Nothing) Then
            Me.transaction = Me.connection.BeginTransaction()
        End If
        Using command As SqlCommand = New SqlCommand()
            command.Connection = Me.connection
            command.Transaction = Me.transaction
            command.CommandText = "
UPDATE Concurrency3
SET   TextColumn        = @TextColumn,
      ValueColumn       = @ValueColumn,
      PreviousVersion   = @CurrentVersion
WHERE Id             = @Id"
            command.Parameters.AddWithValue("@TextColumn", data.TextValue)
            command.Parameters.AddWithValue("@ValueColumn", If(data.NumberValue.HasValue, _
                                                  data.NumberValue.Value, System.DBNull.Value))
            command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue)
            command.Parameters.AddWithValue("@Id", data.Id)
            rowsAffected = command.ExecuteNonQuery()
            If (rowsAffected <> 1) Then
                Throw New Exception(String.Format("Wrong number of rows ({0}) affected", rowsAffected))
            End If
        End Using
    Catch exception As System.Exception
        System.Windows.MessageBox.Show(exception.Message, _
                                       exception.Source, _
                                       System.Windows.MessageBoxButton.OK, _
                                       System.Windows.MessageBoxImage.Error)
        Return False
    End Try

    Return True
End Function

您可以看到 `WHERE` 子句中不再有额外的条件，只有主键的条件。相反，`UPDATE` 语句中的 `PreviousVersion` 列被设置为具有您当前在客户端拥有的行版本的值。

另外，请注意，检查更新行数的条件仅在主键无效时才会失败。

现在，如果您再次重复最初列出的测试，您将收到如下错误：

使用 SQL Server Management Studio，您还可以尝试在不提供正确旧行版本的情况下更新多行，例如：

-- Try to update several rows at a time
UPDATE Concurrency3 SET ValueColumn = ValueColumn + 1;

这会导致一个错误

-- Msg 50000, Level 16, State 1, Procedure Concurrency3_RowversionCheck, Line 16 [Batch Start Line 91]
-- The given row versions do not match old row versions.
-- Msg 3609, Level 16, State 1, Line 93
-- The transaction ended in the trigger. The batch has been aborted.

Entity Framework 中的相应检查（仅限 C#）

那么，我们已经讨论了仅使用纯 SQL 的问题，那么 Entity Framework 呢？使用 EF 时如何强制执行相同的检查？

想法自然是相同的，将条件嵌入到执行的 SQL 语句中。为了使示例更直观，请将 `Id` 列定义为主键，以便 EF 生成器能够正确拾取表的实际键。

CREATE TABLE Concurrency5 (
   Id               int          identity (1,1) 
                                PRIMARY KEY    NOT NULL,
   CurrentVersion  rowversion                  NOT NULL,
   TextColumn      varchar(100)                NULL,
   ValueColumn     decimal                     NULL
);

下一步是从数据库生成模型。我不会详细介绍，因为这主要通过点击向导中的“下一步”按钮来完成…… 重要！ EF 的连接字符串在 `app.config` 文件中，因此请修改它以包含您环境中正确的实例和数据库名称。

模型创建完成后，让我们看看获取和保存的方法。

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.IData> FetchData() {
   try {
      if (this.CurrentDataCollection == null) {
         this.CurrentDataCollection = new ObservableCollection<Data.IData>();
         foreach (Concurrency5 item in this.CurrentContext.Concurrency5) {
            this.CurrentDataCollection.Add(item);
         }
      } else {
         foreach (Concurrency5 item in this.CurrentDataCollection) {
            this.CurrentContext.Entry(item).Reload();
         }
         this.CurrentDataCollection = new ObservableCollection<Data.IData>(this.CurrentDataCollection);
      }
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return this.CurrentDataCollection;
   }

   return this.CurrentDataCollection;
}

上面的代码从 `DbContext` 中获取项目集合。代码段很简单，只是用 `Concurrency5` 表中的项目填充集合。代码中有一些特殊之处。首先，如果集合已经存在，我只是单独从数据库重新加载项目。另一件事是返回的集合总是新的集合。由于我没有实现 `INotifyPropertyChanged` 接口，这只是一个快速的解决方法，以强制网格响应可能的更改。

重要！ 与之前的示例一样，在此示例中，上下文和事务的生命周期也过长。原因是这样可以更容易地测试不同的场景，但在实际情况下不应保留上下文。

那么保存看起来如何？最简单的形式可以是：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();

      rowsAffected = this.CurrentContext.SaveChanges();
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

您会看到，没有代码强制检查行版本，没有修改 `WHERE` 子句，并且受影响的行数被存储但未被检查，因为它没有意义。如果您跟踪执行的 SQL 语句，您会注意到 `WHERE` 子句仅包含主键的条件，仅此而已。

那么如何进行行版本检查呢？关键是更改您的 EF 模型。如果您打开模型并单击 `CurrentVersion` 列，您可以看到 `ConcurrencyMode` 属性已更改为 `Fixed`。这是通知 EF 执行乐观并发检查的方法。

那么代码中发生了什么，如何检查更新的行数是否正确？答案是您不必检查。如果更新没有更新行，EF 会抛出 `DbUpdateConcurrencyException`。因此，完整的代码可能如下所示：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   int rowsAffected;

   try {
      this.BeginTransaction();

      rowsAffected = this.CurrentContext.SaveChanges();
   } catch (System.Data.Entity.Infrastructure.DbUpdateConcurrencyException concurrencyException) {
      System.Windows.MessageBox.Show("Row versions do not match (" + concurrencyException.Message + ")",
                                     concurrencyException.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}


现在，如果您重复前面描述的相同测试，您应该会看到类似这样的消息：“*存储更新、插入或删除语句影响了意外数量的行（0）。自加载实体以来，实体可能已被修改或删除……*”

更新后的行版本值（仅限 C#）

一个显而易见但尚未回答的问题是，在使用纯 SQL 时，行版本的值应该如何重新获取？当然，您可以重新查询该值，但这将是与数据库的一次额外往返。为了避免不必要的网络流量，您可以将这两个语句包含在一次运行中。例如：

/// <summary>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.IData data) {
   System.Collections.Generic.List<Data.IData> oldItems;

   try {
      this.BeginTransaction();
      using (SqlCommand command = new SqlCommand()) {
         command.Connection = this.Connection;
         command.Transaction = this.Transaction;
         command.CommandText = @"
UPDATE Concurrency2
SET   TextColumn  = @TextColumn,
      ValueColumn = @ValueColumn
WHERE Id             = @Id
AND   CurrentVersion = @CurrentVersion;
SELECT @@ROWCOUNT    AS AffectedRows, 
      CurrentVersion AS CurrentVersion
FROM Concurrency2
WHERE Id             = @Id";
         command.Parameters.AddWithValue("@TextColumn", data.TextValue);
         command.Parameters.AddWithValue("@ValueColumn", data.NumberValue.HasValue 
                                                         ? (object)data.NumberValue.Value 
                                                         : System.DBNull.Value);
         command.Parameters.AddWithValue("@Id", data.Id);
         command.Parameters.AddWithValue("@CurrentVersion", data.CurrentRowVersion.TheValue);
         using (SqlDataReader reader = command.ExecuteReader()) {
            if (!reader.Read()) {
               throw new System.ApplicationException("Row has been deleted");
            }
            if ((int)reader["AffectedRows"] != 1) {
               throw new System.Exception("Row versions do not match.");
            }
            ((Data.TheData)data).CurrentRowVersion = new Data.Rowversion();
            reader.GetBytes(1, 0, data.CurrentRowVersion.TheValue, 0, 8);
         }
      }
      oldItems = new System.Collections.Generic.List<Data.IData>();
      oldItems.AddRange(this.CurrentDataCollection);
      this.CurrentDataCollection.Clear();
      oldItems.ForEach(x => this.CurrentDataCollection.Add(x));
   } catch (System.Exception exception) {
      System.Windows.MessageBox.Show(exception.Message, 
                                     exception.Source, 
                                     System.Windows.MessageBoxButton.OK, 
                                     System.Windows.MessageBoxImage.Error);
      return false;
   }
   return true;
}

如您所见，SQL 文本包含两个单独的 SQL 语句，一个用于更新行，另一个用于从行中获取新值。`SELECT` 部分获取：

  • 更新的行数（`@@ROWCOUNT`）
  • `CurrentVersion` 列中的新值

使用获取的值，客户端代码检查实际更新的行数是否正确，如果正确，则使用数据库中的新值存储最新的 `rowversion` 值。

要点

本文的主要要点是：

  • 为什么在即使使用悲观锁定时也常常需要进行行版本检查
  • 如何利用 `rowversion` 来实现此检查
  • 如何在存储过程中检查 `rowversion`
  • 如何使用触发器强制客户端程序提供正确的 `rowversion`
  • 如何使用 Entity Framework 进行检查

通过运用这些原则，您可以使您的程序更加健壮，并确保用户始终知道自己在更新什么。

另外，请注意，本文仅涵盖 `UPDATE` 场景。在许多情况下，在 `DELETE` 上执行类似测试也是有意义的。

另一个不错的额外技巧是，您可以轻松地跟踪 Entity Framework 的 SQL 语句。在 C# 代码中，查看 `ConcurrencyContext_CustomConnectionString` 类文件。它为上下文添加了一个额外的构造函数，还向 `Log` 属性添加了一个方法，用于将日志信息写入 `Debug`。

/// <summary>
/// This partial definition extends the EF generated context class
/// </summary>
public partial class ConcurrencyContext : System.Data.Entity.DbContext {

   /// <summary>
   /// Constructor for using custom connection string
   /// </summary>
   /// <param name="connectionString">Connections string to use</param>
   public ConcurrencyContext(string connectionString) : base(connectionString) {
      this.Database.Log = WriteLog;
   }

   /// <summary>
   /// Write the message into debug output 
   /// </summary>
   /// <param name="message">Message to write</param>
   public void WriteLog(string message) {
      System.Diagnostics.Debug.WriteLine(message);
   }
}

参考文献

历史

  • 2017 年 3 月 23 日：创建。
  • 2017 年 4 月 2 日：添加了 Oracle 替代方案。
  • 2017 年 4 月 10 日，C# 版本更新：
    • 添加了登录屏幕
    • 添加了使用 EF6 的示例
    • 添加了在更新数据库的同一调用中获取新行版本的示例
  • 2017 年 5 月 1 日：删除了固定连接字符串中的残留内容，改进了术语的准确性。
© . All rights reserved.