SQL Server，插入一行锁定整个表 [英] SQL Server, insert one row locks whole table

问题描述

我们遇到一些死锁问题，我发布了这个问题.

We have an issue with some deadlock and I posted this question.

在我的帮助下，我进行了大量的搜索，我相信自己已经明白了发生了什么.为了解决死锁而不控制锁升级，我需要了解为什么sql server在插入一行时会锁定整个表.

With some help and a lot of searching myself I believe I figured out what is going on. In order to solve the deadlocks without controlling lock escalation I need to understand why sql server locks the whole table on inserting one row.

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

Here is my insert statement (with renamed variables):

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行.

If I run this statement in a transaction and then query sys.dm_tran_locks before committing I get 10233 rows belonging to that session.

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

65是测试时我当前窗口的会话ID.

65 is the session id of my current window when testing.

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

Also if I look at table locking (which is the cause of my deadlock) I can see that it puts a X lock on the table 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

我不知道是否由于锁定升级而完成了操作，或者如果它从开始请求对表进行排他锁.无论如何，这给我带来了死锁的麻烦.

I don't know if this is done due to lock escalation or if it requests an exclusive lock on the table from start. Anyhow this causes me trouble with deadlocks.

单个表上有16个锁行的原因是由于锁分区.

The reason there are 16 lock rows on a single table is because of lock partitioning.

我的问题是，为什么它不请求表上的意图互斥锁(IX)?相反，它请求排他锁.我该如何预防?在调优顾问中没有调优技巧，我已经尝试过了.

My question is, why doesn't it request an intent exclusive lock (IX) on the table? Instead it requests an exclusive lock. How do I prevent this? I get no tuning tips in the tuning advisor, I have already tried that.

编辑 OurTable上有一个插入触发器，用于更新OurTable3上的一个字段.看起来像这样:

EDIT There is a insert trigger on OurTable which updates a field on OurTable3. It looks like this:

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

As you can see it doesn't update OurTable but query OurTable in order to update the correct row in OurTable3.

推荐答案

我找到了答案.我们团队中某个开发人员的一个小失误(我总是怪别人:-). 我可能应该早就知道答案了，因为马丁·史密斯(Martin Smith)在另一个问题中指出，我应该检查ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS.但是当时我们认为partitionid与索引ID有关，而我只检查了该索引.

I found the answer. A little blunder from a developer in our team (I always blame everybody else :-). I probably should have known the answer already because again, Martin Smith pointed out in the other question that I should check ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS. But at that time we thought that partitionid related to an index id and I only checked that index.

我所做的是用相同的数据创建一个新表.效果消失了，我在新表上只有正确的IX锁.然后，我创建了每个索引，并在每个创建之间进行了测试，直到突然间再次生效.

What I did was creating a new table with the same data. The effect was gone and I only had the correct IX lock on the new table. Then I created every index and tested between every creation until I suddenly had the effect again.

我在OurTable上找到了该索引:

I found this index on 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的情况下，很明显，我们会对插入和选择产生这种影响.

With ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF it's obvious we would have this effect on the insert and also the selects.

谢谢您的评论，也非常感谢Martin，他确实帮助我解决了这些僵局问题.

Thank you for your comments and many thanks to Martin who really helped me to solve these deadlock problems.

这篇关于SQL Server，插入一行锁定整个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！