Deadlocks are a common issue in SQL Server that can cause performance problems and hinder the execution of queries. Many developers believe that adding a query hint, such as the ROWLOCK directive, can solve deadlock issues. However, this is a misconception that needs to be debunked.
Let’s take a look at a scenario involving two tables, Test1 and Test2, each containing five rows. We will perform updates on a single row in each table, which will result in a deadlock. Without the ROWLOCK directive, the deadlock occurs as expected.
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;
Now, let’s see what happens when we add the ROWLOCK directive to the update statements:
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;
Even with the ROWLOCK directive, the deadlock still occurs. The locks held and the deadlock graph remain the same. This demonstrates that the ROWLOCK directive is not effective in preventing deadlocks in this scenario.
Instead of relying on query hints, it is important to optimize the code and understand the underlying causes of deadlocks. Analyzing the locks held and the code can provide insights into the issue and help in finding a more efficient solution.
Extended Events can be a powerful tool for capturing deadlock information. By using an Extended Events session, you can extract valuable data such as the deadlock graph, event data, and other relevant information. This can aid in troubleshooting and understanding the deadlock events.
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:DatabaseXEDeadlock*.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;
By analyzing the deadlock graph, you can gain a deeper understanding of the deadlock event. Saving the XML as an XDL file and opening it in SQL Server Management Studio (SSMS) allows you to view the graphical report of the deadlock.
In conclusion, it is important to optimize code and understand the underlying causes of deadlocks instead of relying on query hints like ROWLOCK. By using tools like Extended Events and analyzing the deadlock graph, you can effectively troubleshoot and resolve deadlock issues in SQL Server.
For more information on using Extended Events, refer to the official documentation.