Deep Dive on Locks & Deadlocks for Application Developers
Вставка
- Опубліковано 31 тра 2024
- Check out Dometrain and use code ZORAN for 15% off any course ► dometrain.com/?coupon_code=ZORAN
Download source code ► / zoranhorvat
Join Discord server with topics on C# ► codinghelmet.com/go/discord
Enroll course Beginning Object-Oriented Programming with C# ► codinghelmet.com/go/beginning...
Subscribe ► / @zoran-horvat
How far are you ready to go as an application developer in understanding the processes that happen inside the relational database? Will you hide behind a database access library or an ORM and pretend the database will cope with any request the application passes down?
This video explains the trouble an application developer must go through to ensure that concurrent application use does not damage data or cause a deadlock. No matter what access method you use, this demo will show you the rules to obey. Otherwise, errors of all kinds will happen, including damage to data, deadlocks, and concurrent update errors.
⌚ 00:00 Intro
⌚ 01:18 Relying on Implicit Transactions
⌚ 08:32 Using Pessimistic Locking
⌚ 11:01 Avoiding Deadlocks
⌚ 17:44 Using Optimistic Locking
⌚ 24:03 Recovering from Concurrent Updates
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
👨 About Me 👨
Hi, I’m Zoran, I have more than 20 years of experience as a software developer, architect, team lead, and more. I have been programming in C# since its inception in the early 2000s. Since 2017 I have started publishing professional video courses at Pluralsight and Udemy and by this point, there are over 100 hours of the highest-quality videos you can watch on those platforms. On my UA-cam channel, you can find shorter video forms focused on clarifying practical issues in coding, design, and architecture of .NET applications.❤️
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
⚡️COPYRIGHT NOTICE:
The Copyright Laws of the United States recognize a “fair use” of copyrighted content. Section 107 of the U.S. Copyright Act states: “Notwithstanding the provisions of sections 106 and 106A, the fair use of a copyrighted work, including such use by reproduction in copies or phono records or by any other means specified by that section, for purposes such as criticism, comment, news reporting, teaching (including multiple copies for classroom use), scholarship, or research, is not an infringement of copyright." This video and our youtube channel, in general, may contain certain copyrighted works that were not specifically authorized to be used by the copyright holder(s), but which we believe in good faith are protected by federal law and the Fair use doctrine for one or more of the reasons noted above.
#csharp #dotnet #relationaldatabase - Наука та технологія
Check out Dometrain and use code ZORAN for 15% off any course ► dometrain.com/?coupon_code=ZORAN
"Designing Data Intensive Applications" by Martin Kleppman covers all the data storage tradeoffs. Read + Zoran videos = success.
That is one of the best books I ever read. Exceptionally informative and full of great ideas and algorithms. Also well organised and easy to read, despite its advanced level.
Thanks for a good recomendation. Already keen to read it.
"how optimistic can it be when if fails" :DD Thank you for db-series, this channel is backend dev goldmine.
Watching "Avoiding dealocks" part I want to add that the soulution WITH (UPDLOCK) is about rising the lock compatibility level - instead of using the default shared lock on the select statement it's been replaced by more strictible update lock, which is not compatible with other update locks (while shared locks are compatible with each other), so any other transaction that tries to set update lock to same data will wait that first transaction to release its lock.
Btw, there is an optimistic appoarch in MS SQL Server out of the box - Snapshot isolation level OR "read committed snapshot on" database option
This is valuable information. Thank you!
😮what a masterclass...these videos should be mandatory viewing for any developer, no matter what level they are.
Hvala puno Zorane.
Ok. This is a real chalange for my mind. Thank you!
You'll have to be sitting for this :)
If concurrent data access was easy we'd all still write directly to files. Imagine having to implement ARIES for your own project. Databases make it better, but still complicated.
Hi Zoran, really nice video!
Should I implement the pessimistic or optimistic locking in every update operation?
Awesome explanation. I like it a lot! Great video, mr. Horvat!
Excellent explanation, tyvm!
I needed this badly
You are very amazing. Thank you so much for this
One word separates the noobs from the pros: multithreading.
Thank you very much for such a wonderful video, Zoran. You have a talent for explaining complex things in simple terms. I want to ask you, maybe you would be interested in covering in the next video a situation that is perhaps more common in the world of SQL - when it is necessary to update many rows at once? For example, it would be very interesting to know how to perform such an operation using the example of an optimistic lock, for example, if we want to roll back the entire update if we were unable to update at least one record because it has already been changed. Let me share my little experience, perhaps it will also give you a topic for a future video. My boss asked me to do something with a stored procedure that worked to update a product catalog, an important part of which was updating many product records in the database. At the same time, he put forward somewhat contradictory demands: 1 - to speed up the operation of the stored procedure, 2 - to ensure that error messages are displayed when updating for each specific product. I told him that fulfilling the second requirement entails using a loop, which is considered bad SQL practice and, conversely, will slow down the procedure. As a result, I decided to make two procedures - one standard and working quickly, but rolling back all changes if something happened wrong, and the second - with a cycle, which starts if the first one was unable to complete the update entirely. However, I don't know if this was a good decision. Maybe you have thoughts on this matter that you could share with viewers in your next videos.
That is a very interesting topic, which I have encountered several times during my career. The shortest answer is that I always had to find a step-wise solution, rather than updating all the rows at once. Different factors would interfere each time, such as performance issues, slowing down other concurrent users or even causing errors in their transactions... Every problem was different and asked for different tradeoffs.
@@zoran-horvat thanks for your answer and thanks for sharing your experience with us
I'm not sure if I've ever encountered optimistic locking before - I'll have to see if I can find places to make use of it.
And here I thought myself clever in using UPDLOCK with HOLDLOCK (but in read committed isolation) in combo to exclusively lock data I will be modifying very soon and minimise deadlocking from other parts of our application suite.
I love using that combo around GUID keys, particularly ones used for idempotency, as it even lets me claim records that don't exist yet.
Just out of curiosity, is storing ordinal orders something you do often?
My personal preference is for a "Sort Priority" to group things together, kind of how many bug trackers triage bugs into P1, P2, and P3 priorities.
One reason being to minimise this exact case, as I can always generate ordinals on the fly (e.g. via RANK or ROW_COUNT) if necessary.
I used ordinal as a user-controlled field, as the user can legally reorder items in the collection. That is the general, and very frequent, element in my designs.
Regarding all kinds of locks, I am not even a good choice to ask that kind of questions. However, I know enough to cause trouble :)
Optimistic locking is the default in EF Core, for performance reasons and the fact that any ORM makes a clear separation between read and write phases of any operation.
@@zoran-horvat I can say the same for myself when it comes to locks. :)
You effectively did something akin to my trick anyway by using the 'serializable' isolation level, even though it is far more strict.
I really should spend some time investigating EF Core. The place I work at treats anything that seems overly magical with suspicion.
Even Dependency Injection still gets a bit of the ol' side eye due to StructureMap (and another predecessor whose name I cannot recall).
Great stuff. Thank you!
But how can I implement passimistic and optimistic locking using DDD aggregate and EF Cote?
For optimistic locking with EF Core, all you need to do is configure the version field.
For pessimistic locking, you need to begin and commit the transaction on the connection used by the DbContext.
@@zoran-horvat Where should I define the version field? In the Books table (aggregate root) or in the BookAuthors table (entity)?
@zoran-horvat Note that what is repeatable depends on the meaning and intent of the operations not just the operations themselves.
In this case the result of executing just the pessimistic version (where Richard Helm ended on the 1st position and Ralph Johnson ended on the 3rd position) is different compared to the result of the optimistic and repeated version (where Richard Helm ended on the 3rd position and Ralph Johnson ended on the 1st position) which is again different to the scenario where the pessimistic version executed and afterwards the other transaction executed.
Was this meant to be a "switch between two positions" operation or was it meant as "set Richard Helm to be the first" operation or was it actually meant to be a "set a certain order" operation? This kind of thing can make an apparently simple task bubble up to higher decisional levels in an attempt to clarify what the actual intent is and what tradeoffs should be made.
Sometimes these issues can be diminished to a degree by redesigning the whole thing. If instead of consecutive integer numbers we use either big gaps or floating point numbers what was before a two updates can become one update. But now, as those gaps change maybe a process that rebalances the gaps during off-peak time becomes necessary. But even so issues can still arise.
You are right. That is why I said it is repeatable once, and only after a failed attempt. However, that is far from an acceptable general conclusion. We must consider each operation carefully, as you have outlined.
@@zoran-horvat I think this opens up the conversation about idempotent operations and thinking about state in general.
@@AlexandruVoda Yes, though I tried to avoid mentioning idempotent operations here, because swapping the authors alone is not idempotent nor can it be without other conditions around it.
@@zoran-horvat Agreed, that topic has many ramifications and opens many cans of worms like natural vs surogate keys, UUID versions, impedance mismatch with HTTP and many more.
This stuff is so good that I wonder it's for free 😂
I call viewers to sponsor the channel if they wish to help and that is as much as I get from it.