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

所见即所得更新

Wendelius
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.95/5 (4投票s)

2017年4月2日

CPOL

8分钟阅读

viewsIcon

11321

downloadIcon

152

这是“所见即所得更新”的替代方案

引言

本文是 SQL Server 特有文章 "所见即所得更新" 的替代方案，展示了 Oracle 环境中的问题（以及解决方法）。因此，如果您在 SQL Server 环境中工作，请使用原始文章。

有关问题的描述，请参阅 “引言，问题”，有关演示程序的图片，请查看 “演示程序”。

目录

基本更新

确保您更新的是正确的行很简单，只需根据唯一键更新记录即可。让我们来看看程序中的基本更新。

假设您有一个如下所示的表

CREATE TABLE Concurrency1 (
   Id          number        NOT NULL,
   TextColumn  varchar2(100) NULL,
   ValueColumn number        NULL
);

获取数据是通过以下代码完成的

/// <summary>
/// Fetch the data from the database and return as a collection
/// </summary>
/// <returns>Collection of data items</returns>
override public ObservableCollection<Data.TheData> FetchData() {
   try {
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn
FROM     Concurrency1";
         using (OracleDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.TheData>();
            while (reader.Read()) {
               this.CurrentDataCollection.Add(new Data.TheData() {
                  Id = (decimal)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>
/// Saves a single data item
/// </summary>
/// <param name="data">Data to save</param>
/// <returns>True if succesful</returns>
override public bool SaveItem(Data.TheData data) {
   int rowsAffected;

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency1
SET   TextColumn  = :TextColumn,
      ValueColumn = :ValueColumn
WHERE Id = :Id";
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                                ? (object)data.NumberValue.Value 
                                                : System.DBNull.Value);
         command.Parameters.Add(":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;
}

与原始文章一样，为了演示问题，请执行以下操作

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

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

更新时检查行版本

问题的解决方案是确保您同时更新了正确的行和行的正确版本。SQL Server 有一种特殊的自动在每次更新时更改的数据类型，而 Oracle 没有直接的等效项。但是，在 Oracle 中轻松实现相同的功能。在此示例中，我使用了一个触发器，该触发器在每次更新时简单地递增版本号。现在表可能看起来像这样

CREATE TABLE Concurrency2 (
   Id             number        NOT NULL,
   CurrentVersion number        NOT NULL,
   TextColumn     varchar2(100) NULL,
   ValueColumn    number        NULL
);

可以看到，有一个名为 CurrentVersion 的额外列。此列现在存储当前的行版本。更新值的触发器可能如下所示

CREATE OR REPLACE TRIGGER Concurrency2Trigger
   BEFORE INSERT OR UPDATE ON Concurrency2
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

触发器只是取旧行的值并加一。插入时，旧行不存在，因此旧值是 NULL。在这种情况下，使用 0 来获得“种子”值。

获取数据时，代码与之前基本相同，但也会检索 rowversion 列的值。

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

   try {
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.CommandText = @"
SELECT   Id,
         TextColumn,
         ValueColumn,
         CurrentVersion
FROM     Concurrency2";
         using (OracleDataReader reader = command.ExecuteReader()) {
            this.CurrentDataCollection = new ObservableCollection<Data.TheData>();
            while (reader.Read()) {
               data = new Data.TheData();
               data.Id = (decimal)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 = reader["CurrentVersion"] != System.DBNull.Value 
                                        ? (decimal?)reader["CurrentVersion"] : null;

               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;
}

因此，重要的是现在程序中始终包含您在用户界面中显示的所有行的 CurrentVersion 列的值。当您想更新行时，您可能会有如下代码

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

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         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.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                ? (object)data.NumberValue.Value : System.DBNull.Value);
         command.Parameters.Add(":Id", data.Id);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion);
         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;
}

代码与之前的代码非常相似，但在此代码中，您还检查 CurrentRowversion 列是否仍然具有从数据库读取记录时的相同值。

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

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

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

在存储过程中进行检查

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

------------------------------------------
-- Procedure for saving to concurrency 2
------------------------------------------
CREATE OR REPLACE PROCEDURE procConcurrency2(
      pId             number,
      pCurrentVersion number,
      pTextColumn     varchar2,
      pValueColumn    number) AS
   BEGIN
      UPDATE Concurrency2
      SET   TextColumn  = pTextColumn,
            ValueColumn = pValueColumn
       WHERE Id             = pId
       AND   CurrentVersion = pCurrentVersion;

      IF SQL%ROWCOUNT != 1 THEN
         RAISE_APPLICATION_ERROR(-20001, 'Row versions do not match.');
      END IF;
   END;
/

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

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

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandType = System.Data.CommandType.StoredProcedure;
         command.CommandText = @"procConcurrency2";
         command.Parameters.Add(":Id", data.Id);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion.HasValue 
                                ? (object)data.CurrentRowVersion.Value : System.DBNull.Value);
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":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;
}

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

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

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

想法是在一个表中保留两个行版本列，一个存储当前行版本值，另一个在执行更新时从客户端提供。新的表布局可能如下所示

CREATE TABLE Concurrency3 (
   Id              number        NOT NULL,
   CurrentVersion  number        NOT NULL,
   PreviousVersion number        NULL,
   TextColumn      varchar2(100) NULL,
   ValueColumn     number        NULL
);

在前一个示例中，触发器如下所示

CREATE OR REPLACE TRIGGER Concurrency2Trigger
   BEFORE INSERT OR UPDATE ON Concurrency2
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

相同的触发器用于 INSERT 和 UPDATE 操作。由于我们只需要在更新期间检查版本值，因此最简单的方法是将此拆分为两个单独的触发器。第一个处理 INSERT

CREATE OR REPLACE TRIGGER Concurrency3_Insert
   BEFORE INSERT ON Concurrency3
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := 1;
   END;
/

第二个触发器在 UPDATE 上触发

CREATE OR REPLACE TRIGGER Concurrency3_Update
   BEFORE UPDATE ON Concurrency3
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := :OLD.CurrentVersion + 1;

      IF (:OLD.CurrentVersion != COALESCE(:NEW.PreviousVersion, -1)) THEN
         RAISE_APPLICATION_ERROR(-20001, 'The given row versions do not match old row versions.');
      END IF;
   END;
/

当更新发生时，触发器期望客户端代码已将行版本的值提供给 PreviousVersion 列。由于触发器对所有更新的行都会触发，因此它只是检查旧行中 CurrentVersion 列的值是否与新行中 PreviousVersion 列的值相同。如果值不同，则会引发错误。

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

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

   try {
      if (this.transaction == null) {
         this.transaction = this.connection.BeginTransaction();
      }
      using (OracleCommand command = new OracleCommand()) {
         command.Connection = this.connection;
         command.Transaction = this.transaction;
         command.CommandText = @"
UPDATE Concurrency3
SET   TextColumn        = :TextColumn,
      ValueColumn       = :ValueColumn,
      PreviousVersion   = :CurrentVersion
WHERE Id             = :Id";
         command.Parameters.Add(":TextColumn", data.TextValue);
         command.Parameters.Add(":ValueColumn", data.NumberValue.HasValue 
                                ? (object)data.NumberValue.Value : System.DBNull.Value);
         command.Parameters.Add(":CurrentVersion", data.CurrentRowVersion.HasValue 
                                ? (object)data.CurrentRowVersion.Value : System.DBNull.Value);
         command.Parameters.Add(":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;
}

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

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

现在，如果您再次重复开始时列出的测试，您将收到类似以下的错误

如果您也尝试更新所有行，例如使用 SQL*Plus，您也会收到相同的错误

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

您将收到以下错误

ORA-20001: The given row versions do not match old row versions.
ORA-06512: at "SYSTEM.CONCURRENCY3_UPDATE", line 5
ORA-04088: error during execution of trigger 'SYSTEM.CONCURRENCY3_UPDATE'

与 Entity Framework 进行相应的检查

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

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

CREATE TABLE Concurrency5 (
   Id              number        NOT NULL,
   CurrentVersion number        NOT NULL,
   TextColumn     varchar2(100) NULL,
   ValueColumn    number        NULL
);

CREATE OR REPLACE TRIGGER Concurrency5Trigger
   BEFORE INSERT OR UPDATE ON Concurrency5
   FOR EACH ROW
   BEGIN
      :NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
   END;
/

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

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

/// <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 (Data.IData 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）。自加载实体以来，实体可能已被修改或删除……”

 

参考文献

历史

  • 2017 年 4 月 2 日：已创建替代方案
© . All rights reserved.