Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server

How to Prevent When a Transaction Accidentally Overwrites Modified Data

Rate me:
Please Sign up or sign in to vote.
4.91/5 (16 votes)
30 Apr 2017CPOL14 min read 23.6K   520   17   5
This article explains the problem, when another transaction (concurrent or not) accidentally overwrites previously modified data and how to prevent this from happening.
The purpose of this article is to explain how to prevent a lost update from occurring in SQL Server. The method is done using row version checking which extends the check of the data version outside the transaction scope. It looks at: Why row version check is often required even though pessimistic locking is used, how to utilize rowversion to implement this check, how to check the rowversion in a procedure, how to force the client program to provide correct rowversion using a trigger, and how the check can be done using Entity Framework.

Introduction

This article explains the problem when a transaction accidentally overwrites modified data and how to prevent it.

Table of contents

This article is SQL Server specific. If you're using an Oracle environment, have a look at the Oracle alternative.

Introduction, the problem

When creating a program that uses a database, one often thinks that the database sufficiently takes care of the concurrency. This is partially true, but not the whole story.

What actually happens is that SQL Server takes care of the locking. In other words, it puts operations accessing the same resource into an order. For example, if you try to modify a row that is being modified by another session you are put ‘on hold’. This happens because the other session has an exclusive lock on the row. Once the lock is released, you are free to update the row as you like. You may not even notice this happening if the duration of the blocking lock is short, which often is the case.

While locking the rows guarantees that the operations modifying the row cannot be done simultaneously it does not guarantee that, the row you’re modifying is still containing the same data as when you executed the statement. Consider the following situation

Image 1

In the scenario above, both sessions read the order row from the database. In the beginning, the row has 10 as ordered amount and both sessions are going to update it based on the information from the customer.

The first sales person receives information that the client wants to order five more items so the session updates the ordered amount from 10 to 15. At the same time, the other sales person receives information that the customer wants to order one more item, so the row will be updated again, this time from 10 to 11.

Since the row is locked, the latter update will have to wait. The first session successfully updates the record and the new ordered amount is 15. Now when the transaction is committed by the first session, the second one can proceed with the update. In result, the row is updated and now the ordered amount is 11.

What happened is that the second session did the update without knowing or investigating that the row was updated by another transaction after it was read from the database. Inside a transaction, this situation is called a lost update.

So the purpose of this article is to explain how to prevent a lost update from occurring using row version checking which extends the check of the data version outside the transaction scope.

Demo program

To test different scenarios, you can use the attached project to simulate updates on existing rows. It has implemented all the variations explained in this article.

Image 2

A few warnings about the demo program: The code written in the demo program is not meant to be used as-is in real world situations. I have deliberately broken some of the golden rules of database programming. One thing is that I hold the connection open but the most important flaw in the program is that I am allowing user conversation in the middle of a transaction. In real world situations, you should never ever do this. However, in this demo program it makes it easier to test different scenarios.

Basic update

Having that said, ensuring that you update a correct row is simple, update the record based on a unique key.  Let’s have a look the basic update in the program.

Say you have a table like the following

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

You can fetch the rows from the table into the grid and once you select a row from the grid, you can update it using the following code.

The fetch is done using the following code

C#
/// <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;
}
VB.NET
''' <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

And the updates is done as follows

C#
/// <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;
}
VB.NET
''' <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

The code is quite simple: A command is created, the parameters are set, the command is executed, and the amount of affected rows is checked in the end. The primary key value in the WHERE clause is used to ensure that you update the correct row.

You can find this kind of code in many places. While the code is correct (as long as both connection and transaction lifetimes are ignored) it overwrites the row in the database without checking anything about data version.

To demonstrate the problem, do the following:

  • Open two instances of the program
  • Instance 1:
    • Fetch the data
    • Select the first row
  • Instance 2:
    • Fetch the data
    • Select the first row
  • Instance 1:
    • Modify the number field to 123
    • Press save button
  • Instance 2:
    • Modify the number field to 456
    • Press save button. Note that the program freezes because it waits for the lock
  • Instance 1:
    • Press Commit button , instance 2 update now proceeds
  • Instance 2:
    • Press Commit button
  • Instance 1:
    • Fetch the data
  • Instance 2:
    • Fetch the data

When looking at the data in the grid you can notice that, the data updated in instance 2 remains the value of the number field is 456. The data updated in instance 1 is simply overwritten.

Checking rowversion upon update

The solution to the problem is to ensure that you are updating both correct row and correct version of the row. SQL Server has a special data type called rowversion (previously known as timestamp). Despite of the name of the data type, it’s not containing any information about time nor it is actually a version number. The sole purpose for this type is that it changes automatically whenever the row is changed, so you can utilize this type in order to observe the changes. Now the table could look like this

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

As you can see there’s an additional column called CurrentVersion. This column now stores the current rowversion.

Before going to updating the row, few lines of code to investigate. The value of the rowversion is binary data having length of 8 bytes. To make it easier to observe and handle I used a custom Rowversion class. It overrides ToString method so that the value of the rowversion can easily be shown in the grid. The class also ensures that the length of the data is exactly 8 bytes.

C#
/// <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();
   }
}
VB.NET
''' <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

When fetching the data the code is basically the same as previously but the value of the rowversion column is retrieved using the GetBytes method and placed into the custom rowversion class

C#
/// <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;
}
VB.NET
''' <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

The important thing is that now you always have the value of the rowversion in the program for all the rows you show in the user interface. When you want to update the row, you could have code like the following

C#
   /// <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;
   }
VB.NET
''' <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

The code is very similar to the previous one, but in this one, you also check that the CurrentRowversion column still holds the same value as when we read the record from the database.

IMPORTANT! If the underlying rowversion has changed in the database, you won’t get an error from the statement. This happens because in this situation the statement is perfectly valid, it just does not update any rows. Because of this, it is critical to investigate, that correct amount of rows is updated. If you update a single row, the update should affect one and one only row.

Now if you repeat the same test with this code version as you did in the first example you will get an error message like following.

Image 3

When you fetch the data again to the grid and repeat the modification, it is saved successfully since now the program knows the current rowversion.

Making the check inside a stored procedure

Many people use stored procedures for changing the data. In such case, the principle is the same but you just need to pass the current value of the rowversion to the procedure in order to make the check. The procedure could look like this

SQL
------------------------------------------
-- 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

The statement for updating the row is the same as in previous example and so is the check how many rows have been updated. With this procedure the calling side could look like this

C#
/// <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;
}
VB.NET
''' <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

One big difference is that you do not investigate the amount of updated rows at the client side anymore. If the procedure updates a wrong amount of rows then it raises an error and the client receives an exception that can be shown.

Image 4

Forcing the rowversion check using a trigger

So now, you know how to check the rowversion but how to ensure that the check is always made. A complex system may have quite a lot of database operations so how to guarantee that each and every one of them is making the check. The answer is that you cannot make such guarantee for all the code, but you can force the check in the database.

The idea is that we have two row version columns in a table, one holding the current value of rowversion and the other one is provided from client side when the update is done.

The first problem is that a single table cannot have more than one column of type rowversion. If you try to create a table having two rowversion columns, you receive an error like following

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.

Even if the table could hold two rowversion columns, it would not make sense since they both would be automatically updated.

Therefore, what you actually need is a binary column. Since the column can contain null values, you need to define it as varbinary(8). The table script could look like this

SQL
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
);

Now to enforce the rowversion check, regardless of what the client does, we need a trigger, which is fired upon an update. The trigger looks like this

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

To understand the trigger you need to know that the old, updated row is stored in virtual table called deleted table and the new version of the row is stored in inserted virtual table. Therefore, when the update happens, you can investigate the old and the new values for the update by using data from these virtual tables.

When the update happens the trigger expects that the client code has provided the value of the rowversion to the column PreviousVersion. The trigger investigates if this value in inserted (new) row matches the value of the CurrentVersion column in the deleted (old) row. If the corresponding row is not found in deleted table, an error is raised.

The check is a bit different from the check used previously in WHERE clause. The reason is that the trigger is fired only once per execution so if you update multiple rows at the same time, all the updated rows need to be investigated.

Now if you look at the code at the client side

C#
/// <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;
}
VB.NET
''' <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

You can see that there is no additional condition in the WHERE clause anymore, just the condition for the primary key. Instead, the PreviousVersion column is set in the UPDATE statement to have the value of the rowversion you currently have at the client.

Also, note that the check investigating how many rows were updated fails only if the primary key is invalid.

Now if you again repeat the tests listed in the beginning you receive an error like

Image 5

Using SQL Server Management Studio, you can also try updating multiple rows without providing correct old rowversion, for example

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

This leads to an error

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

Corresponding check with Entity Framework (C# only)

So now that we've discussed the problem using plain SQL only, what about Entity Framework? How can we enforce the same check when using EF? 

The idea is naturally the same, embed the condition to the SQL statement that is executed. In order for the example to be more intuitive, define the Id column as a primary key so that EF generator can correctly pick up the actual key for the table

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

The next step is to generate the model from the database. I won't go into details of that since it's mainly done by clicking Next-button in the wizard... Important! The connection string for the EF is in app.config file so modify it to contain correct instance and database names in your environment.

After the model has been created let's have a look at the methods for fetching and saving

C#
/// <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;
}

The code above fetches the collection of items from the DbContext. The snippet is simple, just fill the collection with the items from Concurrency5 table. There are few peculiarities in the code. First of all, if the collection already exists, I just reload the items individually from the database. Another thing is that the returned collection is always a new collection. Since I haven't implemented INotifyPropertyChanged interface, this is just a quick workaround to force the grid to react to possible changes.

Important! As in the previous examples, also in this one the lifetime of the context and the transaction is unnecessarily prolonged. The reason is that this way it's easier to test different scenarios, but preserving the context shouldn't be done in real situations. 

So what does the save look like? In the simplest form it could be

C#
/// <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;
}

As you see, there is no code to enforce the check for the rowversion, no modification to the WHERE clause and the amount of affected rows is stored but it isn't checked since it would make no sense. If you would trace the SQL statement that is executed, you'd notice that the WHERE clause contains only condition for the primary key, nothing else.

So how to make the rowversion check to take place? The key is to alter your EF model. If you open the model and click the CurrentVersion column, you can see that the ConcurrencyMode property is changed to Fixed. This is the way to inform EF that optimistic concurrency check is to be enforced.

So what happens in the code, how to check if correct amount of rows has been updated? The answer is that you don't need to check it. If the update doesn't update the row, EF throws a DbUpdateConcurrencyException. So the complete code could look like this

C#
/// <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;
}


Now if you repeat the same test described earlier, you should see a message like this "Store update, insert, or delete statement affected an unwxpected amount of rows (0). Entities may have been modified or deleted since entities were loaded..."

Image 6

The updated rowversion value (C# only)

The obvious question that isn't yet answered is, how the value of the rowversion should be refetched when using plain SQL? Of course you could requery for the value, but that would be an extra round-trip to the database. In order to avoid unnecessary network traffic, you can include both statements in a single run. Here's an example

C#
/// <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;
}

As you see the SQL text contains two separate SQL statements, one for updating the row and another one for fetching the new values from the row. The SELECT part fetches:

  • The number of rows updated (@@ROWCOUNT)
  • New value from the CurrentVersion column

With the fetched values the client side code checks that the correct amount of rows was really updated and if that was true, then use the new value from the database to store the latest rowversion value. 

Key points

The main points in this article were:

  • Why row version check is often required even though pessimistic locking is used
  • How to utilize rowversion to implement this check
  • How to check the rowversion in a procedure
  • How to force the client program to provide correct rowversion using a trigger
  • How the check can be done using Entity Framework

By using these principles, you can make your program more robust and ensure that the user always knows what she or he is updating.

Also, note that this article covers only UPDATE scenarios. In many cases, it would also make sense to perform equal test upon DELETE.

One additional nice gotcha was that you can easily trace the SQL statements with Entity Framework. In the C# code, have a look at the ConcurrencyContext_CustomConnectionString class file. It adds an additional constructor to the context but also adds a method to the Log property for writing the logging information to Debug.

C#
/// <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);
   }
}

References

History

  • 23rd March, 2017: Created.
  • 2nd April, 2017: Oracle alternative added.
  • 10th April, 2017, additions to C# version
    • Login screen added
    • Added an example using EF6
    • Added an example how to fetch the new rowversion within the same call that updates the database
  • 1st May, 2017: Removed leftovers from fixed connection string, enhancements in terminological accuracy.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
QuestionInteresting... One more good reason to use Oracle. Pin
Member 956162025-Mar-17 23:32
Member 956162025-Mar-17 23:32 
AnswerRe: Interesting... One more good reason to use Oracle. Pin
Klaus-Werner Konrad27-Mar-17 7:11
Klaus-Werner Konrad27-Mar-17 7:11 
GeneralRe: Interesting... One more good reason to use Oracle. Pin
Member 956162027-Mar-17 8:46
Member 956162027-Mar-17 8:46 
AnswerRe: Interesting... One more good reason to use Oracle. Pin
Wendelius2-Apr-17 1:49
mentorWendelius2-Apr-17 1:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.