主页 / dba / 问题 / 199606
Accepted
DForck42
Asked: 2018-03-08 07:36:51 +0800 CST

主键上的非聚集索引会加速删除并防止死锁吗？

  • 772

我有一个非常高流量的数据库。在应用程序中，他们将发出删除，并且这些删除经常会与同一个表上的其他删除死锁。我正在研究解决这个问题的方法，我看到的一个答案是确保删除有最快的记录路径。

目前，所有的删除都遵循这种形式：

(@0 int) delete [dbo].[table] where (table_id = @0)

这些表中的每一个都有一个主键和聚簇索引table_id。

我的问题是，添加一个非聚集索引可以table_id帮助加速这些删除并防止发生死锁吗？

死锁图：

<deadlock victim="process2b53a408c8"><process-list><process id="process2b53a408c8" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" waittime="2034" ownerId="14899585071" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x50746b1c0" lockMode="S" schedulerid="7" kpid="967168" status="suspended" spid="185" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.850" lastattention="1900-01-01T00:00:00.850" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585071" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown     </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)    </inputbuf></process><process id="process218b0a5468" taskpriority="0" logused="284" waitresource="KEY: 19:72057597368270848 (c94c2713ec0f)" waittime="2036" ownerId="14899585063" transactionname="user_transaction" lasttranstarted="2018-03-07T09:47:18.833" XDES="0x1fcd0c4d10" lockMode="S" schedulerid="4" kpid="962372" status="suspended" spid="384" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-07T09:47:18.850" lastbatchcompleted="2018-03-07T09:47:18.847" lastattention="1900-01-01T00:00:00.847" clientapp="redacted_service" hostname="redacted_host" hostpid="164656" loginname="redacted_domain\_SQL_redacted_database_P" isolationlevel="read committed (2)" xactid="14899585063" currentdb="19" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056" databaseName="redacted_database"><executionStack><frame procname="adhoc" line="1" stmtstart="16" stmtend="120" sqlhandle="0x020000001335f1027371c186c8d7405191cdef00ddd0ebe70000000000000000000000000000000000000000">
unknown     </frame><frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame></executionStack><inputbuf>
(@0 int)DELETE [dbo].[redacted_table]
WHERE ([id] = @0)    </inputbuf></process></process-list><resource-list><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock9d21dab80" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process218b0a5468" mode="X" /></owner-list><waiter-list><waiter id="process2b53a408c8" mode="S" requestType="wait" /></waiter-list></keylock><keylock hobtid="72057597368270848" dbid="19" objectname="redacted_database.dbo.redacted_table_2" indexname="PK_redacted_table_2" id="lock4de4b1800" mode="X" associatedObjectId="72057597368270848"><owner-list><owner id="process2b53a408c8" mode="X" /></owner-list><waiter-list><waiter id="process218b0a5468" mode="S" requestType="wait" /></waiter-list></keylock></resource-list></deadlock>

表定义：

create table [dbo].[redacted_table](
    [id] [int] identity(1,1) not for replication not null,
    [loan_id] [int] not null,
    [user_role_id] [int] not null,
    [assigned_by_user_id] [int] not null,
    [out_for_assignment] [bit] not null,
    [assignment_date] [datetime] not null,
    [recognize_date] [datetime] not null,
    [routing_source] [varchar](50) null,
    [request_guid] [uniqueidentifier] null,
 constraint [PK_redacted_table] primary key clustered 
(
    [id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY]
) on [PRIMARY]

go

alter table [dbo].[redacted_table] add  constraint [DF_redacted_table_assignment_date]  default (getdate()) for [assignment_date]
go

alter table [dbo].[redacted_table] add  constraint [DF_redacted_table_recognize_date]  default (getdate()) for [recognize_date]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_redacted_table3] foreign key([loan_id])
references [dbo].[redacted_table3] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table3]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_user_redacted_table4] foreign key([user_role_id])
references [dbo].[user_redacted_table4] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_user_redacted_table4]
go

alter table [dbo].[redacted_table]  with check add  constraint [FK_redacted_table_redacted_table5] foreign key([assigned_by_user_id])
references [dbo].[redacted_table5] ([id])
go

alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table5]
go
sql-server sql-server-2014

2 个回答

  1. Best Answer

    由于您的语句是通过WHERE主键上的子句删除行table_id，因此添加非聚集索引table_id不太可能有帮助，而且很可能会增加发生死锁的次数。

    DELETE FROM ...从表（或聚集索引）中删除该行，以及在该行所在的表上定义的每个非聚集索引。添加额外的索引需要额外的删除操作。

    以这个非常简单的例子为例：

    IF OBJECT_ID(N'dbo.DeleteTest', N'U') IS NOT NULL
DROP TABLE dbo.DeleteTest;
GO
CREATE TABLE dbo.DeleteTest
(
    table_id int NOT NULL
        CONSTRAINT PK_DeleteTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(2000) NOT NULL
        CONSTRAINT DF_DeleteTest_someVal
        DEFAULT ((CRYPT_GEN_RANDOM(2000)))
);
GO

--insert 500 rows
INSERT INTO dbo.DeleteTest DEFAULT VALUES;
GO 500

--turn on "Actual" execution plans
DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 90;

    以上的实际执行计划DELETE：

    在此处输入图像描述 您可能应该将CREATE TABLE语句添加到您的问题中，并通过死锁图添加有关死锁的详细信息。

    现在，如果我们添加一个额外的非聚集索引，并运行另一个删除：

    CREATE INDEX IX_DeleteTest
ON dbo.DeleteTest (table_id);

DELETE 
FROM dbo.DeleteTest 
WHERE table_id = 91;

    我们看到如下计划：

    在此处输入图像描述

    正如您在两张图片中所见，第二次删除的成本几乎是后者的两倍；第一次删除 0.0132841，第二次删除 0.0232851。

    如果您使用SentryOne Plan Explorer（免费！）查看执行计划，您可以看到发生的额外非聚集索引删除操作的数量：

    在此处输入图像描述

    查看您的死锁图，结合您问题中的非聚集索引列表，看起来死锁可能是由同时删除同一页上包含的行引起的。向您的删除添加ROWLOCK提示可能会防止这种特殊的死锁。SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;您还可以在删除操作之前测试使用。请注意，这会更改同一批次中之后发生的所有语句的隔离级别。如果在删除操作后执行其他语句，请确保重置事务隔离级别。

    死锁显示两个语句的等待资源相同，除了行号： waitresource="KEY: 19:72057597368270848 (0e2c6d2527ac)" 行号是括号内的数字。waitresource@Kin在他的回答中展示了一种查看详细信息的好方法

    ROWLOCK
    指定在通常获取页或表锁时获取行锁。当在 SNAPSHOT 隔离级别运行的事务中指定时，除非 ROWLOCK 与其他需要锁的表提示（例如 UPDLOCK 和 HOLDLOCK）结合使用，否则不会获取行锁。

    • 8

  2. 我不这么认为。我在一个简单的表上进行了测试，非集群更改了查询计划，使其看起来更糟。

    到达和提示通常不是可行的方法，但要尝试 

    delete [Table_1] with (rowlock) where pk = @pk;

    需要检查表的外键。

    • 0

相关问题