SQL Server, insert one row locks whole table

Question

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

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
)

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 is the session id of my current window when testing.

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.

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

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.

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

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

Answer 1

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.

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.

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

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.

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