Introduction
This article is an alternative to SQL Server specific article "What You See Is What You Update" and shows the problem (and the resolution) in an Oracle environment. So if you're working in an SQL Server environment, please use the original article.
For description about the problem, pelase refer to "Introduction, the problem" and also to get a picture about the demo program, have a look at "Demo program".
Contents:
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 number NOT NULL,
TextColumn varchar2(100) NULL,
ValueColumn number NULL
);
The fetch is done using the following code
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;
}
And the updates is done as follows
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;
}
As in the original article, 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 the correct version of the row. While SQL Server has a special data type which is automatically changed during each update, Oracle does not have a direct equivalent. However, it's easy to achieve same type of functionality in Oracle. In this example I've used a trigger, which simply increments the version number on each update. Now the table could look like this
CREATE TABLE Concurrency2 (
Id number NOT NULL,
CurrentVersion number NOT NULL,
TextColumn varchar2(100) NULL,
ValueColumn number NULL
);
As you can see there’s an additional column called CurrentVersion. This column now stores the current rowversion. The trigger that updates the value could looke like
CREATE OR REPLACE TRIGGER Concurrency2Trigger
BEFORE INSERT OR UPDATE ON Concurrency2
FOR EACH ROW
BEGIN
:NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
END;
/
The trigger just takes the value from the old row and increments it by one. During an insert the old row does not exist so the old values are NULL. In this case 0 is used in order to get the "seed" value.
When fetching the data the code is basically the same as previously but also the value of the rowversion column is retrieved.
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;
}
So the important thing is that now you always have the value of the CurrentVersion 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.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;
}
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
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;
/
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.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;
}
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 new table-layout could look like this
CREATE TABLE Concurrency3 (
Id number NOT NULL,
CurrentVersion number NOT NULL,
PreviousVersion number NULL,
TextColumn varchar2(100) NULL,
ValueColumn number NULL
);
In the previous example the trigger looked like this
CREATE OR REPLACE TRIGGER Concurrency2Trigger
BEFORE INSERT OR UPDATE ON Concurrency2
FOR EACH ROW
BEGIN
:NEW.CurrentVersion := COALESCE(:OLD.CurrentVersion, 0) + 1;
END;
/
The same trigger was used for both INSERT and UPDATE operations. Since we need to check the version value only during an update, it's easiest to break this into two separate triggers. The first one handling an INSERT
CREATE OR REPLACE TRIGGER Concurrency3_Insert
BEFORE INSERT ON Concurrency3
FOR EACH ROW
BEGIN
:NEW.CurrentVersion := 1;
END;
/
And the second trigger firing on an 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;
/
When the update happens the trigger expects that the client code has provided the value of the rowversion to the column PreviousVersion. Since the trigger fires for all updated rows, it simply investigates if the value in CurrentVersion column of the old row is the same as the value in PreviousVersion column in the new row. If the values differ, an error is raised.
Now if you look at the code at the client side
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;
}
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
You also receive the same error if, for example usinq SQL*Plus, you try to update all rows
UPDATE Concurrency3 SET ValueColumn = ValueColumn + 1;
You receive the following error
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'
Corresponding check with Entity Framework
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 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;
/
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 TNS alias and user name 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 (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;
}
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..."
References
History
- 2nd April, 2017: Alternative created
