One of the criteria is the amount of log used. SQL Server will choose the process with the lowest cost as the victim. It is also usually displayed on the left. The most obvious information visible to us by looking at the Graph is that Process 61 was chosen as the deadlock victim. This is visible when you hover over the process node in the graph, in this case it will display the Transact-SQL command executed by the process.Īccording to msdn this is the type of statement which can be: insert, update, delete, unknown or NOP (no operation) This value corresponds to the request_owner_id field in the sys.dm_tran_locks DMV.Ī pointer to obtain the state if the transaction This is the transaction id of the transaction which owns the request. The amount of space in the transaction log used by the process If a deadlock priority for the specific process was set, this will be displayed here. If it is the parent thread this will be 0, like in this example. If the thread is associated to another SPID and it is not the main thread it will have a number identifying the subthread. This is the SPID (session process id) of the process Process 61 and process 62.Įach process node (indicated in light orange above) displays the following data: We can see from this graph that there are 2 processes involved in this deadlock. In this case the Person.person table is waiting on process 62 to release it. Occurs when resources are waiting on processes. And Spid 62 is waiting to gain access to the Person.Address table. In this case SPID 61 (query 1) is waiting to gain access to the Person.Person table. Occurs when a resource is waiting on a process. The edges indicates the relationships between the resource and process nodes. The deadlock graph consists out of 3 concepts as shown below When this happens the easiest way around it, is to go to the deadlock graph line in profiler, right click and select Extract Event Data… On occasion you might encounter the following error: xdl file, which you can then open in SSMS. On the Events Extraction Settings tab you can choose to have the deadlock events saved to an XML File. The event that we are interested in is the Deadlock Graph. You can use the standard TSQL_Locks template. The easiest way to obtain the deadlock graph is by using Profiler. Various factors are taken into account when a victim is selected, as I mentioned in my previous article on Deadlocks. In this instance query 1 was chosen as the deadlock victim. Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Since neither query can proceed, this results in a deadlock and the deadlock message will be displayed for one of them, which means that that query was terminated and rolled back (deadlock victim) and the other query will complete successfully. Query 2 attempts to do the same but in reverse order. These queries are fairly simple, as you can see query 1 tries to update Person.Address and then waits for 5 seconds, and then tries to update Person.Person. I will cover that in my next article: Understanding the deadlock graph part 2: The XML description.įor the purpose of this demonstration I will create a very simple deadlock in the AdvetureWorks2012 database to allow us to see what we can learn from looking at the deadlock graph. Generally, however looking at the XML version of the file, reveals more information which can make troubleshooting deadlocks a lot easier. rdl format (graphical representation), what you can know by looking at the graph image, this is handy if you just want to get the basic information quickly. In this particular article I will only focus on the Deadlock Graph or the file in. There are multiple ways to go about troubleshooting deadlocks which include: This killed process is known as the deadlock victim. SQL Server routinely performs a check for deadlocks and will choose to kill one of the processes to allow the other to proceed. Since neither query can proceed, intervention is required. If you are reading this I am sure you already know what a deadlock is, but just in case you are new to SQL, a deadlock is when 2 queries are blocking each other in such a way that neither of the two can be completed.įor example, 2 queries are holding shared locks on a table, but both queries need to escalate their locks to exclusive locks to perform an update.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |