SQL Server - 使用聚集索引时如何存储数据页

Question

federico

Asked: 2019-01-18 08:58:28 +0800 CST2019-01-18 08:58:28 +0800 CST 2019-01-18 08:58:28 +0800 CST

SQL Server 中的“奇怪”死锁问题

772

我正在使用具有以下特征的数据库：

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

问题是我有死锁错误。我正在使用 SAP 业务对象创建一些报告，并且我正在使用 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>

请帮助我理解为什么这两个查询会导致死锁，它们引用不同的表。

第二个查询正在使用表ADS_EVENT_DETAIL，而第一个查询没有使用该表，除了第一个查询只是一个 SELECT，甚至他们正在使用不同的用户。

那么为什么我会陷入僵局？

提前感谢您的支持。

1 个回答

Voted

HandyD · Answer 1 · 2019-01-18T16:16:33+08:00

死锁图看起来切断了输入缓冲区的一部分，我怀疑您的查询正在访问ADS_EVENT_DETAIL。

第一个查询的输入缓冲区部分，在截止处，说：

INNER JOIN ADS_EV    </inputbuf>

这可能是ADS_EVENT_DETAIL，并且在 SELECT 语句中，选择了一列：

EventDetails.Event_Detail_Value

根据您的架构脚本，此列仅存在于ADS_EVENT_DETAIL中。使用死锁报告中的sqlhandle值从 sys.dm_exec_sql_text 获取完整查询（如果它仍在缓存中）并分析查询以进行优化。

ADS_EVENT_DETAIL上的 JOIN 语法将特别令人感兴趣，因为您正在为该表中的三个 ID 列编制索引，但查询返回的 NVARCHAR(MAX) 字段未包含在任何索引中。您可能正在低效地扫描聚簇索引或执行键查找，这可能会减慢您的 SELECT 语句并导致死锁。

假设您在两个不同的会话中选择和插入同一个表，则始终存在死锁的可能性。您可能无法消除它们，并且您可能需要在遇到死锁时使用重试逻辑来编写您的应用程序。

此外，您应该从快照隔离的角度评估您的报告要求。对此进行调查，如果适合您的要求，这可能会帮助您缓解僵局。

SQL Server 中的“奇怪”死锁问题

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

SQL Server 中的“奇怪”死锁问题

1 个回答

相关问题