Avoid Deadlocks

I’ve been trying to figure out the cause of a SQL deadlock problem, and it dawned on me that preventing deadlocks shouldn’t be that hard. You just have to save data in the same order every time, and the easiest way to do that is to have a repository with one save method.
I'll show you what a deadlock is, and how to prevent them.
#sql #csharp #database
Blog:
betterwithcode.com/
LinkedIn:
/ jeff-zuerlein-2aa67b7
To find deadlocks in your database...
SP_BlitzLock:
github.com/BrentOzarULTD/SQL-...
00:00 Introduction
00:40 Example Aggregate
01:08 What is a deadlock?
02:54 Fixing the repository.
03:51 Tools to find problems.

[@o_glethorpe]

Nice to see someone that is actually teaching something.

[@JeffZuerlein]

Thank you!

[@persiansayed]

Great video! Please keep on making these informative videos. 😊

[@JeffZuerlein]

More to come!

[@MartinoBordin]

The aggregate\aggregate root concept in DDD will naturally lead to this approach. Great video

[@JeffZuerlein]

Thank you! I'm curious, do you generally use EF Core, Dapper, NHibernate, or SQL Client for data access?

[@MartinoBordin]

@@JeffZuerlein In my latest project I'm using EF Core. I believe it has everything nowadays to do proper data access

[@AndrewBoudreau]

​​@@MartinoBordinif you are using ef and have aggregates why not just use dbcontext.savechanges? I always felt like save on repository is making ef somuch less powerful. Thoughts?

[@samerelsahih]

Great video thank you ! If you're using EF, a good way would be in my opinion to use the UOW (Unit of work) pattern. It's easy to use, very straightforward and saves you from similar problems.

[@JeffZuerlein]

Good point! If you're using SQL Client or Dapper, the UOW would probably have a SQL transaction. But with EF Core, I think that logic for order of updates would reside somewhere in the DB Context. Maybe I should read through that source code and find out how it chooses to order update statements. Thank you for the feedback!

[@MrSupasonik]

​@@JeffZuerleinThat's why I prefer Dapper; one has to invest so much time to understand how EF works under the hood, learning its abstractions and constraints (sure, you can skip this, but it will cause a mess in any serious cases). In contrast, with Dapper, it's much quicker and simpler, and you fully understand what will be sent to the SQL Server and how it will be processed there. Anyway, important (and concise, that's greatly appreciated) explanation. Keep up the good work!

[@JeffZuerlein]

I really love Dapper. It gives you all the control you want, but I really want the change tracking that comes with EF. I can't tell you how many unneeded update statements get executed in Dapper because you can't tell if the domain entity mutated.

[@frankbanini8884]

Great video. I just subscribed to your channel.

[@JeffZuerlein]

Awesome! Thank you!

[@chandez]

Just one point. We don't always want to persist the entity and all its aggregates. For example, if we have a section for address changes. I've seen applications that use this technique to the extreme, persisting many aggregates unnecessarily.

[@JeffZuerlein]

You bring up a point that bugs me with repositories that don’t have have access to change tracking. I don’t like to save data to the db when it hasn’t changed. It feels wasteful. EF Core ‘s best feature, to me, is change tracking. I have the tendency to look at an aggregate as a set of data that depends on each other. They have business rules that tie each other together. So if you had an address that could be changed and didn’t affect another entity, then I do think the address would be its own aggregate, with a repository of its own. I think it depends on the business rules.

[@chandez]

@@JeffZuerlein Exactly always depends on the context. 😃

[@aarondcmedia9585]

I never understand why people have videos with some part of the content at the beginning, with no context or discernible meaning. Was there a memo I missed? It happens nearly everywhere, including TV and makes no sense at all.

[@JeffZuerlein]

Touché. Attention spans these days can be very short. Most people only watch the first 10 seconds of a video, so it’s hard to make good intros.

[@aarondcmedia9585]

@@JeffZuerlein yeah I am clearly too old hahaa!

[@ibrahimhussain3248]

That's not possible with the FKs shown. Cannot insert an address without a person. The FK constraint will fail

[@JeffZuerlein]

Good callout...I should have specified update statements.