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.
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
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.
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.
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.
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
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
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;
}
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
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;
}
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:
- Instance 1:
- Instance 2:
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.
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
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.
public class Rowversion {
private byte[] theValue;
public byte[] TheValue {
get {
return this.theValue;
}
set {
if (value == null || value.Length != 8) {
throw new System.Exception("Invalid rowversion value");
}
this.theValue = value;
}
}
public Rowversion() {
this.TheValue = new byte[8];
}
public override string ToString() {
StringBuilder sb = new StringBuilder("0x");
foreach (byte item in this.TheValue) {
sb.Append(item.ToString("X"));
}
return sb.ToString();
}
}
Public Class Rowversion
Private _theValue As Byte()
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
Public Sub New()
Me.TheValue = New Byte(7) {}
End Sub
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
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;
}
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
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;
}
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.
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.
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
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
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;
}
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.
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
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
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
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;
}
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
Using SQL Server Management Studio, you can also try updating multiple rows without providing correct old rowversion, for example
UPDATE Concurrency3 SET ValueColumn = ValueColumn + 1;
This leads to an error
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
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
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
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
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..."
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
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.
public partial class ConcurrencyContext : System.Data.Entity.DbContext {
public ConcurrencyContext(string connectionString) : base(connectionString) {
this.Database.Log = WriteLog;
}
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.
