sql-server - SQL Server，插入一行锁定整个表

Question

我们遇到了一些僵局问题，我发布了此question .

在一些帮助和大量的 self 搜索下，我相信我已经弄清楚发生了什么。为了在不控制锁升级的情况下解决死锁，我需要了解为什么 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，我会得到属于该 session 的 10233 行。

SELECT *
FROM sys.dm_tran_locks l
WHERE l.resource_type <> 'DATABASE' AND l.request_session_id = 65

65是我测试时当前窗口的 session 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 中的正确行。

Answer 1

我找到了答案。我们团队中的开发人员犯了一个小错误(我总是责怪其他人:-)。我可能应该已经知道答案了，因为 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，他确实帮助我解决了这些死锁问题。