创建索引以解决表上的死锁。

本文介绍了创建索引以解决表上的死锁。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！   我已经确定了一个永远是死锁的受害者的表，我发现有一个范围可以使用索引来避免死锁。我想检查哪些索引和顺序将提供最佳性能以及帮助避免死锁：  这里是查询和表结构：  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 解决方案 - 使用更新中的列创建索引 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.Table1SET RequestDemographicStatusID = @p1WHERE 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]GOALTER TABLE [dbo].[RequestDemographic] ADD CONSTRAINT [DF_RequestDemographic_CreatedDatetime] DEFAULT (sysdatetime()) FOR [CreatedDatetime]GO 解决方案 -- create an index with the columns from your updateCREATE NONCLUSTERED INDEX IX_Table1_UpdatePerf ON dbo.Table1 (RequestDemographicStatusID, CrosswalkDomainID, EntityTypeID, SourceID)-- suggestions for improving performance of your update-- Get scalar valuesDECLARE @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.Table1SET RequestDemographicStatusID = @p1WHERE RequestDemographicStatusID = @RequestDemographicStatusIDAND CrosswalkDomainID = @CrosswalkDomainID-- use exists rather than inAND EXISTS (SELECT 1 FROM dbo.EntityTypes et WHERE Table1.EntityTypeID = et.EntityTypeID)AND SourceID = @SourceID 这篇关于创建索引以解决表上的死锁。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！