Practical .NET and Software Architecture Tips | Microsoft MVP
EF Core doesn't support pessimistic locking out of the box. So, what can you do if you need this feature? I'm sorry (no, I'm not) to have to say this, but you'll have to write SQL. Pessimistic locking is a form of concurrency control where your transactions lock the queried rows until the transaction is committed or rolled back. Unfortuntalely, you can't easily do this with EF Core. The solution? Just write some SQL. Postgres has the SELECT FOR UPDATE statement. SQL Server has the UPDLOCK query hint. Tomorrow, I'll show 46,427+ engineers how to implement pessimistic concurrency control with EF Core. You can subscribe here: https://bit.ly/3x8W1dU P.S. This could be the missing insight you need to stop those pesky concurrency bugs.
Raw SQL is the most understandable in any case. EF core has great support for it!
Great topic, Looking forward to the newsletter 👌
Check out this nuget (source code available) its an easy and intuitive way of using postgres lock in ef core. You just need to call ForUpdate() method on IQueryable and thats all. https://www.nuget.org/packages/Pandatech.EFCore.PostgresExtensions
Yeah EF has a lot of limitations but still I don't want to write much SQL. I've found a way to write "For Update" with select queries in PostgresSQL using LINQ. I'll write a post about it soon.
Haha, the visuals intrigue me 😁
I have to do this on my work api in order to sequence concurrent calls for financial updates
Great Milan Jovanović. I'm currently working at a company that has many related topics on this subject. I look forward to your message
Wait, don't SQL Server and other RDBMS using the locking mechanism by default ?
Thanks for the info! The pessimistic lock is really important to handle concurrency, especially shared lock 😀
Practical .NET and Software Architecture Tips | Microsoft MVP
1y𝗣.𝗦. If you liked this, consider joining The .NET Weekly - my newsletter with 46,000+ engineers that teaches you how to improve at .NET and software architecture. Subscribe here → https://bit.ly/3x8W1dU