Everything can be fixed with a query hint (*cough* directive), right? If a certain process is consistently causing deadlocks, a simple ROWLOCK hint can be added to prevent it, right?
Well, for whatever reason, there seems to be a myth out there that when deadlocks come a-knocking, then just throw this little directive at it and all will be well. Today, we get to test that and show what will continue to happen.
First, lets look at what the ROWLOCK actually means:
Specifies that row locks are taken when page or table locks are ordinarily taken.
This seems like a fabulous idea if the deadlocks are occurring against a table involving a massive update. Let’s take a look at a small update scenario involving just a handful of records.
USE tempdb;
GO
IF OBJECT_ID('dbo.Test1') IS NOT NULL
DROP TABLE dbo.Test1;
IF OBJECT_ID('dbo.Test2') IS NOT NULL
DROP TABLE dbo.Test2;
CREATE TABLE dbo.Test1 ( col1 INT );
CREATE TABLE dbo.Test2 ( col2 INT );
INSERT INTO dbo.Test1
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );
INSERT INTO dbo.Test2
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );
GO
BEGIN TRANSACTION;
UPDATE dbo.Test1
SET col1 = col1 * 10
WHERE col1 = 3;
/* query window 2*/
USE tempdb;
BEGIN TRANSACTION;
UPDATE dbo.Test2
SET col2 = col2 * 20
WHERE col2 = 4;
UPDATE dbo.Test1
SET col1 = col1 * 20
WHERE col1 = 3;
COMMIT TRANSACTION;
/* back to the first query window*/
UPDATE dbo.Test2
SET col2 = col2 * 10
WHERE col2 = 4;
COMMIT TRANSACTION;Looking at the code, we can see there are only five rows in each of the tables. In addition, an update will be performed to both col1 and col2 in each table for one specific row. So we are keeping this to a singleton type of update, and we are able to force a deadlock by using this setup. 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.
In my environment, I used session 51 and 54 consistently for the deadlock repro. 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. This is what we saw when I ran the setup without the ROWLOCK directive. 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!
/* what about using rowlock hint? let's try it */
USE tempdb;
GO
IF OBJECT_ID('dbo.Test1') IS NOT NULL
DROP TABLE dbo.Test1;
IF OBJECT_ID('dbo.Test2') IS NOT NULL
DROP TABLE dbo.Test2;
CREATE TABLE dbo.Test1 ( col1 INT );
CREATE TABLE dbo.Test2 ( col2 INT );
INSERT INTO dbo.Test1
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );
INSERT INTO dbo.Test2
VALUES ( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );
GO
BEGIN TRANSACTION;
UPDATE dbo.Test1 WITH (ROWLOCK)
SET col1 = col1 * 10
WHERE col1 = 3;
/* query window 2*/
USE tempdb;
BEGIN TRANSACTION;
UPDATE dbo.Test2 WITH (ROWLOCK)
SET col2 = col2 * 20
WHERE col2 = 4;
UPDATE dbo.Test1 WITH (ROWLOCK)
SET col1 = col1 * 20
WHERE col1 = 3;
COMMIT TRANSACTION;
/* back to the first query window*/
UPDATE dbo.Test2 WITH (ROWLOCK)
SET col2 = col2 * 10
WHERE col2 = 4;
COMMIT TRANSACTION;The only change to this setup is that the ROWLOCK directive has been added to the update statements. Examining the sys.dm_tran_locks DMV reveals the same locks being held as was seen without the directive. This shouldn’t be too big of a surprise since the updates are against a single row.
In addition to the same locks being held, we continue to experience the same deadlock problem. Using an Extended Events session to trap deadlock information (similar to the article previously discussed), we can pull out some pretty good info. Let’s examine some of the deadlock data trapped by an XE session.
WITH cte AS ( SELECT t2.event_data.VALUE('(event/@name)[1]', 'varchar(50)') AS event_name
, t2.event_data.VALUE('(event/@timestamp)[1]',
'datetime2') AS StartTime
, t2.event_data.VALUE('(event/data[@name="duration"]/value)[1]',
'bigint') AS duration
, t2.event_data.VALUE('(event/data[@name="database_name"]/value)[1]',
'sysname') AS DBName
, t2.event_data.VALUE('(event/action[@name="sql_text"]/value)[1]',
'varchar(max)') AS sql_text
, t2.event_data.VALUE('(event/action[@name="nt_username"]/value)[1]',
'varchar(500)') AS nt_username
, t2.event_data.VALUE('(event/data[@name="mode"]/value)[1]',
'varchar(15)') + ' ('
+ t2.event_data.VALUE('(event/data[@name="mode"]/text)[1]',
'varchar(50)') + ')' AS mode
, t2.event_data.VALUE('(event/data[@name="object_id"]/value)[1]',
'integer') AS OBJECT_ID
, t2.event_data.VALUE('(event/data[@name="resource_description"]/value)[1]',
'varchar(max)') AS resource_description
, t2.event_data.VALUE('(event/data[@name="resource_owner_type"]/text)[1]',
'varchar(max)') AS resource_owner_type
, t2.event_data.VALUE('(event/data[@name="resource_type"]/text)[1]',
'varchar(max)') + ' ('
+ t2.event_data.VALUE('(event/data[@name="resource_type"]/value)[1]',
'varchar(max)') + ')' AS resource_type
, t2.event_data.VALUE('(event/action[@name="server_principal_name"]/value)[1]',
'varchar(max)') AS server_principal_name
, t2.event_data.VALUE('(event/action[@name="session_id"]/value)[1]',
'varchar(max)') AS session_id
, t2.event_data.VALUE('(event/action[@name="client_pid"]/value)[1]',
'integer') AS client_pid
, t2.event_data.VALUE('(event/action[@name="client_app_name"]/value)[1]',
'varchar(max)') AS client_app_name
, t2.event_data
,ROW_NUMBER() OVER (ORDER BY t2.event_data.VALUE('(event/@timestamp)[1]',
'datetime2')
,t2.event_data.VALUE('(event/@name)[1]', 'varchar(50)')) AS Event_Seq
FROM sys.fn_xe_file_target_read_file('C:\Database\XE\Deadlock*.xel',
NULL, NULL, NULL) t1
CROSS APPLY ( SELECT CONVERT(XML, t1.event_data)
) t2 ( event_data )
)
SELECT c1.event_name AS LockEvent, c2.event_name AS GraphEvent
, c1.StartTime
, DATEADD(MICROSECOND, c1.duration, CONVERT(DATETIME2, [c1].StartTime)) AS EndDate
, c1.duration
, c1.DBName
, c1.sql_text
, c1.nt_username
, c1.server_principal_name
, c1.mode
, c1.OBJECT_ID
, c1.resource_description
, c1.resource_owner_type
, c1.resource_type
, c1.session_id
, c1.client_pid
, c1.client_app_name
, c1.event_data
, graph.query('.') AS EventDeadlockGraph
FROM cte c1
INNER JOIN cte c2
ON c1.Event_Seq = c2.Event_Seq - 1
CROSS APPLY c2.event_data.nodes('(event/data[@name="xml_report"]/value)[last()]/*')
AS deadlock ( graph )
WHERE c1.event_name = 'lock_deadlock'
AND c2.event_name = 'xml_deadlock_report'
ORDER BY c1.StartTime;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. 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. 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). 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. Otherwise, the sql_text does not produce with the deadlock_report event. I leave the rest of the query to the reader to discover and question.
From the EventDeadlockGraph column, we could click the cell and take a close look at the XML generated for the deadlock event. 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.
We see that row locks are still in effect for the update coming from both sides. This further supports that the directive really is just a waste of time in trying to combat this type of deadlock. This is one of those cases where the best option would be to optimize the code and work things out without trying to take a shortcut.
Wrapping Up
Look to optimize the code instead of trying to take a shortcut. In addition, take a look at the deadlocks, the locks held, and the code to get a better understanding of what is truly happening.
