Sql-server – Issue with a “weird” deadlock in SQL server

I'm working with a database that has the characteristics that follow:

GO
/****** Object:  Table [dbo].[ADS_EVENT_TYPE_STR]    Script Date: 1/17/2019 8:20:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ADS_EVENT_TYPE_STR](
    [Event_Type_ID] [int] NOT NULL,
    [Language] [varchar](10) NOT NULL,
    [Event_Type_Name] [nvarchar](255) NULL,
 CONSTRAINT [ADS_EVENT_TYPE_STR_PK] PRIMARY KEY CLUSTERED 
(
    [Event_Type_ID] ASC,
    [Language] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ADS_EVENT]    Script Date: 1/17/2019 8:20:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ADS_EVENT](
    [Event_ID] [varchar](64) NOT NULL,
    [Cluster_ID] [varchar](64) NULL,
    [Server_ID] [varchar](64) NULL,
    [Service_Type_ID] [varchar](64) NULL,
    [Client_Type_ID] [varchar](64) NULL,
    [Start_Time] [datetime] NULL,
    [Duration_ms] [int] NULL,
    [Added_To_ADS] [datetime] NULL,
    [User_ID] [varchar](64) NULL,
    [User_Name] [nvarchar](255) NULL,
    [Session_ID] [varchar](64) NULL,
    [Action_ID] [varchar](64) NULL,
    [Sequence_In_Action] [int] NULL,
    [Event_Type_ID] [int] NULL,
    [Status_ID] [int] NULL,
    [Object_ID] [varchar](64) NULL,
    [Object_Name] [nvarchar](255) NULL,
    [Object_Type_ID] [varchar](64) NULL,
    [Object_Folder_Path] [nvarchar](255) NULL,
    [Top_Folder_Name] [nvarchar](255) NULL,
    [Top_Folder_ID] [varchar](64) NULL,
    [Folder_ID] [varchar](64) NULL,
 CONSTRAINT [ADS_EVENT_Event_ID] PRIMARY KEY CLUSTERED 
(
    [Event_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ADS_USER]    Script Date: 1/17/2019 8:20:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ADS_USER](
    [User_ID] [varchar](64) NOT NULL,
    [User_Name] [varchar](255) NULL,
    [Tenant_ID] [varchar](64) NULL,
    [Cluster_ID] [varchar](64) NOT NULL,
 CONSTRAINT [ADS_USER_PK] PRIMARY KEY CLUSTERED 
(
    [User_ID] ASC,
    [Cluster_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ADS_EVENT_DETAIL]    Script Date: 1/17/2019 8:20:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ADS_EVENT_DETAIL](
    [Event_ID] [varchar](64) NOT NULL,
    [Event_Detail_ID] [int] NOT NULL,
    [Event_Detail_Type_ID] [int] NULL,
    [Bunch] [int] NULL,
    [Event_Detail_Value] [nvarchar](max) NULL,
 CONSTRAINT [ADS_EVENT_DETAIL_PK] PRIMARY KEY CLUSTERED 
(
    [Event_ID] ASC,
    [Event_Detail_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Index [ADS_EVENT_DETAIL_1]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_DETAIL_1] ON [dbo].[ADS_EVENT_DETAIL]
(
    [Event_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ADS_EVENT_DETAIL_2]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_DETAIL_2] ON [dbo].[ADS_EVENT_DETAIL]
(
    [Event_Detail_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ADS_EVENT_DETAIL_3]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_DETAIL_3] ON [dbo].[ADS_EVENT_DETAIL]
(
    [Event_Detail_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [ADS_EVENT_2]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_2] ON [dbo].[ADS_EVENT]
(
    [Client_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ADS_EVENT_3]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_3] ON [dbo].[ADS_EVENT]
(
    [Event_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ADS_EVENT_4]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_4] ON [dbo].[ADS_EVENT]
(
    [Status_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [ADS_EVENT_5]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_5] ON [dbo].[ADS_EVENT]
(
    [Object_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [ADS_EVENT_6]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_6] ON [dbo].[ADS_EVENT]
(
    [Object_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [ADS_EVENT_7]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_7] ON [dbo].[ADS_EVENT]
(
    [Start_Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [ADS_EVENT_8]    Script Date: 1/17/2019 8:20:36 PM ******/
CREATE NONCLUSTERED INDEX [ADS_EVENT_8] ON [dbo].[ADS_EVENT]
(
    [Cluster_ID] ASC,
    [Server_ID] ASC,
    [Service_Type_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO

The issue is that I am having deadlocks errors. I am using SAP business objects to create some reports, and I am capturing the deadlock error with SQL Server Profiler:

<deadlock-list>
 <deadlock victim="process408105468">
  <process-list>
   <process id="process408105468" taskpriority="0" logused="0" waitresource="PAGE: 6:1:2113949 " waittime="39" ownerId="443576461" transactionname="user_transaction" lasttranstarted="2019-01-09T23:42:42.170" XDES="0x4085faf20" lockMode="S" schedulerid="4" kpid="7836" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-01-09T23:42:42.787" lastbatchcompleted="2019-01-09T23:42:42.170" lastattention="1900-01-01T00:00:00.170" hostpid="21168" loginname="PRD_tempadmin" isolationlevel="read committed (2)" xactid="443576461" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtend="4258" sqlhandle="0x02000000985e993782e82d0b110ee7b0ae582e8f01b4eae00000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
SELECT
  dbo.Get_UTC_Time(Event.Start_Time ),
  max( ADS_EVENT_DETAIL_TYPE_STR.Event_Detail_Type_Name  ),
  EventDetails.Event_Detail_Value,
  Datepart(hour,(dbo.Get_UTC_Time(Event.Start_Time))),
  day(dbo.Get_UTC_Time(Event.Start_Time)),
  year(dbo.Get_UTC_Time(Event.Start_Time)),
  month(dbo.Get_UTC_Time(Event.Start_Time)),
  Datepart(dw,(dbo.Get_UTC_Time(Event.Start_Time))),
  convert(bigint,count(distinct Event.User_ID)),
  UserTenantCluster.User_Name,
  ( Event.Object_Folder_Path )+( Event.Object_Name ),
  Event.Object_Name,
  max( ObjectType.Object_Type_Name  ),
  max( EventType.Event_Type_Name  ),
  sum(convert(bigint,isnull(Event.Duration_ms/1000,0)))
FROM
  ADS_EVENT_TYPE_STR  EventType INNER JOIN ADS_EVENT  Event ON (EventType.Event_Type_ID=Event.Event_Type_ID  AND  upper({fn left(EventType.Language,2)})=upper({fn left(&apos;en_US&apos;,2)}))
   LEFT OUTER JOIN ADS_USER  UserTenantCluster ON (UserTenantCluster.User_ID = Event.User_ID  AND  UserTenantCluster.Cluster_ID = Event.Cluster_ID)
   INNER JOIN ADS_EV    </inputbuf>
   </process>
   <process id="process407d908c8" taskpriority="0" logused="1724" waitresource="OBJECT: 6:965578478:0 " waittime="276489" ownerId="443594542" transactionname="user_transaction" lasttranstarted="2019-01-09T23:44:15.077" XDES="0x407688d90" lockMode="IX" schedulerid="4" kpid="3572" status="suspended" spid="71" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-01-09T23:44:15.097" lastbatchcompleted="2019-01-09T23:44:15.093" lastattention="1900-01-01T00:00:00.093" hostpid="2040" loginname="BO_AUDIT" isolationlevel="read committed (2)" xactid="443594542" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="102" stmtend="374" sqlhandle="0x020000007bbfed297d0b8a8a3abbeda3dc07749a051457e70000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P1 varchar(64),@P2 int,@P3 int,@P4 int,@P5 ntext)INSERT INTO ADS_EVENT_DETAIL (Event_ID, Event_Detail_ID, Event_Detail_Type_ID, Bunch, Event_Detail_Value) VALUES(@P1, @P2, @P3, @P4, @P5)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="2113949" dbid="6" subresource="FULL" objectname="SAPBO_AUDIT.dbo.ADS_EVENT" id="lock390651100" mode="IX" associatedObjectId="72057594043695104">
    <owner-list>
     <owner id="process407d908c8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process408105468" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <objectlock lockPartition="0" objid="965578478" subresource="FULL" dbid="6" objectname="SAPBO_AUDIT.dbo.ADS_EVENT_DETAIL" id="lock2e366a900" mode="S" associatedObjectId="965578478">
    <owner-list>
     <owner id="process408105468" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process407d908c8" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Please help me to understand why these two queries are causing deadlocks, they refer to different tables.

The second query is using the table ADS_EVENT_DETAIL, and that table is not being used in the first one, besides the first query is only a SELECT and even they are using different users.

So then why I am getting deadlocks?

Thanks in advance for your kind support.

Best Answer

The deadlock graph looks to have cut off a portion of the input buffer, and I suspect your query is accessing ADS_EVENT_DETAIL.

The input buffer section of the first query, at the cutoff, says:

INNER JOIN ADS_EV    </inputbuf>

Which could be ADS_EVENT_DETAIL, and in the SELECT statement, there is a column selected:

EventDetails.Event_Detail_Value

This column only exists in ADS_EVENT_DETAIL according to your schema script. Use the sqlhandle value from the deadlock report to get the full query from sys.dm_exec_sql_text if it is still in cache and analyse the query for optimisations.

The JOIN syntax on ADS_EVENT_DETAIL will be of particular interest, given you're indexing three ID columns in that table, but the query is returning the NVARCHAR(MAX) field which is not included in any of the indexes. You may be inefficiently scanning the clustered index or performing key lookups which could be slowing down your SELECT statement and contributing to the deadlocks.

Given you're SELECTing and INSERTing the same table in two different sessions, there is always the potential for deadlocks. You may not be able to eliminate them, and you may need to look at coding your applications with retry logic when a deadlock is encountered.

Also, you should assess your reporting requirements from the perspective of snapshot isolation. Investigate this, and if suitable for your requirements, this may help you alleviate the deadlocks.

Best Answer

Related Solutions

Sql-server – How to handle deadlock issue sql server 2008

Sql-server – Parent-Child Tree Hierarchical ORDER

Related Question