Deadlocks: Lets Do One, Understand It, and Fix It - Brent Ozar

[Music] hello party people and welcome to my session at sql bits today we're going to be talking about deadlocks i don't know about you but when i was a data professional and i used that term kind of loosely when i was a database professional i i didn't really know what to do whenever i got an alarm about deadlocks i would get an email or something for my monitoring tool and be like yo you've got a deadlock and i didn't really know what i was supposed to do about that i didn't know how to reliably reproduce them it seemed like they would just happen somewhere out of nowhere and i wasn't really sure how to trigger them every time to make them happen again and again let alone what i was supposed to do about them whenever i got those uh warnings so inside the next span of the next 45 minutes what i'm going to do is build a really simple deadlock between two different queries i'm going to solve that by changing the query then i'm going to build a progressively harder deadlock using a stored procedure that ends up dead locking itself then i'm going to talk about a few different ways to fix that as well after you understand what causes deadlocks and what you should probably do to the code or to the indexes to fix it then you're going to be bewildered about what should i do where should i go look in my code to find this happening and i'll show you a couple of free tools that'll make your life way easier so let's go hop into demos and go get started so i'm going to start here i'm using sql server 2019 everything that i'm using i'm using latest and greatest as patches it can possibly get but everything that i'm about to show you here has really been pretty much true since the dawn of time i'll tell you when things veer off from different past versions so i'm going to start over on the left hand side by creating a couple of tables i'm going to create a table called lefty and righty and i'm just going to put a row in each table so you can walk through these exact same pro scripts yourself i'll give you the link too as well in your sql bits presentation notes so i'm going to start by dropping this table if it already exists and go put in rows and lefty and righty then i'm going to start doing a transaction on the left hand window i am going to start a transaction on the left-hand window i am locking the lefty table i'm sure you and your life you've never actually typed to begin tran and then walked away and like dropped the microphone what i'm doing here is just kind of slowing down time to show you how these kinds of things happen so the left hand side i've locked the left hand table over here on the right hand side i'm going to lock the right hand table you'll notice i'm also doing a begin tran over here and i'm not doing a commit let's go highlight that query hit execute and right now things are totally okay these two sessions aren't blocking each other this is just the word locking not the word blocking there's no b inside there it's just locking and this can go on for forever let's make things a little bit more complex over in the left-hand window let's go continue our transaction and let's try to update the right-hand table three two one execute he's not making progress because of course he's waiting for a lock that this other session on the right hand side already has he already took a lock on the right hand side table this is now blocking with a b this is the thing that your users usually complain about the session isn't about blocking though by default there's no blocking timeout in sql server that query over on the left is going to wait for forever until this guy finishes his work and over on the right-hand side we would be okay as long as the right-hand side did one of two things if he either did a commit or if he did a roll back either of those would be fine to show you i'll do one three two one roll back boom he finishes he also goes forward on the left so now he on the left could go forward and commit if he wanted to but let's go back and replay this again and let's do it a little bit differently left hand side let's start the transaction on lefty right hand side let's start the transaction on righty left hand side let's try to go get the lock that he can't have the forbidden lambada over on the left hand side we'll execute that he is now waiting on blocking he's being blocked by this guy over here but instead of doing a commit or a roll back let's do something different let's do something that is destined to fail over on the right hand side let's try to go update the right hand or the left hand table now i'm going to describe what's going to happen because things are going to move really quick once i hit execute over here when i hit execute i'm going to count down from three to one he is going to want a lock that he can't get whoops come back out over here he's going to want that lock that he can't get because lefty already has it over there that lefty table's already locked over there he down here over on this side that lefty is gonna try to lock righty he's still sitting around waiting this is now gonna be a deadlock this is going to be two queries that both have guns pointed at each other i don't know if you've ever seen the movie reservoir dogs from quentin tarantino if you haven't seen it please don't go look it up now because it's terrible and awful for but for those of you who have very bad tastes like i do from reservoir dogs you'll remember the scene where there are a whole bunch of people in a warehouse with guns pointed at each other they're all bad guys and nobody trusts each other nobody wants to put their gun down that's a deadlock nobody's willing to put their lock down so every five seconds and more under certain circumstances sql servers deadlock monitor wakes up looks around sees that we have a reservoir dogs standoff sql server draws its own gun and puts a bullet in whatever query is going to be the easiest to roll back now this happens every five seconds so i can't predict when it's going to happen and i can't predict who's going to lose so i'm going to count down from 3 2 1 and you'll see what happens 3 2 1 execute query over on the left hand side was chosen as the loser if i look over at the left hand side and go in and i'm going to hit enter a few times just to kind of tighten this stuff up it says hey transactions process 68 was deadlocked on lock resources with someone else and he chose poorly please rerun your transaction then the query on the right he's free to go nothing's wrong with him he leaves the warehouse totally intact with all of the loot he can say commit if he likes and he's off to the races this is a deadlock now what you're supposed to do if i go look over here at this error message what you're supposed to do in your application is you're supposed to monitor for error number 1205 and when your application gets error 1205 it's supposed to automatically try to figure out what it should retry that's what books online says and has said the same thing for like 20 years i don't know anyone who actually does that just like i don't know a whole lot of people who eat all of their vegetables especially not in fried form i just had tater tots recently and about recently i mean about half an hour ago so we're supposed to do is we're supposed to check for error 1205 and retry our code there's nothing built into sql server that will retry deadlocks for you automatically most of the time users don't complain about deadlocks because deadlocks solve themselves automatically every five seconds usually they just kind of get used to it and they're like uh it throws errors sometimes and it just gets logged somewhere and we gotta fix it but that's what you're here for is maybe the application isn't handling it correctly your application isn't retrying the transaction later or maybe your manager just said hey we're sick and tired of these deadlock errors why don't you go learn to do something about it now the general school book kind of advice is that you should always work through tables in the same order i don't really care whether you go parent child or child parent but in all of your code remember this is textbook this isn't really brent speaking the textbook says you should work through all the to the tables in the same order every time you write queries you and i both know we're lucky if our queries even compile but setting that aside let's see how we would go fix that simple deadlock if we work through the queries in the same order or work through the tables inside the same order we're going to try this again so now this time around i'm still going to do the same work but i'm going to do it in a slightly different order i'm just going to make sure that i don't have any outstanding transactions here nothing up my short sleeves i'm going to start the transaction on the left hand side i'm going to update the left hand table i'm going to start the transaction on the right side but i'm not going to start with the right hand table this time this time i'm going to start with the left hand table i'm going to work through tables in the same order on both sides let's change our code let's go cut this and move it up to the top so now both sides are working in the same order the transaction on the left hand side already started i'm not sure if i highlighted the begin trans so i'm going to type commit okay so left hand side i'm going to begin tran right hand side i'm going to begin tran in 3 2 one it's not making progress but that's a good thing that's actually strangely a good thing because this means that this query over here can't grab a lock that would stop the query over on the left this is how you fix deadlocks one of the ways we'll talk about others how you fix deadlocks working through tables in the exact same order now we're back to blocking with a b this query over here is blocked he can't make any progress at the beginning so the query over on the left hand side says party time let's do this three two one execute it works three two now let me describe what's gonna happen before i hit three two one things are gonna happen kind of quick on the left hand side i'm gonna hit commit when i count down and you'll notice that over on the right hand side his transaction will suddenly be able to start he'll be able to make progress because the guy on the left has gotten out of the way here we go [Music] and now they both finish well this guy isn't really finished yet he just started his transaction but now he can go on and update righty and then he can commit boom and the deadlocks are gone cheers all around right free guinness for everybody well not exactly because notice that we now have blocking now the query on the right hand side is going to take longer because he's going to wait until the query over on the left finished you remember the first time we did this demo the query on the right went blazing on through all leroy jenkins and just brushed his busted his way on through because he happened to win the deadlock this is kind of the curse of deadlocks when you solve them sometimes you have to solve them by introducing blocking by causing queries to wait in order to get everything that they want before they can make progress but in fairness you're the one who asked me to solve the deadlocking and i solved it you have a different query now that'll be ten thousand dollars or fifteen pounds so twenty quints i don't know exactly how y'all measure things over there heads bushels whatever it is so that was a simple query but now let's take a harder one something that feels more real world i'll close that file close this other guy over here and now i'm going to introduce something a little bit harder a stored procedure over at stack overflow when you go to stackoverflow.com you can upvote or downvote things i'm going to build a stored procedure against the stackoverflow database i'm using the stackoverflow database license to be a creative commons it's totally open source you can go download it for free over at brentozar.com go slash query stack and i have to say that because the kind folks over at stack overflow make this database available for free and i have to make sure to give credit to them for that so i have that store the stored procedure that i've written over against the stack overflow database and i'm going to pretend that this is a stored procedure that upvotes things so let's say that whoever is going to go cast an upvote in the stackoverflow database we want to track who cast the vote and what they were voting up like you just cast a vote up on my answer because you think my answer is brilliant i would like to take a moment to acknowledge you for thanking me you're welcome whatever it's getting complicated so here i'm going to create a stored procedure that takes your id as a voter this isn't gdpr just leave that alone for a minute and then whatever vote or whatever post you want to go cast it on i start my transaction by updating your users account it's stack overflow they have a little last access date that tracks when you last did something over at stack overflow so i'll start by updating whoever's doing the voting to set their last access date to right now i'll go insert a row into the votes table to record that you cast a vote i'll go update the score of whatever you were voting on i'm going to go give an extra point to the question or the answer that you chose to upvote so far so good pretty simple straightforward stored procedure but there's a catch down here just a little bit lower i'm going to touch the users table a second time i'm going to come back and say whoever authored that brilliant question or answer let's go join over to their users account and let's give them a reputation point so for example if you're running this stored procedure to upvote my answer we would update your last access date we would cast the vote and then after we update the post to give my answer an extra point you're also going to update my row in the users table to give me an extra 0.2 this would work just fine as long as two people weren't voting on each other's stuff at the same time let's say just by luck of the draw because i also think your answers are amazing too i'm gonna log in and i'm gonna give you an upvote at the exact same moment in time it's like fate it's like serendipity you and i in a cafe sitting around upvoting each other's posts except that's a voter ring and that's against the rules stack overflow you get the point so you and i are sitting across the table from each other on laptops i upvote yours you upvote mine we're going to have a problem in here if these two queries run at exactly the same time now as much as i would love to sit around and run this over and over again until i get a deadlock i kind of sort of value your time if this was a 90-minute session i might actually do that but because these sessions aren't 90 minutes long i'm only going to introduce in a wait for in here no i'm not trying to pad my session this is just to simulate what happens when a lot of queries run concurrently you're gonna say brent no one would ever put a weight for inside their code and then i would tell you amusing stories about when i've actually found them but my goal here is just to reproduce deadlocks quickly even at low concurrency i'll talk about the weight for in a second and how we might play around with that then finally at the end we get a commit on our transaction down here so let's put the stored procedure into production let's highlight the whole thing put it into production and then i'm going to run it across two different sessions you and i it's not you and i it's a couple of other people are going to upvote each other's stuff so let's copy this and put it in a new window we're going to run them side by side just like we did before so i'm going to run these three two one execute execute now both of these queries have gathered the initial locks that they need and they've now hit the wait for they're getting ready to try to update each other's user accounts and oh it actually happened quickly over there on the left hand side this transaction over here was done chosen as the deadlock victim please rerun the transaction oh that kind of sucks how am i going to fix that well if i go back up to the stored procedure and one of the things that you're going to immediately say because i know what kind of person you are and i've seen you talk to other people's sessions one of the first things that you're going to say is brent why don't you just go remove the weight for let me tell you what remove the weight 4 really means in terms of performance tuning what remove the weight for means is buy faster hardware buy faster disks index your tables better the more that i can do to make this transaction execute more quickly that's the equivalent of taking out the weight for but sooner or later you're going to get a hit a point where you've already invested a ton of money in hardware or you've got the best storage that money can buy or you're in the cloud and you've got the crappiest storage money can buy i kid it's a lot of money for kind of crappy storage and you're gonna have to tune it some other way well what if i try to do all of the locking on the users table at the beginning right now i'm doing users votes posts users what if i try to nail both of the users tables right up at the top of the query let's go see what happens so i'm going to rewrite the stored procedure kind of like a tv shaft i've already got a bun in the i've got another version of the stored procedure so here i've got another version of the stored procedure where i have moved both of the users table updates to the top of the stored procedure remember how during the simple query i suggest work through tables in the same order and you're going to be fine well now i'm working through tables in the same order i'm still not going to be fine i'm doing users users votes posts you'll notice that i've left in the weight for the reason why is again i'm simulating crappy hardware lots of concurrency where people is possible for someone to get this lock and then wait for a millisecond before they get this lock and someone else in another window has already grabbed a lock on this row happens at the millisecond level all the time up in production i just don't want to sit around and wait for that to happen while i'm doing a demo so i still have memories of my tater tots so let's go down here and put this stored procedure into production and then let's try the exact same thing again let's go execute these two in different sessions right hand side i've got this person left hand side i've got this person three two one execute execute [Music] have a sip of my tasty beverage and well curses foiled again so now in this case it's this guy over here you can never really predict which session it's going to be that's the loser unlike real life where we can all pretty well predict who's going to be the loser so what i'm about to do next what i'm about to do next is going to make you very angry if i want to get all of the locks that i want at once sometimes you have to write t sql that will leave a bad taste in your mouth sometimes the ugly looking t sequel is actually the t sequel that works out the best when i'm mentoring my junior developers junior dbas that kind of thing i try to teach them how to write good clean code first try to write best practices ways first but when you hit the point where best practices no longer work you may have to do things that are kind of ugly and clue g and that you're not going to want to see on bathroom walls so i said we're going to work through tables in the same order what if instead of users users what if i update both of the users rows at once remember the whole point of the stored procedure is that you who just logged in you're going to log your last access date and you're casting a vote for someone else that's someone else we need to raise their reputations could i write a query that in one statement i grab locks on both of those well of course i can it's not going to be attractive it's like most of my t sql so let's go take a look at that version of the stored procedure let's go page down a little bit further and now i have this i know i no you're not happy about this what we're doing is we're going to go get the posts that we're voting on and then we're joining over to the users table saying we're looking for all the users either the person who owns the post that we're voting on right now or the person who's logged in and who's casting the vote this is dirty and shameful but it grabs a lock on both of those users at the same time then up in my update statement now [Applause] i know i know i'm not proud of it either but look it's you you go get a conference session sometime on how to fix deadlocks it's not as easy as it looks so here i'm saying if the last act let's go set last access date as case if it's the person casting the vote then go update their last access date otherwise don't touch it at all also go update their reputation if it's the person who owns the post the person we want to give a free point to then give them a reputation point otherwise just leave it as the reputation they already have just for the sake of it i'm also leaving in the wait for to show you that the problem is not the wait for that's never been the problem this whole time the problem was that we were trying to get locks on two different users in two different statements and based on luck of the draw not my dancing based on luck of the draw it's whoever happens to dive in there in between sessions that grabs a lock that's what's causing the deadlocks if i grab both of these locks at once i'm going to be fine let's see how it works let's go put this into production let's go put this stored procedure in and then let's go run them in two different windows again over on the left hand side i have user number 8741 right hand side i've got 149 in three two one execute execute and now [Music] i'd like to thank the academy i don't know what academy over on the left hand side that query went through and he got by you'll notice that the one on the right hand side took longer why because we introduced blocking just like we did in the simple example we introduced blocking and let this guy get the lock that he wanted then he hit the weight for this guy wasn't allowed to get what he wanted until the weight 4 was finished over there thereby simulating crappy hardware crappy storage cloud machines by which i mean crappy storage so here they're both able to finish and we're off to the races so if i had to give you like big general advice let's come back up to that version of the stored procedure here if i had to give you like big general advice the idea is that you work through tables in exactly the same order every time but you also can't go back and touch tables twice sometimes if you're having dead locking due to accessing the same table multiple times in a transaction you may need to get that thing all of the locks that you need inside one statement or what if it's not transactional what if you don't need both of these updated inside a transaction maybe you could pull some stuff out of the transaction and that's what the last version of the stored procedure does here i'm saying i don't really care if this gets transactional consistency or not i don't care if the vote attempt fails let's just go in and update their last access date because they tried to do something let's just set that in i'm not as worried about the transactional stuff down in votes posts and users for reputation points seal that in a transaction but up here just let that finish all by itself that will also accomplish the goal in a perfect world your transaction needs to be a short and sweet and only have the mission critical stuff in it if it's not mission critical just take it out do it somewhere else do it asynchronously so now we have our general advice we now we have our general advice up here's how you go about solving deadlocking now we have a new problem our new problem is that you're looking over at your own applications and you're going oh sweet potato i have no idea where to start first i know that my code is working on all kinds of things out of order i am certainly not getting locks across multiple rows in the same table at once i've never written t sql like that i feel dirty just looking at that so you're looking at this going where do i even begin well that's where things like monitoring tools can come in but a lot of us aren't allowed to get monitoring tools or we're not allowed to put things in place ahead of time that's where there's a really cool stored procedure written by eric darling called sp blitz lock sp blitz lock is totally open source and it piggybacks on top of your extend i never say this right extended events system default health session since sql server 2008 sql servers had this built-in kind of black box recorder thing with extended events it's just tracking all kinds of system health events and sb blitz lock doesn't go all the way back to 2008 but it does go back to 2012 all versions from 2012 forward are supported you don't have to install anything ahead of time the deadlock data is already there all you have to do is go harvest it and query it and here's how it works so i'm going to go run sp blitz lock and i'm not even going to use any parameters there are parameters for doing things like for example setting up your own extended event session and you can do this in azure sql db as well setting up sessions to go to different places on disk that then you can go and pull those extended events and deadlocks out later sp blitz lock returns two sets of results the top set of result is an itemized list of deadlocks i'll be honest i don't go there deadlocks are gross i go down to the bottom down to the you're not going to make a joke on that i go down to the bottom and down here it says here are the tables that have been involved in deadlocks oh i don't even have to like do any digging the next question you're going to ask is well brent how much data does this go or how much history do we have inside here and it's going to depend on your server by by default sql server only keeps a limited number of rows inside the system default extended events health session we need a catchy name for that only keeps a certain amount of history inside there since a recent cumulative update on 2016 i think it is a newer you actually get knobs where you can go and change that and if you want to learn more about that go search for aaron burtrand change extended events retention history probably system health session something to those lines aaron bertrand has written a post on how you keep different amounts of data but it's really easy change it's not that hard it's kind of redundant really easy and not that hard but down here i can't really tell just by here how far my history goes back you can kind of get an idea up in the top section where we're looking at the deadlock details i got stuff going back in there to july on my particular server because it's not that active the more active your server is especially at throwing out errors the more problems that you're going to have the less you're going to see in the deadlock history so down here it gives me how many object deadlocks we had you can see that lefty and righty haven't been involved in that many deadlocks but the users table on mine has been involved in 126 deadlocks and i love this because this tells me if i'm going to go find something in terms of which queer or which tables that i need to go focus on indexing i'm probably going to want to look at the one that has the most deadlocks on it and if i go down further in here there are even sp blitz index commands that you can run on those specific objects eric that nice guy he's even written more info queries so when you scroll down and you're like i wonder what kind of indexes that table has does it have too many does it not have enough i can go look at that with that sp blitz index command going further if i scroll up back up to the top it also tells me which specific indexes are involved in some of my deadlocks going down further it'll also tell me who to pin the blame on it'll tell me that this database server has had 189 instances of deadlocks involving me from management studio don't judge me you're not the boss of me you can go in and see which applications are involved in the deadlocks this is so useful to me when people are like i never query production and then we go in there and all the deadlocks or cassette bozos in there doing begintran or they're running serializable reporting queries in production i have the proof now and i don't even have to go in and look at the deadlock graphs if i continue to go further down it'll also show me things like which stored procedures and queries have been involved in the deadlocks so for example here that usp cast up vote that we were playing around with here we can see here's the name of the stored procedure there's an sp blitz cash query to go get its plan out of the cache right now and start examining how it's written and there's s p blitz query storage uh parameters so you can go check your query store if you have it enabled to see what different versions of the plan have looked like over time i'll be honest that that bottom section there that's all i usually need that bottom section down there gives me the tables the queries involved in it even if they're not stored procedures if they're ad hoc queries notice that there's ad hoc stuff down here i get the sp blitzcash commands to go look at their plan handles or sql handles assuming that their queries are still in cash which is a gamble they're not always in cash that's usually all i need but sometimes i need to look at individual deadlock graphs and that's where the top section comes in bottom sections like a slice and dice business intelligence thing that tells you which tables and queries are involved the top are the specific deadlocks themselves so i'm going to scroll down to the end just to go look at the more recent deadlocks and i'm going to widen these up just a little bit so that we can see them here there are lines in here for every that's not vic is victor these are show me the victims of each deadlock and the other queries involved in the deadlock it shows me the stored prox or ad hoc t-sql whatever it who's doing that you weren't supposed to see that look i was that's for another session and they can be involved in deadlocks too isn't that neat so there you go so here i've got don't ask me why i was running dead shrink file across multiple sessions on different files look my life is complicated you can't judge me you can't judge me life goes on so here i've got stored procedures it's i did not rehearse that for the record that's kind of stunning so here i can see the queries or if they're not stored procedures i can also see like our update lefty and righty i can see the tables that were involved in each of these deadlocks it tells me things like what isolation level the query was running on sometimes i'll catch people who are running in serializable isolation level because they didn't understand the defaults that they were using in.net or people who swear that they were doing dirty reads in production they're like oh yeah i always use no lock i always use read uncommitted i wouldn't want to do any blocking well here i get the proof good excuse me i can see how many transactions they have open what their login name what their host name is the application that they were using i can see whether it was a regular transaction or whether it was a user transaction or an implicit transaction for those bozos who have implicit transactions turned on and then way far over here i have the deadlock graph here's the deal i don't read deadlock graphs are terrible they're nasty obscene ugly but sometimes i have to read deadlock graphs so here's the deal when you click on it and open it it's not going to work the way you expect because sql server management studio can't comprehend that that's actually a deadlock graph so when i click on one oh god xml so what you do is you click file save as then when you're naming it i'm going to say i'm going to put it up on my desktop i'm going to name it as deadlock graph but you got to surround it with double quotes and then dot xdl xdl is the extension name for deadlock graphs so when i name it with dot xdl at the end and i got to put the double quotes or else sql server management studio still appends an xml at the end of it when i save that i can click save and then i can close it and i can burn it and i can go out for beers no i click file and then open recent and it's right in here in that list this is gonna sound really stupid i know but you go click on it again now that you've saved it and oh it's well it's still ugly who wants to read this this is terrible this doesn't do me any good this is where the next free tool comes in century one plan explorer you may have heard of century one plan explorer and you think of it in terms of viewing execution plans but it has another use too it can also read deadlock graphs to show it to you i'm going to close that deadlock graph i'm going to minimize sql server management studio and i've opened up century one plan explorer over here in the background waiting always waiting i'm going to go over and open that deadlock graph that i just saved and when i open it it renders in a circle i don't know why they set the defaults that way i don't know a lot of people who have circular monitors but me myself i don't have a circular monitor so i change a couple of defaults down here at the bottom down here at the bottom instead of circular i'm going to go with layered digraph and layered digraph lays things out a little bit easier for those of us who don't have pie shaped monitors and the other thing you can do on big dead locks when there are a lot of queries involved you can also check the box that says optimize layout and that will pull away the queries that aren't really that heavily involved in the deadlock i don't need it here because there were only two queries involved now here's where things get kind of cool i can hit the play button you see the little play button down there in the corner i can hit the play button and i get animation you're not that impressed i was impressed the first time i clicked on it okay the other thing is cool with this is you can click on individual things inside here because you won't understand what they are until you start clicking and then you get as you're clicking on it now now you get the exact stored procedure name the exact query that was involved the host it was running on the application here's the victim's text data which sounds kind of weird and awkward now that i say it that way the times when it finished who was waiting and who was blocking you get all kinds of really cool stuff in here about who owned the lock who was waiting for it this is the tool that i like to use when i have to read deadlock graphs i don't always read deadlock graphs i almost never read deadlock graphs but when i have to i find this way more useful to me because i'll just go hit play and i'll say okay so who got oh this guy grabbed something first he took an exclusive lock and then this other guy over there wanted to lock all right so what was it that this lock this guy took here first oh he tried to update the reputation of these users and this gets me right to the line in the stored procedure that i need to start working on tuning love it so now let's sum up the things that we talked about inside this session so first if you're the kind of person who just keeps getting emails and warnings about deadlocks and you're not sure what you should do about them if no one's complaining should probably sweep them under the rug because now you understand that in order to fix them you're going to have to roll up your sleeves and start changing the code or changing indexes in some cases depending on how things work i feel like i've only started touching the surface of deadlocks and what you're going to end up doing is once you start using these tools once you start using sp blitz lock and century one plan explorer to analyze which queries you're having that are undergoing deadlocks now then you're going to go and start analyzing the queries and see do i need to consistently work parent child or child parent just pick one way of applying changes to tables throughout your code you're also going to know which tables and stored procedures you need to focus on but you're also going to find outliers you're going to find some kinds of deadlocks that i didn't explain here for example sometimes you'll have deadlocks between readers and writers in which case it might make sense to change your isolation level rcsi and snapshot isolation are both great ways to start solving your deadlocking problems if the deadlock is between a reader and a writer now if your deadlocks are between two writers trying to access the same thing like i showed during this session your isolation level probably isn't going to help a whole lot another thing you can run into is a query the deadlocks itself due to parallelism you can have what's called an intra query parallel deadlock meaning one query with multiple parallel tasks at the same time can try to grab locks block itself and end up being dead how you solve that is also totally different but the way the seed that i would plant in your mind there is when you hit that problem search for intra query parallel deadlocks and that will be the key word that you need in order to start getting fixed solved on that one so thanks for hanging out with me during this session if you're watching live i'll be doing text questions and answers throughout the entire session i hope to see you around at sql bits and see you around in other sessions adios [Music] you

Channel: SQLBits

Views: 2,689

Rating: 5 out of 5

Keywords: Blocking, Cloud, Database engine, On-prem, Optimising, SentryOne, execution plans, indexing

Id: 3EwDn9hqgkg

Length: 44min 0sec (2640 seconds)

Published: Mon May 10 2021