federico提出的问题 -dba

federico

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

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

1

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

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，甚至他们正在使用不同的用户。

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

提前感谢您的支持。

federico

Asked: 2017-11-02 14:43:00 +0800 CST

init.ora db_name 和 sqlplus 中查询的区别

0

根据 oracle 文档，我对文件 init.ora 的内容有疑问。

DB_NAME 指定最多 8 个字符的数据库标识符。必须指定此参数，并且必须与 CREATE DATABASE 语句中指定的名称相对应。

在我的理解中，命令 sqlplus 知道连接到哪个数据库，因为它从 init.ora 文件中读取信息。

我的 init.ora 文件如下

db_name='ORCL'
memory_target=1G
processes = 150

我认为是默认的，我认为我的数据库名称会出现在 db_name 参数中，然后我在 sqlplus 中执行查询以获取数据库名称，结果是

我的问题是，如果在 init.ora 中是 ORCL，为什么名称是 BO1？顺便说一句，我没有任何其他的 init.ora

提前致谢

federico

Asked: 2017-09-19 14:06:12 +0800 CST

什么是在线原子模式备份

0

我正在阅读 BO 的 SAP 指南管理员，我对他们使用的一些术语感到有点困惑，无论如何执行在线或热备份。

用同样的话

对于热备份，在线原子模式下使用数据库厂商的备份工具

这是我第一次听说在线原子备份。

这些备份和普通备份有什么区别？？虽然该指南正在谈论进行离线备份，但他们不使用术语在线原子模式，所以我认为管理员应该执行正常备份（对我来说正常）

任何人都可以做一些光吗？

提前致谢

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

init.ora db_name 和 sqlplus 中查询的区别

什么是在线原子模式备份

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

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

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

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

列出指定表的所有列

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

你如何mysqldump特定的表？

使用 psql 列出数据库权限

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

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

federico's questions