1

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.

  • 3
    Foreign keys? If you provide the DDL for all objects involved it may shed some light. Hint: it's probably foreign keys. – LowlyDBA Jan 17 at 17:02
  • 3
    Also if the insert is part of a larger transaction (say, one that also inserts to the event table to get the identity value you must be passing in to @P1)... are you using transactions here? Are you doing it in the application or in a procedure or both or ...? We need more details than just the <deadlock-list>. – Aaron Bertrand Jan 17 at 17:14
  • @LowlyDBA I have added the DDL for all involved objects, my knowledge about sql query is restricted, I tried to identify the culprit foreign keys but without success – federico Jan 17 at 20:50
  • @AaronBertrand I am not pretty sure about your questions, but both queries are been exectued from the application – federico Jan 17 at 20:50
  • 1
    The DDL provided did not include any Foreign Key definitions. By default scripting of foreign keys is disabled if you are using the "Script Create" in SSMS. Please check your scripting options in Tools --> Options and ensure that the foreign keys are being included. – Steve Strelzyk Jan 17 at 21:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.