I have two different processes (on different machines) that are reading and updating a database record.
The rule I need to ensure is that the record must only be updated if the value of it, lets say is "Initial". Also, after the commit I would want to know if it actually got updated from the current process or not (in case if value was other than initial)
Now, the below code performs something like:
var record = context.Records
.Where(r => (r.id == id && r.State == "Initial"))
.FirstOrDefault();
if(record != null) {
record.State = "Second";
context.SaveChanges();
}
Now couple of questions
1) From looking at the code it appears that after the record is fetched with state "Initial", some other process could have updated it to state "Second" before this process performs SaveChanges. In this case we are unnecessarily overwriting the state to the same value. Is this the case happening here ?
2) If case 1 is not what happens then EntityFramework may be translating the above to something like
update Record set State = "Second" where Id = someid and State = "Initial"
and performing this as a transaction. This way only one process writes the value. Is this the case with EF default TransactionScope ?
In both cases again how do I know for sure that the update was made from my process as opposed to some other process ?
If this were in-memory objects then in code it would translate to something like assuming multiple threads accessing same data structure
Record rec = FindRecordById(id);
lock (someobject)
{
if(rec.State == "Initial")
{
rec.State = "Second";
//Now, that I know I updated it I can do some processing
}
}
Thanks
In general there are 2 main concurrency patterns that can be used:
Focusing on the optimistic concurrency option, which EF does support, let's compare how your example behaves with and without EF's optimistic concurrency control handling. I'll assume you are using SQL Server.
Let's start with the following script in the database:
create table Record (
Id int identity not null primary key,
State varchar(50) not null
)
insert into Record (State) values ('Initial')
And here is the code with the DbContext
and Record
entity:
public class MyDbContext : DbContext
{
static MyDbContext()
{
Database.SetInitializer<MyDbContext>(null);
}
public MyDbContext() : base(@"Server=localhost;Database=eftest;Trusted_Connection=True;") { }
public DbSet<Record> Records { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
modelBuilder.Configurations.Add(new Record.Configuration());
}
}
public class Record
{
public int Id { get; set; }
public string State { get; set; }
public class Configuration : EntityTypeConfiguration<Record>
{
public Configuration()
{
this.HasKey(t => t.Id);
this.Property(t => t.State)
.HasMaxLength(50)
.IsRequired();
}
}
}
Now, let's test your concurrent update scenario with the following code:
static void Main(string[] args)
{
using (var context = new MyDbContext())
{
var record = context.Records
.Where(r => r.Id == 1 && r.State == "Initial")
.Single();
// Insert sneaky update from a different context.
using (var sneakyContext = new MyDbContext())
{
var sneakyRecord = sneakyContext.Records
.Where(r => r.Id == 1 && r.State == "Initial")
.Single();
sneakyRecord.State = "Sneaky Update";
sneakyContext.SaveChanges();
}
// attempt to update row that has just been updated and committed by the sneaky context.
record.State = "Second";
context.SaveChanges();
}
}
If you trace the SQL, you will see that the update
statement looks like this:
UPDATE [dbo].[Record]
SET [State] = 'Second'
WHERE ([Id] = 1)
So, in effect, it doesn't care that another transaction sneaked in an update. It just blindly writes over whatever the other update did. And so, the final value of State
in the database for that row is 'Second'
.
Let's adjust our initial SQL script to include a concurrency control column to our table:
create table Record (
Id int identity not null primary key,
State varchar(50) not null,
Concurrency timestamp not null -- add this row versioning column
)
insert into Record (State) values ('Initial')
Let's also adjust our Record
entity class (the DbContext
class stays the same):
public class Record
{
public int Id { get; set; }
public string State { get; set; }
// Add this property.
public byte[] Concurrency { get; set; }
public class Configuration : EntityTypeConfiguration<Record>
{
public Configuration()
{
this.HasKey(t => t.Id);
this.Property(t => t.State)
.HasMaxLength(50)
.IsRequired();
// Add this config to tell EF that this
// property/column should be used for
// concurrency checking.
this.Property(t => t.Concurrency)
.IsRowVersion();
}
}
}
Now, if we try to re-run the same Main()
method we used for the previous scenario, you will notice a change in how the update
statement is generated and executed:
UPDATE [dbo].[Record]
SET [State] = 'Second'
WHERE (([Id] = 1) AND ([Concurrency] = <byte[]>))
SELECT [Concurrency]
FROM [dbo].[Record]
WHERE @@ROWCOUNT > 0 AND [Id] = 1
In particular, notice how EF automatically includes the column defined for concurrency control in the where
clause of the update
statement.
In this case, because there was in fact a concurrent update, EF detects it, and throws a DbUpdateConcurrencyException
exception on this line:
context.SaveChanges();
And so, in this case, if you check the database, you'll see that the State
value for the row in question will be 'Sneaky Update'
, because our 2nd update failed to pass the concurrency check.
As you can see, there isn't much that needs to be done to activate automatic optimistic concurrency control in EF.
Where it gets tricky though is, how do you handle the DbUpdateConcurrencyException
exception when it gets thrown? It will largely be up to you to decide what you want to do in this case. But for further guidance on the topic, you'll find more information here: EF - Optimistic Concurrency Patterns.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With