死锁的主要原因是什么，可以预防吗？

Question

tsells

Asked: 2011-10-14 09:58:42 +0800 CST2011-10-14 09:58:42 +0800 CST 2011-10-14 09:58:42 +0800 CST

启用触发器时缓慢删除记录

772

认为这是通过下面的链接解决的 - 解决方法 - 但补丁没有。与 Microsoft 支持合作解决。

http://support.microsoft.com/kb/2606883

好的，所以我有一个问题想扔给 StackOverflow 看看是否有人有想法。

请注意，这是使用 SQL Server 2008 R2

问题：启用触发器时，从包含 15000 条记录的表中删除 3000 条记录需要 3-4 分钟，而禁用触发器时只需 3-5 秒。

表设置

两张表，我们将称为 Main 和 Secondary。次要包含我要删除的项目的记录，因此当我执行删除时，我会加入到次要表中。一个进程在删除语句之前运行，以使用要删除的记录填充辅助表。

删除语句：

DELETE FROM MAIN 
WHERE ID IN (
   SELECT Secondary.ValueInt1 
   FROM Secondary 
   WHERE SECONDARY.GUID = '9FFD2C8DD3864EA7B78DA22B2ED572D7'
);

该表有很多列和大约 14 个不同的 NC 索引。在确定触发器是问题之前，我尝试了很多不同的方法。

开启页面锁定（我们已经默认关闭）
手动收集统计信息
禁用自动收集统计信息
已验证的索引运行状况和碎片
从表中删除聚集索引
检查执行计划（没有显示缺失索引，实际删除的成本为 70%，记录的连接/合并成本约为 28%

触发器

该表有 3 个触发器（插入、更新和删除操作各一个）。我将删除触发器的代码修改为只返回，然后选择一个以查看它被触发了多少次。它在整个操作过程中只触发一次（如预期的那样）。

ALTER TRIGGER [dbo].[TR_MAIN_RD] ON [dbo].[MAIN]
            AFTER DELETE
            AS  
                SELECT 1
                RETURN

回顾一下

启用触发器 - 语句需要 3-4 分钟才能完成
关闭触发器 - 语句需要 3-5 秒才能完成

有人对为什么有任何想法吗？

另请注意 - 不希望更改此架构、添加删除索引等作为解决方案。该表是一些主要数据操作的中心部分，我们必须对其进行调整和调整（索引、页面锁定等），以允许主要并发操作在没有死锁的情况下工作。

这是执行计划 xml（名称已更改以保护无辜者）

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1790.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="185.624" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.42706" StatementText="DELETE FROM MAIN WHERE ID IN (SELECT Secondary.ValueInt1 FROM Secondary WHERE Secondary.SetTMGUID = '9DDD2C8DD3864EA7B78DA22B2ED572D7')" StatementType="DELETE" QueryHash="0xAEA68D887C4092A1" QueryPlanHash="0x78164F2EEF16B857">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="48" CompileTime="20" CompileCPU="20" CompileMemory="520">
            <RelOp AvgRowSize="9" EstimateCPU="0.00259874" EstimateIO="0.296614" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="0.42706">
              <OutputList />
              <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_02]" IndexKind="Clustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_01]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_03]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_04]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_05]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_06]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_07]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_08]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_09]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_10]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_11]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_12]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_13]" IndexKind="NonClustered" />
                <RelOp AvgRowSize="15" EstimateCPU="1.85624E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.127848">
                  <OutputList>
                    <ColumnReference Column="Uniq1002" />
                    <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                  </OutputList>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="15" EstimateCPU="0.0458347" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.12783">
                      <OutputList>
                        <ColumnReference Column="Uniq1002" />
                        <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                      </OutputList>
                      <Merge ManyToMany="false">
                        <InnerSideJoinColumns>
                          <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                        </InnerSideJoinColumns>
                        <OuterSideJoinColumns>
                          <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                        </OuterSideJoinColumns>
                        <Residual>
                          <ScalarOperator ScalarString="[MyDatabase].[dbo].[MAIN].[ID]=[MyDatabase].[dbo].[Secondary].[ValueInt1]">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Residual>
                        <RelOp AvgRowSize="19" EstimateCPU="0.0174567" EstimateIO="0.0305324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15727" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0479891" TableCardinality="15727">
                          <OutputList>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1002" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="0.00392288" EstimateIO="0.03008" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3423.53" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0340029" TableCardinality="171775">
                          <OutputList>
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Index="[IX_Secondary_01]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="SetTMGUID" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="'9DDD2C8DD3864EA7B78DA22B2ED572D7'">
                                        <Const ConstValue="'9DDD2C8DD3864EA7B78DA22B2ED572D7'" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </Merge>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

3 个回答

Voted

Paul White · Answer 1 · 2012-07-12T20:58:25+08:00

SQL Server 2005 中引入的行版本控制框架用于支持许多功能，包括新的事务隔离级别READ_COMMITTED_SNAPSHOT和SNAPSHOT. 即使没有启用这些隔离级别，行版本控制仍用于AFTER触发器（以促进生成表inserted和deleted伪表）、MARS 和（在单独的版本存储中）在线索引。

如文档所述，引擎可以为表的每一行添加一个 14 字节的后缀，以用于任何这些目的。这种行为是相对众所周知的，将 14 字节数据添加到启用了行版本控制隔离级别的在线重建的索引的每一行中也是如此。即使没有启用隔离级别，只有在重新构建时才会向非聚集索引ONLINE添加一个额外的字节。

如果存在 AFTER 触发器，并且版本控制会为每行添加 14 个字节，则引擎中存在优化以避免这种情况，但不会发生ROW_OVERFLOWor分配。LOB实际上，这意味着行的最大可能大小必须小于 8060 字节。在计算最大可能行大小时，引擎假设例如 VARCHAR(460) 列可以包含 460 个字符。

使用触发器最容易看到该行为AFTER UPDATE，尽管相同的原则适用于AFTER DELETE. 以下脚本创建一个最大行内长度为 8060 字节的表。数据适合单个页面，该页面上有 13 个字节的可用空间。存在无操作触发器，因此页面被拆分并添加了版本信息：

USE Sandpit;
GO
CREATE TABLE dbo.Example
(
    ID          integer NOT NULL IDENTITY(1,1),
    Value       integer NOT NULL,
    Padding1    char(42) NULL,
    Padding2    varchar(8000) NULL,
    
    CONSTRAINT PK_Example_ID
    PRIMARY KEY CLUSTERED (ID)
);
GO
WITH
    N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R)
INSERT TOP (137) dbo.Example
    (Value)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM N4;
GO
ALTER INDEX PK_Example_ID 
ON dbo.Example 
REBUILD WITH (FILLFACTOR = 100);
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
CREATE TRIGGER ExampleTrigger
ON dbo.Example
AFTER DELETE, UPDATE
AS RETURN;
GO
UPDATE dbo.Example
SET Value = -Value
WHERE ID = 1;
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
DROP TABLE dbo.Example;

该脚本产生如下所示的输出。单页表分为两页，最大物理行长度从 57 字节增加到 71 字节（= 行版本信息的 +14 字节）。

更新示例

DBCC PAGE显示单个更新的行有Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71，而表中的所有其他行都有Record Attributes = NULL_BITMAP; record Size = 57。

UPDATE用单行替换的相同脚本DELETE会产生如下所示的输出：

DELETE dbo.Example
WHERE ID = 1;

删除示例

总共少了一行（当然！），但最大物理行大小没有增加。行版本信息仅添加到触发器伪表所需的行中，并且该行最终被删除。但是，页面拆分仍然存在。此页面拆分活动是导致存在触发器时观察到的缓慢性能的原因。如果Padding2列的定义从更改varchar(8000)为varchar(7999)，则页面不再拆分。

另请参阅 SQL Server MVP Dmitri Korotkevitch 的这篇博客文章，其中还讨论了对碎片的影响。

tsells · Answer 2 · 2011-11-02T06:16:40+08:00

好吧，这是微软的官方回应……我认为这是一个主要的设计缺陷。

2011 年 11 月 14 日 - 官方回应已更改。如前所述，他们没有使用事务日志。正在使用内部存储（行级别）将更改的数据复制到其中。他们仍然无法确定为什么花了这么长时间。

我们决定使用 Instead Of 触发器来代替 after delete 触发器。

触发器的 AFTER 部分导致我们必须在删除完成后通读事务日志并构建触发器插入/删除表。这是我们花费大量时间的地方，并且是为触发器的 AFTER 部分设计的。INSTEAD OF 触发器将阻止这种扫描事务日志和构建插入/删除表的行为。此外，据观察，如果我们使用 nvarchar(max) 删除所有列，事情会变得更快，这是有道理的，因为它被视为 LOB 数据。请查看以下文章以获取有关行内数据的更多信息：

http://msdn.microsoft.com/en-us/library/ms189087.aspx

摘要：AFTER 触发器需要在删除完成后扫描事务日志，然后我们必须构建和插入/删除表，这需要更多使用事务日志和时间。

因此，作为一项行动计划，这就是我们目前的建议：

A) Limit the number of rows deleted in each transaction or
B) Increase timeout settings or
C) Don't use AFTER trigger or trigger at all or
D) Limit usage of nvarchar(max) datatypes.

mrdenny · Answer 3 · 2011-10-26T09:55:41+08:00

mrdenny

2011-10-26T09:55:41+08:002011-10-26T09:55:41+08:00

按照计划，一切进展顺利。您可以尝试将删除写为 JOIN 而不是 IN，这将为您提供不同的计划。

DELETE m
FROM MAIN m
JOIN Secondary s ON m.ID = s.ValueInt1
AND s.SetTMGUID = '9DDD2C8DD3864EA7B78DA22B2ED572D7'

但是，我不确定这会有多大帮助。当删除与表上的触发器一起运行时，执行删除的会话的等待类型是什么？

1

启用触发器时缓慢删除记录

你如何mysqldump特定的表？

您如何显示在 Oracle 数据库上执行的 SQL？

如何选择每组的第一行？

使用 psql 列出数据库权限

我可以查看在 SQL Server 数据库上运行的历史查询吗？

如何在 PostgreSQL 中使用 currval() 来获取最后插入的 id？

如何在 Mac OS X 上运行 psql？

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

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

将数组参数传递给存储过程

启用触发器时缓慢删除记录

3 个回答

相关问题