![]() It happened at the same time our first query was running again. Okay cool, it starts to rerun it, but it’s unlucky. Our second query it’s like ah, I’ll run my transaction. Let’s go ahead and re-run that sequence just to prove that it really is re-runable. :(īut it gives you the helpful advice that you need to catch that error and re-run your transaction. When we go and we look at the results we can see that oh man, I don’t know about you but this error like the message that you have been chosen as the deadlock victim, no matter how many times I see it it’s always sad. ![]() Our other query, it’s no longer executing. Here it goes and it’s hm, it’s taking a minute for it to run that update. It now updates the Application.StateProvinces table. It didn’t really wait around for me to talk. Our first query gets– and imagine this all happened just at the same time. Let’s get both of its updates on the screen at the same time, there we go. Our first query meanwhile, gets to the point where it’s ready to run its second update and finish up. We’ve got a situation where that second query, both of them are running. It wants to know information for state province Virginia. It is running a select query that joins between Application.Cities, Application.StateProvinces, and Application.Countries. This query is also using WideWorldImporters. We’re gonna open up our new second session. It has updated the Countries tables and incremented the latest recorded population.īefore it can get to the rest of its code though, another query comes in and another query starts in another session. This first query in session one, at this point it’s just done the first of two statements in its transaction. This first session is gonna do two statements as part of this transaction but this deadlock, a deadlock happens when two queries are running at the same time. The first query involved in this deadlock, it comes in and says I am going to start a transaction and I’m updating the country’s table. It does use that WideWorldImporters database, so let’s get in the right database. This code is a rerunable deadlock which is nice and convenient. You’ll see it does change some data but it does it in a way that you can rerun this sequence of events multiple times and get it deadlocked each time without having to re-restore the database or undo the update that you did between them. I don’t like all the code I write, but I like this code because this is a re-runnable deadlock. I really like this code which, I wrote this code. Now that it’s set up, we need a deadlock to happen. To get it actually collecting stuff we need to start it as well. Let’s go ahead and create our events session. Just try to stay alive if things get bad in SQL Server. If you’re under stress don’t worry about it too much. It’s gonna capture that event, save it to a file again on my S drive, that’s where I happen to keep my Extended Events traces on this instance.Īgain with similar kind of default settings it’s okay if you lose an event. Here I’ve got an Extended Events trace that says hey, create a session. In Extended Events there’s just one event that we need to collect, the XML deadlock report. If you really want to catch a deadlock, I recommend setting up a specific trace to capture the deadlock graph It’s set to only capture a certain amount of stuff that can happen because they try to make it not consume too many resources on your SQL Server. Sometimes you’re not lucky and that system health trace, by the time you get to it it’s rolled over because it doesn’t contain history for all time. Sometimes you get lucky and it’s there.īut like I said, sometimes you get lucky. It’s possible that if you’re having deadlocks you could get the information out of the system health trace. It picks up deadlock graphs among other things. Now technically speaking there are some tools on modern SQL Servers where there’s a system health extended events trace running in the background. ![]() We can do it with Ye Old SQL Trace done as a Server Side SQL trace. Now just like the blocked process report, we need to do this with a trace. We can see this in XML, as well as a pictorial representation of the XML. The deadlock graph contains rich information about how it actually mapped out, what queries were involved with it, where did the lock conflicts happen. The best way to do that in SQL Server is to capture the deadlock graph. We want to capture information about deadlocks. Check it out here: ( Gist License info.) Transcript I’ve got a Gist with sample code to trace deadlock graphs in both SQL Trace and Extended Events. ![]() Prefer to use a server side SQL Trace? That’s OK, I’ve got sample code for that, too! Sample code to trace deadlock graphs I’ll show you re-runnable code that you can use to cause a deadlock in WideWorldImporters, then show you how to capture the deadlock graph with an Extended Events trace. The best way to learn to tackle deadlocks is practice
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |