![]() And looking at the session data, one can see that the sql_text from each of the queries will demonstrate both the ROWLOCK directive and the directive-free versions of the query. The results from this query will show us the deadlock graph, the event data, as well as several other pieces of data already parsed from the session data for you. ![]() event_name = 'xml_deadlock_report' ORDER BY c1. nodes ( ) AS deadlock ( graph ) WHERE c1. event_data ) ) t2 ( event_data ) ) SELECT c1. fn_xe_file_target_read_file ( 'C: \Database \XE \Deadlock*.xel',ĬROSS APPLY ( SELECT CONVERT (XML, t1. VALUE ( 'varchar(50)' ) ) AS Event_SeqįROM sys. Let’s examine some of the deadlock data trapped by an XE session. Using an Extended Events session to trap deadlock information (similar to the article previously discussed), we can pull out some pretty good info. In addition to the same locks being held, we continue to experience the same deadlock problem. This shouldn’t be too big of a surprise since the updates are against a single row. Examining the sys.dm_tran_locks DMV reveals the same locks being held as was seen without the directive. The only change to this setup is that the ROWLOCK directive has been added to the update statements. Test2 WITH (ROWLOCK ) SET col2 = col2 * 10 * back to the first query window*/ UPDATE dbo. Test1 WITH (ROWLOCK ) SET col1 = col1 * 20 Test2 WITH (ROWLOCK ) SET col2 = col2 * 20 Test1 WITH (ROWLOCK ) SET col1 = col1 * 10 * what about using rowlock hint? let's try it */ USE tempdb What if I used the ROWLOCK directive (are you guessing already that there would be no change because the deadlock occurs on the lock held for the update that is waiting on the other update to complete?)? Let’s take a look at that too! This is what we saw when I ran the setup without the ROWLOCK directive. In each of the tests, each spid did obtain page locks as well as RID locks (row locks) that were waiting on each other in order to perform an Update. In my environment, I used session 51 and 54 consistently for the deadlock repro. Not only do we see that a deadlock will occur consistently, we would see the following in the sys.dm_tran_locks DMV as well as in the deadlock graphs. So we are keeping this to a singleton type of update, and we are able to force a deadlock by using this setup. In addition, an update will be performed to both col1 and col2 in each table for one specific row. Looking at the code, we can see there are only five rows in each of the tables. IF OBJECT_ID ( 'dbo.Test2' ) IS NOT NULL DROP TABLE dbo. Other layout options are available as well so if you haven’t ever played around with this I recommend giving it a try.IF OBJECT_ID ( 'dbo.Test1' ) IS NOT NULL DROP TABLE dbo. Seeing these types of visualizations was exciting and worth checking out. Click the play button at the bottom left and rewatch the deadlock as it happens. What I love about this is the ability to play through the deadlock step by step. You can expand the top section to see and scan through the deadlock information. When that opens, you should see a screen that looks like this: Once installed, open Sentry One Plan Explorer, click File > Open, and select the deadlock graph that was just saved a moment ago. It’s free, it’s amazing for troubleshooting execution plans, and it’s also useful for deadlock graphs as we’re going to see below. If you don’t already have Sentry One Plan Explorer, got to and download it. From here, paste it into your favorite text editor, remove the “xml_report” text at the beginning of the report, and save it as XML. Right click on the row of information and copy it. Deadlock Graph in Actionįor this example, click off of the Deadlock tab and go back to the Details tab. This is one way to watch for deadlocks and grab the deadlock graph as they occur. Now, if we run through our example from above, we will see the following when the deadlock occurs:Ĭlicking on the Deadlock tab towards the bottom of the window will show us our deadlock graph. Right click on system_health, start the session if it’s not already started, and select Watch Live Data. ![]() In Object Explorer in SSMS, go to Management > Extended Events > Sessions > system_health. How do we obtain a deadlock graph so that we can review information about a deadlock? One way is to use the system_health session in Extended Events. We know we have a deadlock and we need to find out more information. Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Now, if we go back to the first query window and run the remaining UPDATE statement, we’ll get our deadlock: Then, we’ll run the BEGIN TRAN and both UPDATE statements in the second query window. ![]() To create our deadlock, we’ll want to start by running the BEGIN TRAN and first UPDATE statement in the first query window.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |