我们遇到一些死锁问题,我发布了此question

相信在我的帮助下和大量的寻找之后,我终于明白了发生了什么。为了解决死锁而不控制锁升级,我需要了解为什么sql server在插入一行时会锁定整个表。

这是我的插入语句(带有重命名的变量):

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, 6)) end
    ,case when @Value3 = 'null' then null else CAST(@Value3 as numeric(18, 6)) end
    ,case when @Value4 = 'null' then null else CAST(@Value4 as numeric(18, 6)) end
    ,[dbo].[GetDate](@Date1, null)
    ,[dbo].[GetDate](@Date2, null)
    ,@Value5
    ,@Value6
    ,[dbo].[GetType](@Type3, null)
    ,case when @Value7 = 'null' then null else CAST(@Value7 as numeric(18, 6)) end
    ,[dbo].[GetType](@Type4, null)
    ,@Type6
    ,case when LOWER(@Type7) = 'null' then null else @Type7 end
    ,@Type5
    ,@Type9
    ,@Type8
    ,@value10
    ,GETDATE()
    ,GETDATE()
    ,[dbo].[GetGuid](@Guid1)
    ,[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。

另外,如果我看一下表锁定(这是导致死锁的原因),我会看到它在表OurTable上放置了X锁。

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中的正确行。

最佳答案

我找到了答案。我们团队中开发人员的一些小失误(我总是怪别人:-)。
我可能应该早就知道答案了,因为马丁·史密斯(Martin Smith)在另一个问题中指出,我应该检查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,他确实帮助我解决了这些僵局问题。

关于sql-server - SQL Server,插入一行锁定整个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8387024/

10-11 02:53