Great video! I had a project for my university where I had to manually implement optimistic locking since we weren't using ef core. Good to know there's an option like this.
Good video - one comment; in the case of Postgres it doesn't actually lock rows during a transaction, instead it uses Multi Version Concurrency Control by default....This means the transaction can fail when you commit rather than being temporarily blocked by shared locked row(s) or chosen as a dead lock victim during a transaction.
Thank you for the informative video. However, I have some follow-up questions. 1-Should we update this migration into the database? 2-Can we use Management Studio (SQL Server) to check if this table makes a note of the version at the point in time when a row is updated or deleted? If so, could you guide me on how to do this? 3-Does this functionality support all database providers?
1 - Yes 2 - Of course, look for anything SQL Server related + concurrency 3 - I can say for sure it works with PostgreSQL, SQL Server. It might not be the identical approach, but the concept exists.
does this work in the following scenario: 1. User 1 opens the order and makes some changes on UI before saving 2. In the meantime, User 2 opens the same order, make change on UI and saves them into DB 3. User 1 tries to save his changes How would you handle this scenario? The goal is to have the User 1 presented with what values User 2 changed and to decide to proceed with overwriting User 2 changes or discarding.
In MySql db provider there is no pessimistic locking. I tried to set "Serialisable" isolation level and exception was thrown that generated sql script couldn't be run. Literally the same behaviour without version column.
MySql.EntityFrameworkCore mysql IsRowVersion() has a bug Changing MySql.EntityFrameworkCore to Pomelo.EntityFrameworkCore.MySql should solve your problem Can use HasDefaultValue or change SaveChangesAsync foreach (var entries in _dbContext.ChangeTracker.Entries()) { if (entries.State == EntityState.Modified) { entries.Entity.RowVersion++; } } public interface IRowVersion { long RowVersion { get; set; } } public class Test:IRowVersion { public long RowVersion { get; set; } } builder.Property(e => e.RowVersion).IsRowVersion()
Is Optimistic locking effective for managing available tickets in a ticketing system? How does optimistic locking ensure that on the query side the correct amount of remaining tickets is shown?
How do you implement a case where lets say more than 1 instance of this service is running and you want to prevent even read operations if this row is being processed by some instance? I have a use case where I have schedules that need to be executed in a transactional approach, we have 3 vms and on those 3, 3 instances of the same service are being running with other things being constant, the purpose of the service is to make transactions in the background like a cronjob. What I want to implement is to make the rows that are currently lets say coming in 1 instance should not appear on other vms even for read purposes since that will also make the transactions. How do I implement this? I've tried Serializeable approach, I run 2 instances on my machine and set the debugger somewhere beneath where the schedules are being fetched, but both of them show the same rows in memory.
Hi Milan, great video as usual! I've been just digging into this topic these days. I've got a question, let's suppose the following sequence: 1) User A gets data from a row on his front-end screen and clicks on 'edit' button. 2) User B gets data from the same row and also clicks on 'edit'. 3) User B is faster and clicks 'Ok' for his changes. A is still in editing mode. If I want to reject changes when, some minutes later, user A clicks on 'Ok' (saying that the data he is seeing has changed since it's been retrieved), should I do this control manually, or there's a way of doing it using some EF functionality? I tried some stuff using IsRowVersion() and IsConcurrencyToken() but it's not working the way I expect. Thanks in advance!
Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt
Hopefully i can buy your course
Simple, short, easy to understand video! Goog job, thanks a lot! Stay inspired!
Thanks a lot! :)
Great video! I had a project for my university where I had to manually implement optimistic locking since we weren't using ef core. Good to know there's an option like this.
Always better to use the built-in stuff
Thank Milan for your sharing.
My pleasure
Please show us how to implement retries when DbUpdateConcurrencyException occurs with optimistic lock with RowVersion
Use Polly 😁
@@MilanJovanovicTechis there your video?
Dude! I was just discussing this today with my team!! 😅
I have a feeling for things like that
Sophisticated approach 👍
Just using the built-in stuff
Good video - one comment; in the case of Postgres it doesn't actually lock rows during a transaction, instead it uses Multi Version Concurrency Control by default....This means the transaction can fail when you commit rather than being temporarily blocked by shared locked row(s) or chosen as a dead lock victim during a transaction.
That's a very important remark!
Thank you for the informative video. However, I have some follow-up questions.
1-Should we update this migration into the database?
2-Can we use Management Studio (SQL Server) to check if this table makes a note of the version at the point in time when a row is updated or deleted? If so, could you guide me on how to do this?
3-Does this functionality support all database providers?
1 - Yes
2 - Of course, look for anything SQL Server related + concurrency
3 - I can say for sure it works with PostgreSQL, SQL Server. It might not be the identical approach, but the concept exists.
Thank you milan
Sure thing
does this work in the following scenario:
1. User 1 opens the order and makes some changes on UI before saving
2. In the meantime, User 2 opens the same order, make change on UI and saves them into DB
3. User 1 tries to save his changes
How would you handle this scenario? The goal is to have the User 1 presented with what values User 2 changed and to decide to proceed with overwriting User 2 changes or discarding.
Pass the Concurrency token to the UI and then send it back to the Server when saving changes in the DB
That way only one use can win
That is surprisingly easy!
I make it look easy 😅 No, just kidding. It's easy because EF abstracts away most of the details.
Hi Milan. Would you consider making a video about handling multitenancy in web api?
Yes
@@MilanJovanovicTech Awesome
What are your thoughts on Select for Update Postgres statements?
I love it
In MySql db provider there is no pessimistic locking. I tried to set "Serialisable" isolation level and exception was thrown that generated sql script couldn't be run. Literally the same behaviour without version column.
Strange?
MySql.EntityFrameworkCore mysql IsRowVersion() has a bug
Changing MySql.EntityFrameworkCore to Pomelo.EntityFrameworkCore.MySql should solve your problem
Can use HasDefaultValue or change SaveChangesAsync
foreach (var entries in _dbContext.ChangeTracker.Entries())
{
if (entries.State == EntityState.Modified)
{
entries.Entity.RowVersion++;
}
}
public interface IRowVersion
{
long RowVersion { get; set; }
}
public class Test:IRowVersion
{
public long RowVersion { get; set; }
}
builder.Property(e => e.RowVersion).IsRowVersion()
Is Optimistic locking effective for managing available tickets in a ticketing system? How does optimistic locking ensure that on the query side the correct amount of remaining tickets is shown?
It doesn't - it's there to solve concurrency on the write side
@@MilanJovanovicTech Would a pessimistic locking be more suitable for this?
If the transaction use 2 table header & detail, is enough to implement RowVersion just in header table?
If you wrap everything in a transaction, it should be fine
How do you implement a case where lets say more than 1 instance of this service is running and you want to prevent even read operations if this row is being processed by some instance? I have a use case where I have schedules that need to be executed in a transactional approach, we have 3 vms and on those 3, 3 instances of the same service are being running with other things being constant, the purpose of the service is to make transactions in the background like a cronjob. What I want to implement is to make the rows that are currently lets say coming in 1 instance should not appear on other vms even for read purposes since that will also make the transactions. How do I implement this? I've tried Serializeable approach, I run 2 instances on my machine and set the debugger somewhere beneath where the schedules are being fetched, but both of them show the same rows in memory.
I think this is what you're looking for: www.milanjovanovic.tech/blog/a-clever-way-to-implement-pessimistic-locking-in-ef-core
Is it better to create PipelineBehavior to handle concurrency conflicts and also use Polly for retry?
Catch the exception and retry?
@@MilanJovanovicTech yes, to be more specific catch DbUpdateConcurrencyException
@@MilanJovanovicTech yes, so what do you think?
Could you please make a video about common ef interview questions ❤
Nice idea
Hi Milan, great video as usual! I've been just digging into this topic these days. I've got a question, let's suppose the following sequence:
1) User A gets data from a row on his front-end screen and clicks on 'edit' button.
2) User B gets data from the same row and also clicks on 'edit'.
3) User B is faster and clicks 'Ok' for his changes. A is still in editing mode.
If I want to reject changes when, some minutes later, user A clicks on 'Ok' (saying that the data he is seeing has changed since it's been retrieved), should I do this control manually, or there's a way of doing it using some EF functionality?
I tried some stuff using IsRowVersion() and IsConcurrencyToken() but it's not working the way I expect.
Thanks in advance!
You'd have to read the row version when they both open the page, and pass it along with the rest of the data
What about Mutex?
Won't work in a distributed env, but otherwise fine
👋
Is there a way to add this property to every ef core entity at once?
Probably using reflection
Custom conventions can do this.