7

We have an application inserting data into a table. Unfortunately, we are getting deadlocks and the deadlocks are coming from inserts only. We are seeing the inserts take key locks in a different order on a nonclustered index which is causing the problem.

Why are the inserts behaving this way and what should we do to try to alleviate the deadlocks? Any help or insight is appreciated.

In the example below, there are only two inserts involved, but we have had as many as 4 different inserts involved in a deadlock.

Here is the deadlock graph:

    <deadlock>
    <victim-list>
        <victimProcess id="process3ab355868" />
    </victim-list>
    <process-list>
        <process id="process3ab355868" taskpriority="0" logused="1184" waitresource="KEY: 5:72057594043629568 (6234ed5bf036)" waittime="7493" ownerId="92332106" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:43.060" XDES="0x123699668" lockMode="X" schedulerid="3" kpid="3540" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.333" lastbatchcompleted="2014-10-13T12:37:44.333" lastattention="1900-01-01T00:00:00.333" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92332106" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
        <process id="process14b38c928" taskpriority="0" logused="2564" waitresource="KEY: 5:72057594043629568 (275232b7b238)" waittime="7491" ownerId="92325909" transactionname="implicit_transaction" lasttranstarted="2014-10-13T12:37:39.567" XDES="0x16b38b988" lockMode="X" schedulerid="3" kpid="3668" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-13T12:37:44.337" lastbatchcompleted="2014-10-13T12:37:44.337" lastattention="1900-01-01T00:00:00.337" clientapp="Microsoft JDBC Driver for SQL Server" hostname="" hostpid="0" loginname="" isolationlevel="read committed (2)" xactid="92325909" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="278" stmtend="818" sqlhandle="0x0200000053a65d302154b91e9fee55234669030a42479c050000000000000000000000000000000000000000">
                    INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)
                </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
                    unknown
                </frame>
            </executionStack>
            <inputbuf>
                (@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 decimal(38,1),@P7 int,@P8 int)INSERT INTO table (col1, col2, col3, col4, col5, col6, col7, col8, col9) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS
            </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock17bc3a480" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process14b38c928" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process3ab355868" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057594043629568" dbid="5" objectname="table1" indexname="unique_index" id="lock10735ce00" mode="X" associatedObjectId="72057594043629568">
            <owner-list>
                <owner id="process3ab355868" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process14b38c928" mode="X" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Here is the table DDL:

CREATE TABLE [table1](
    [col0] [int] IDENTITY(1,1) NOT NULL,
    [col1] [int] NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [decimal](15, 4) NULL,
    [col4] [datetime2](7) NOT NULL,
    [col5] [varchar](8) NOT NULL,
    [col6] [varchar](30) NOT NULL,
    [col7] [datetime2](7) NOT NULL,
    [col8] [varchar](8) NOT NULL,
    [col9] [varchar](30) NOT NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [col0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [unique_index] UNIQUE NONCLUSTERED 
(
    [col2] ASC,
    [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col4]
GO

ALTER TABLE table1 ADD  DEFAULT (sysdatetime()) FOR [col7]
GO
7
  • Are these inserts part of a larger transaction? Oct 13 '14 at 19:55
  • The inserts are a part of transactions that each do about 6 or 7 of these same inserts.
    – GoodwinSQL
    Oct 13 '14 at 20:06
  • Could you show all of the code? Tough to troubleshoot with just the deadlock info, because it doesn't capture all of the details. Oct 13 '14 at 20:09
  • Unfortunately, I don't have access to all of the code at the moment.
    – GoodwinSQL
    Oct 13 '14 at 20:26
  • So if the way the code is constructed is the problem, how will you fix it? Oct 13 '14 at 20:30
7

I'm answering my own question here because we finally figured out the problem.

Short Version: We added a third column to the nonclustered index. Deadlocks disappeared.

Long Version:

First, check out James Rowland-Jones' dynamite blog post about lock hashing collision (My explanation will be nowhere close to the quality of his).

From the blog post:

When SQL Server needs to lock a row it creates a hash value that is based on the key values of the table. It is this hash value that is used by the lock manager and means it has a single value to look at when checking to see if a row is already locked.

The lock hash collision occurs when duplicate hash values are generated.

After doing some deeper analysis of many deadlock graphs we noticed that a lot of the WAITRESOURCE Key hash values (the values between the parenthesis) were the same. I started making a short list to keep track:

waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (bb0d06c12baa)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)
waitresource="KEY: 5:72057594043629568 (a27543d90a1a)
waitresource="KEY: 5:72057594043629568 (8328314847df)
waitresource="KEY: 5:72057594043629568 (5b39284eef16)

Sure enough, we were getting a lot of duplicate hash values from different deadlock graphs. I decided to look into the data in the two columns (col2 & col1) of the unique_index index (where the deadlocks were occurring). All of the table DDL is up above in the question.

The col2 column is always going to have a value of 1-6 for a single value in the col1 column. So this started to make sense. There was a limited variety of data available for SQL to generate hash values from - which explains why we were getting duplicate hash values.

One of the fixes JRJ mentioned in the blog was to add an additional column to the index. This adds some diversity to the data and gives more options for the hashing algorithm. Luckily, we were able to add a create_timestamp column to the index and maintain the same uniqueness we had with the two columns. BOOM! After adding the third column to the index, the deadlocks disappeared.

Sidenote: One of the comments on the blog suggested disabling row locking on the index. We tried this first. It DID get rid of the deadlocks, but led to more locking and cut the overall throughput down by about 40-50% so we didn't like this option for our system. However, on a database with lighter workload, this might work fine.

Hopefully this all makes sense.

0

I am not sure whether this could actually be answer but I cannot post this as comment so putting this

If you see your application code its like

@P1 nvarchar(4000)
,@P2 nvarchar(4000),
@P3 datetime2,
@P4 nvarchar(4000),
@P5 nvarchar(4000),
@P6 decimal(38,1),
@P7 int,
@P8 int)
INSERT INTO OBC.MBL_CPU_POS_MSR_ATB (
CRT_S, 
CRT_PGM_C, 
CRT_UID, 
LST_UPD_S, 
LST_UPD_PGM_C,
 LST_UPD_UID, 
MSR_ATB_VAL, 
MBL_CPU_POS_I, 
POS_MSR_TYP_I) 
VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8)                                                                         select SCOPE_IDENTITY() AS GENERATED_KEYS

If you see you have declared @p4,@p5...@P6 as nvarchar() but if you see table definition

CREATE TABLE [OBC].[MBL_CPU_POS_MSR_ATB](
    [MBL_CPU_POS_MSR_ATB_I] [int] IDENTITY(1,1) NOT NULL,
    [POS_MSR_TYP_I] [int] NOT NULL,
    [MBL_CPU_POS_I] [int] NOT NULL,
    [MSR_ATB_VAL] [decimal](15, 4) NULL,
    [CRT_S] [datetime2](7) NOT NULL,
    [CRT_UID] [varchar](8) NOT NULL,
    [CRT_PGM_C] [varchar](30) NOT NULL,
    [LST_UPD_S] [datetime2](7) NOT NULL,
    [LST_UPD_UID] [varchar](8) NOT NULL,
    [LST_UPD_PGM_C] [varchar](30) NOT NULL,

The column in which they are inserting value is declared as varchar and value you are passing is nvarchar. None of your table column has nvarchar data type. If this is true implicit conversion will happen and index scans would happen instead of seeks holding locks(specially when you have update ) for longer duration. I guess this could be possible cause of the deadlock.

There is also trancount=2 which means there are uncommitted transaction and of course bit if query tuning is required.

Both transaction which participated in deadlock were contending same resource waitresource="KEY: 5:72057594043629568 (6234ed5bf036)"

2
  • Good catch on the varchar/nvarchar conversions. We are going to get the developers to fix this; however, this doesn't seem to have any real effect on the inserts (selects / updates would be another story probably). But the general consensus does seem to be to look further into the rest of the transactions.
    – GoodwinSQL
    Oct 13 '14 at 21:00
  • Like I said this might be one of the cause. deadlock is all about missing index, lock being taken for longer duration and some poor code. An incorrect data type can convert seek into scan please read this link how incorrect data type can cause problem social.technet.microsoft.com/wiki/contents/articles/…
    – Shanky
    Oct 13 '14 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.