创建索引以解决表上的死锁。 [英] Create indexes to resolve deadlock on a table.

问题描述

 

我已经确定了一个永远是死锁的受害者的表，我发现有一个范围可以使用索引来避免死锁。我想检查哪些索引和顺序将提供最佳性能以及帮助避免死锁： 

这里是查询和表结构： 

UPDATE dbo.Table1

SET RequestDemographicStatusID = @ p1

WHERE RequestDemographicStatusID =（从dbo.subStatus中选择StatusID，其中（nolock）StatusDescription = 'xxx'）
$
AND CrosswalkDomainID =（选择CrosswalkDomainID FROM dbo.CrosswalkDomain with（nolock），其中CrosswalkDomainDesc ='yyy'和IsActive = 1）

AND EntityTypeID in（SELECT EntityTypeID FROM EntityTypes）

AND SourceID = @ SourceID 

 CREATE TABLE [dbo]。[Table1]（
 [RequestDemographicID] [int] IDENTITY（1,1）NOT NULL，
 [CrosswalkDomainID] [int] NOT NULL，
 [RequestEncounterID] [int] NOT NULL，
 [RequestDemographicStatusID] [ int] NOT NULL，
 [SourceRefreshDatetime] [datetime2]（7）NULL，
 [CreatedDatetim e] [datetime2]（7）NOT NULL，
 [CreatedUserID] [int] NULL，
 [UpdatedDatetime] [datetime2]（7）NULL，
 [CrosswalkRequestTypeID] [int] NOT NULL ，
 [RequestEncounterLineID] [int] NOT NULL，
 [EntityTypeID] [int] NULL，
 [SourceID] [int] NULL，
 CONSTRAINT [PK_RequestDemographic] PRIMARY KEY CLUSTERED 
（
 [RequestDemographicID] ASC 
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
）ON [主要] 
 GO 
 
 ALTER TABLE [dbo]。[RequestDemographic] ADD CONSTRAINT [DF_RequestDemographic_CreatedDatetime] DEFAULT（sysdatetime（））FOR [CreatedDatetime] 
 GO 
 

解决方案

< blockquote>

  - 使用更新中的列创建索引
 CREATE NONCLUSTERED INDEX IX_Table1_UpdatePerf ON dbo.Table1（RequestDemographicStatusID，CrosswalkDomainID，Ent） ityTypeID，SourceID）
 
  - 提高更新性能的建议
  - 获取标量值
 DECLARE @RequestDemographicStatusID int，@ CrosswalkDomainID int; 
 
 SELECT @RequestDemographicStatusID =来自dbo.subStatus的StatusID with（nolock）其中StatusDescription ='xxx'; 
 SELECT @CrosswalkDomainID = CrosswalkDomainID FROM dbo.CrosswalkDomain with（nolock），其中CrosswalkDomainDesc ='yyy'和IsActive = 1; 
 
 UPDATE dbo.Table1 
 SET RequestDemographicStatusID = @ p1 
 WHERE RequestDemographicStatusID = @RequestDemographicStatusID 
 AND CrosswalkDomainID = @CrosswalkDomainID 
  - 使用存在而不是
 AND EXISTS（SELECT 1 FROM dbo.EntityTypes et WHERE Table1.EntityTypeID = et.EntityTypeID）
 AND SourceID = @SourceID 
 
 

Hi , 

I have identified a table which keeps being a victim of deadlocks and I see there is a scope of using Indexes to avoid deadlocks. I'd like to check what indexes and in which order will give optimal performance along with helping to avoid dead locks : 

here is the query and table structure : 

UPDATE dbo.Table1
SET RequestDemographicStatusID = @p1
WHERE RequestDemographicStatusID = (SELECT StatusID from dbo.subStatus with (nolock) where StatusDescription = 'xxx')
AND CrosswalkDomainID = (SELECT CrosswalkDomainID FROM dbo.CrosswalkDomain with (nolock) where CrosswalkDomainDesc = 'yyy' and IsActive = 1)
AND EntityTypeID in (SELECT EntityTypeID FROM EntityTypes)
AND SourceID = @SourceID 

CREATE TABLE [dbo].[Table1](
	[RequestDemographicID] [int] IDENTITY(1,1) NOT NULL,
	[CrosswalkDomainID] [int] NOT NULL,
	[RequestEncounterID] [int] NOT NULL,
	[RequestDemographicStatusID] [int] NOT NULL,
	[SourceRefreshDatetime] [datetime2](7) NULL,
	[CreatedDatetime] [datetime2](7) NOT NULL,
	[CreatedUserID] [int] NULL,
	[UpdatedDatetime] [datetime2](7) NULL,
	[CrosswalkRequestTypeID] [int] NOT NULL,
	[RequestEncounterLineID] [int] NOT NULL,
	[EntityTypeID] [int] NULL,
	[SourceID] [int] NULL,
 CONSTRAINT [PK_RequestDemographic] PRIMARY KEY CLUSTERED 
(
	[RequestDemographicID] 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

ALTER TABLE [dbo].[RequestDemographic] ADD  CONSTRAINT [DF_RequestDemographic_CreatedDatetime]  DEFAULT (sysdatetime()) FOR [CreatedDatetime]
GO

解决方案

-- create an index with the columns from your update
CREATE NONCLUSTERED INDEX IX_Table1_UpdatePerf ON dbo.Table1 (RequestDemographicStatusID, CrosswalkDomainID, EntityTypeID, SourceID)

-- suggestions for improving performance of your update
-- Get scalar values
DECLARE @RequestDemographicStatusID int, @CrosswalkDomainID int;

SELECT @RequestDemographicStatusID = StatusID from dbo.subStatus with (nolock) where StatusDescription = 'xxx';
SELECT @CrosswalkDomainID = CrosswalkDomainID FROM dbo.CrosswalkDomain with (nolock) where CrosswalkDomainDesc = 'yyy' and IsActive = 1;

UPDATE dbo.Table1
SET RequestDemographicStatusID = @p1
WHERE RequestDemographicStatusID = @RequestDemographicStatusID
AND CrosswalkDomainID = @CrosswalkDomainID
-- use exists rather than in
AND EXISTS (SELECT 1 FROM dbo.EntityTypes et WHERE Table1.EntityTypeID = et.EntityTypeID)
AND SourceID = @SourceID 

这篇关于创建索引以解决表上的死锁。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！