![]() This further supports that the directive really is just a waste of time in trying to combat this type of deadlock. We see that row locks are still in effect for the update coming from both sides. Further, if I choose to save the xml as an XDL file and then reopen it in SSMS, I can see the deadlock graphical report as shown in the following. I leave the rest of the query to the reader to discover and question.įrom the EventDeadlockGraph column, we could click the cell and take a close look at the XML generated for the deadlock event. Otherwise, the sql_text does not produce with the deadlock_report event. Then I was able to join the two together to produce one row per deadlock event and to see the sql_text with the deadlock graph on one row. In this query you can also see that I did a little black magic to match up the two event types from the event session (lock_deadlock and xml_deadlock_report). 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. IF OBJECT_ID ( 'dbo.Test1' ) IS NOT NULL DROP TABLE dbo.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |