我们有一个问题,有一些僵局,我发布了这个
question.
有一些帮助和大量的搜索,我相信我想出了发生了什么.为了解决死锁而不用控制锁升级,我需要了解为什么sql server在插入一行时锁定整个表.
这是我的insert语句(重命名变量)：
DECLARE
@Type1 INT = 11,@Type2 INT = NULL,@Value1 VARCHAR(20) = '0',@Value2 VARCHAR(20) = '0',@Value3 VARCHAR(20) = '0',@Value4 VARCHAR(20) = '0',@Date1 DATETIME = '2011-11-25',@Date2 DATETIME = '2011-11-25',@Value5 NVARCHAR(50) = '',@Value6 NVARCHAR(50) = '',@Type3 INT = NULL,@Value7 VARCHAR(20) = '4',@Type4 INT = 4,@Type5 INT = 15153,@Type6 INT = 3,@Type7 INT = 31,@Type8 INT = 5976,@Type9 INT = 5044,@Guid1 UNIQUEIDENTIFIER = 'a8293471-3hb4-442b-844f-44t92f17n67s',@Value8 VARCHAR(200) = '02jfgg55savolhffr1mkjf45',@value10 INT = 1,@Option2 BIT = 0,@Value9 VARCHAR(20) = null,@Option1 BIT = 0
insert into dbo.OurTable
(
Type1,Type2,Value1,Value2,Value3,Value4,Date1,Date2,Value5,Value6,Type3,Value7,Type4,Type5,Type6,Type7,Type8,Type9,value10,Col1,Col2,Col3,Col4,Value8,Option2,Value9
)
values
(
CASE
WHEN [dbo].[GetType](@Type1,null) = 6 AND @Option1 = 1 AND [dbo].[GetType](@Type4,0) <> 1
THEN 7
ELSE [dbo].[GetType](@Type1,null)
END,[dbo].[GetType](@Type2,null),case when @Value1 = 'null' then null else CAST(@Value1 as numeric(18,6)) end,case when @Value2 = 'null' then null else CAST(@Value2 as numeric(18,case when @Value3 = 'null' then null else CAST(@Value3 as numeric(18,case when @Value4 = 'null' then null else CAST(@Value4 as numeric(18,[dbo].[GetDate](@Date1,[dbo].[GetDate](@Date2,@Value5,@Value6,[dbo].[GetType](@Type3,case when @Value7 = 'null' then null else CAST(@Value7 as numeric(18,[dbo].[GetType](@Type4,@Type6,case when LOWER(@Type7) = 'null' then null else @Type7 end,@Type5,@Type9,@Type8,@value10,GETDATE(),[dbo].[GetGuid](@Guid1),@Value8,@Option2,case when @Value9 = 'null' then null else CAST(@Value9 as int) end
)
如果我在事务中运行此语句,然后在提交之前查询sys.dm_tran_locks,我将获得属于该会话的10233行.
SELECT * FROM sys.dm_tran_locks l WHERE l.resource_type <> 'DATABASE' AND l.request_session_id = 65
65是测试时我当前窗口的会话ID.
另外,如果我看到表锁定(这是我的死锁的原因),我可以看到它把X锁放在了OurTable的表上.
resource_type resource_associated_entity_id Name resource_lock_partition request_mode request_type request_status OBJECT 290100074 OurTable 0 X LOCK GRANT OBJECT 290100074 OurTable 1 X LOCK GRANT OBJECT 290100074 OurTable 2 X LOCK GRANT OBJECT 290100074 OurTable 3 X LOCK GRANT OBJECT 290100074 OurTable 4 X LOCK GRANT OBJECT 290100074 OurTable 5 X LOCK GRANT OBJECT 290100074 OurTable 6 X LOCK GRANT OBJECT 290100074 OurTable 7 X LOCK GRANT OBJECT 290100074 OurTable 8 X LOCK GRANT OBJECT 290100074 OurTable 9 X LOCK GRANT OBJECT 290100074 OurTable 10 X LOCK GRANT OBJECT 290100074 OurTable 11 X LOCK GRANT OBJECT 290100074 OurTable 12 X LOCK GRANT OBJECT 290100074 OurTable 13 X LOCK GRANT OBJECT 290100074 OurTable 14 X LOCK GRANT OBJECT 290100074 OurTable 15 X LOCK GRANT
我不知道这是由于lock escalation还是从开始就要求从桌上排除锁定.无论如何,这导致我遇到僵局的麻烦.
在一张桌子上有16个锁定行的原因是因为lock partitioning.
我的问题是,为什么它不要求桌上的意图排他锁(IX)？相反,它要求排他锁.如何防止这种情况？我在调整顾问中没有调整提示,我已经尝试过了.
编辑
OurTable上有一个插入触发器,它更新了OurTable3上的一个字段.看起来像这样：
UPDATE OurTable3 SET Date1 = NULL
FROM OurTable3 as E
JOIN OurTable2 as C on E.Id = C.FKId
JOIN OurTable as ETC on ETC.FKId = C.Id
AND (ETC.Date2 IS NULL OR CAST(ETC.Date2 AS DATE) > E.Date1)
AND ETC.Type1 = 1
如您所见,它不会更新OurTable,但查询OurTable以更新OurTable3中的正确行.
解决方法
我找到了答案.我们团队中的开发人员有一点失误(我总是责怪别人:-).
我可能应该已经知道了这个答案,因为在另一个问题中,马丁·史密斯再次指出我应该检查ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS.但是当时我们认为partitionid与索引id相关,我只检查了该索引.
我可能应该已经知道了这个答案,因为在另一个问题中,马丁·史密斯再次指出我应该检查ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS.但是当时我们认为partitionid与索引id相关,我只检查了该索引.
我所做的是创建一个具有相同数据的新表.效果不见了,我在新桌上只有正确的IX锁.然后我创建了每个索引,并在每个创作之间进行测试,直到我突然再次发挥作用.
我在OurTable上找到了这个索引：
CREATE NONCLUSTERED INDEX [IX_OurTable] ON [dbo].[OurTable]
(
[Col1] ASC,[Col2] ASC,[Col3] ASC,[Col4] ASC,[Col5] ASC
)
INCLUDE ( [Col6],[Col7],[Col8],[Col9]) WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = OFF,ALLOW_PAGE_LOCKS = OFF,FILLFACTOR = 90) ON [PRIMARY]
GO
使用ALLOW_ROW_LOCKS = OFF和ALLOW_PAGE_LOCKS = OFF,显然我们会对插入和选择产生这种影响.
感谢您的意见,并非常感谢Martin真正帮助我解决这些死锁问题.
