Login/Register
Rare and elusive deadlocks (select for update; then update) in case of multiple concurrent transactions
Asked Answered
Solved sqlsql-serverdeadlockdatabase-deadlocks
F

3

7

Database: MSSQL server 2012;

Isolation level: READ_COMMITTED_SNAPSHOT

Now I have a table "COV_HOLES_PERIODDATE". It has a composite primary key which is also a clustered index. No other indexes on this table.

There are many threads(via Java) working concurrently. Each thread will first do a "select for update" on a DIFFERENT primary key via lock hint (updlock, rowlock), then do some work, then update table for this row. It is guaranteed from Java side that each thread is operating on a different row in this table.

However there is a rare and elusive deadlock that I could not reproduce consistently. This deadlock happens once in a great while. I would imagine there should never be lock contention since each transaction should only lock different row. The deadlock graph shows "collision" on "key lock". "Hobt id" in the deadlock graph, as I queried, is the "index" in this table.

What did I miss? Thanks!

Code listing for each tran: note each tran operate on a different primary key.

begin tran;

select DIVISION_ID 
from COV_HOLES_PERIODDATE with (updlock, rowlock) 
where composite_primary_key = xxx;

//some work on other tables, not related to this table.

update COV_HOLES_PERIODDATE 
set non_primaryKey_field= xxx
where composite_primary_key = xxx;

commit;

EDIT 3: from the execution plan captured by SQL server profiler while my Java load testing program runs, if I read it correctly, due to implicit conversion, "skill=" was changed to "skill between a range" in "Seek Predicates" as this image shows. But I do not know how to interpret that it is still an "Clustered Index Seek", although "Seek Predicates" in this image shows a range for "Skill" column?

https://i.sstatic.net/9vThc.png

Code listing for the intended sql (but execution plan shows implicit conversion in "skill" column changes "skill" part) select DIVISION_ID from cpq_jfu_v4_speedTest.cpqjfuv4speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID = 1 and UNIT_ID = 2 and SKILL = 'M'
and PERIOD_START_DATE = '2014-02-09' ;

EDIT 2: first link is the picture for the deadlock. 2nd link is the table and its index https://i.sstatic.net/8j6V9.png https://i.sstatic.net/JaFkl.png

EDIT 1: listing for deadlock graph

  <deadlock-list>
<deadlock victim="process2fb02d498">
        <process-list>
            <process id="process2fb02d498" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (425c06b927a8)" waittime="4321" ownerId="181788" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.930" XDES="0x2f0d6a3a8" lockMode="U" schedulerid="4" kpid="8704" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-18T14:29:09.010" lastbatchcompleted="2014-02-18T14:29:09.010" lastattention="1900-01-01T00:00:00.010" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181788" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="148" sqlhandle="0x02000000f9bd1c3532128f75b67ae477b2447ba2a64528db0000000000000000000000000000000000000000">
update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 nvarchar(4000),@P1 smallint,@P2 smallint,@P3 nvarchar(4000),@P4 date)update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4                                            </inputbuf>
            </process>
            <process id="process2f0110cf8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (3d51ccbaf870)" waittime="4303" ownerId="181789" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.933" XDES="0x2f651e6c8" lockMode="U" schedulerid="3" kpid="904" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-18T14:29:09.030" lastbatchcompleted="2014-02-18T14:29:09.030" lastattention="1900-01-01T00:00:00.030" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181789" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="148" sqlhandle="0x02000000f9bd1c3532128f75b67ae477b2447ba2a64528db0000000000000000000000000000000000000000">
update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 nvarchar(4000),@P1 smallint,@P2 smallint,@P3 nvarchar(4000),@P4 date)update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4                                            </inputbuf>
            </process>
            <process id="process2f05d0188" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (f40d3be1c4a7)" waittime="4381" ownerId="181790" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.937" XDES="0x2f0290d08" lockMode="U" schedulerid="4" kpid="5248" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-18T14:29:08.950" lastbatchcompleted="2014-02-18T14:29:08.950" lastattention="1900-01-01T00:00:00.950" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181790" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="110" sqlhandle="0x02000000e59aa71c628d78d0574a5f60f697c8ffd8228e4b0000000000000000000000000000000000000000">
select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 smallint,@P1 smallint,@P2 nvarchar(4000),@P3 date)select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3                                    </inputbuf>
            </process>
            <process id="process2f2a9f868" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (8b00f1e21b7f)" waittime="4380" ownerId="181792" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.950" XDES="0x2f651f1d8" lockMode="U" schedulerid="2" kpid="7652" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-18T14:29:08.950" lastbatchcompleted="2014-02-18T14:29:08.950" lastattention="1900-01-01T00:00:00.950" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181792" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="110" sqlhandle="0x02000000e59aa71c628d78d0574a5f60f697c8ffd8228e4b0000000000000000000000000000000000000000">
select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 smallint,@P1 smallint,@P2 nvarchar(4000),@P3 date)select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3                                    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2c70ba180" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f2a9f868" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2fb02d498" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cc3d9980" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f05d0188" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f0110cf8" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cf4fa480" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f0110cf8" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f05d0188" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cf4f9e80" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2fb02d498" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f2a9f868" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
        </resource-list>
    </deadlock>
    <deadlock victim="process2f0110cf8">
        <process-list>
            <process id="process2fb02d498" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (425c06b927a8)" waittime="4321" ownerId="181788" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.930" XDES="0x2f0d6a3a8" lockMode="U" schedulerid="4" kpid="8704" status="suspended" spid="60" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-18T14:29:09.010" lastbatchcompleted="2014-02-18T14:29:09.010" lastattention="1900-01-01T00:00:00.010" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181788" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="148" sqlhandle="0x02000000f9bd1c3532128f75b67ae477b2447ba2a64528db0000000000000000000000000000000000000000">
update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 nvarchar(4000),@P1 smallint,@P2 smallint,@P3 nvarchar(4000),@P4 date)update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4                                            </inputbuf>
            </process>
            <process id="process2f0110cf8" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (3d51ccbaf870)" waittime="4303" ownerId="181789" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.933" XDES="0x2f651e6c8" lockMode="U" schedulerid="3" kpid="904" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-02-18T14:29:09.030" lastbatchcompleted="2014-02-18T14:29:09.030" lastattention="1900-01-01T00:00:00.030" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181789" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="148" sqlhandle="0x02000000f9bd1c3532128f75b67ae477b2447ba2a64528db0000000000000000000000000000000000000000">
update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 nvarchar(4000),@P1 smallint,@P2 smallint,@P3 nvarchar(4000),@P4 date)update cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE set LAST_UPDATED_DTZ=@P0 where DIVISION_ID=@P1 and UNIT_ID=@P2 and SKILL=@P3 and PERIOD_START_DATE=@P4                                            </inputbuf>
            </process>
            <process id="process2f05d0188" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (f40d3be1c4a7)" waittime="4381" ownerId="181790" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.937" XDES="0x2f0290d08" lockMode="U" schedulerid="4" kpid="5248" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-18T14:29:08.950" lastbatchcompleted="2014-02-18T14:29:08.950" lastattention="1900-01-01T00:00:00.950" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181790" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="110" sqlhandle="0x02000000e59aa71c628d78d0574a5f60f697c8ffd8228e4b0000000000000000000000000000000000000000">
select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 smallint,@P1 smallint,@P2 nvarchar(4000),@P3 date)select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3                                    </inputbuf>
            </process>
            <process id="process2f2a9f868" taskpriority="0" logused="0" waitresource="KEY: 6:72057594048413696 (8b00f1e21b7f)" waittime="4381" ownerId="181792" transactionname="implicit_transaction" lasttranstarted="2014-02-18T14:29:08.950" XDES="0x2f651f1d8" lockMode="U" schedulerid="2" kpid="7652" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-18T14:29:08.950" lastbatchcompleted="2014-02-18T14:29:08.950" lastattention="1900-01-01T00:00:00.950" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ACNU34794GD" hostpid="0" loginname="cpq_jfu_v380_speedTest_WEBAPP" isolationlevel="read committed (2)" xactid="181792" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="110" sqlhandle="0x02000000e59aa71c628d78d0574a5f60f697c8ffd8228e4b0000000000000000000000000000000000000000">
select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@P0 smallint,@P1 smallint,@P2 nvarchar(4000),@P3 date)select DIVISION_ID from cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE with (updlock, rowlock) where DIVISION_ID =@P0 and UNIT_ID =@P1 and SKILL =@P2 and PERIOD_START_DATE =@P3                                    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2c70ba180" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f2a9f868" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2fb02d498" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cc3d9980" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f05d0188" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f0110cf8" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cf4fa480" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2f0110cf8" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f05d0188" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594048413696" dbid="6" objectname="cpq_jfu_v380_speedTest.cpqjfuv380speedTest.COV_HOLES_PERIODDATE" indexname="1" id="lock2cf4f9e80" mode="U" associatedObjectId="72057594048413696">
                <owner-list>
                    <owner id="process2fb02d498" mode="U"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2f2a9f868" mode="U" requestType="wait"/>
                </waiter-list>
            </keylock>
        </resource-list>
    </deadlock>
</deadlock-list>
Fever answered 18/3, 2014 at 19:43 Comment(10)
Is this the whole transaction? Triggers? Indexed views (shouldn't matter with the given deadlock graph but who knows)? Can you post the graph as XML somewhere? And there are no NC indexes, right? Can you post the query plan for both statements? This is a nice challenge. – Report
"each tran operate on a different primary key" Even if not, and even if the lock hashes collide, this should be deadlock-free. – Report
Thanks. There is no NC indexes. No trigger nor indexed view. I do not have reputation to post an image (for query plan) yet. But the former(select ... for update) is a "clustered index seek". The latter (update where) is a "clustered index update". I have ruled out "lock hash collide" by querying " %%lockres%%" and "having count(%%lockres%% ) > 1" clause. – Fever
Why do some of the transactions have trancount="2". Maybe you misnested transactions so that they are extended? That would explain why multiple locks can be taken per tran. IOW an app bug. Add this IF @@TRANCOUNT <> 1 ROLLBACK to assert this at runtime. Rerun your load test with this assert. – Report
Are there implicit conversions? I know there are because you're passing in an nvarchar(4000) but PK columns cannot have such a big column. This can lead to range locks being taken. Change the update from @P2 to CONVERT(CORRECT_COLUMN_TYPE_HERE, @P2) to make sure. Examine the execution plan to make sure that the CI seek is using only equality predicates. Not ranges. We want a singleton seek. – Report
From the images just posted I can see conversion mismatches for the date and the string col. I suggest you try to fix those and report back. Also add the nested transaction assert. – Report
This is not really a programming question. I feel that it fits better on dba.stackexchange.com They also have a lot of smart people who can help with exactly this type of stuff. Related meta: meta.stackexchange.com/questions/172516/… meta.stackexchange.com/questions/201695/… – Konyn
Thanks. I think "conversion mismatch" is really onto something. It explains the range lock. I will use sql profiler to capture the execution plan that captured from my java load testing program. About asserting the "TRANACOUNT", it may not be as easy, as all the sqls are spitting out by java and hibernate. But I do need to inspect further. – Fever
Hibernate allows you to execute manual SQL statements as well (separately). That should work.; If you are really getting a range scan on the string column, this would mean that the query completely disregards the filter on the date column from a locking perspective. – Report
I added "EDIT 3", which uploads the execution plan. One column is changed to a range in "Seek Predicates" due to implicit conversion. Does this indicate a range scan? However it still shows "Clustered Index Seek"? – Fever
R
4

The investigation documented in the comments lead to the following answer:

You're passing in parameters which do not match the corresponding columns in type. This leads to implicit conversions and a range seek on the clustered index. "Seek" mean that a subset of the index will be scanned. It does not necessarily imply a singleton seek.

The range seek on the 3rd column of the index means that the query completely disregards the filter on the 4th column from a locking perspective. This can lead to multiple rows being locked. Not necessarily range locks because these are not taken under READ COMMITTED. Had you also set the locking hint HOLDLOCK (or SERIALIZABLE) range locks would have been taken.

Other things that were tried:

Is this the whole transaction? Triggers? Indexed views (shouldn't matter with the given deadlock graph but who knows)? Can you post the graph as XML somewhere? And there are no NC indexes, right? Can you post the query plan for both statements?

.

Why do some of the transactions have trancount="2". Maybe you misnested transactions so that they are extended? That would explain why multiple locks can be taken per tran. IOW an app bug. Add this IF @@TRANCOUNT <> 1 ROLLBACK to assert this at runtime. Rerun your load test with this assert.

.

Are there implicit conversions? I know there are because you're passing in a nvarchar(4000) but PK columns cannot have such a big column. This can lead to range locks being taken. Change the update from @P2 to CONVERT(CORRECT_COLUMN_TYPE_HERE, @P2) to make sure. Examine the execution plan to make sure that the CI seek is using only equality predicates. Not ranges.

I'm adding this to the answer so that future visitors of this question can learn how to investigate an issue like this one.

Report answered 18/3, 2014 at 21:21 Comment(1)
Thanks a million for your detailed explanation. It really explains this mystery. Also thanks for educating "index seek" does not necessarily imply singleton. Thanks so much! – Fever
A
0

ROWLOCK never gives you an absolute guarantee of locking only this particular row(s) that you operate on. This is even stated on MSDN:

Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows.

As you cannot 100% rely on it, a rare deadlock may happen. In your case, you can see that a deadlock is on a key which would point to this situation.

Aristides answered 18/3, 2014 at 19:54 Comment(3)
There is only one index - the CI. Exactly one rowlock per tran should be taken there. I'm not aware of any case where a page lock or even higher would be taken by the given tran. The OP should examine the deadlock graph to make sure this is not the case. – Report
waitresource="KEY: 6:72057594048413696 (8b00f1e21b7f)" It looks like it's locking a key range in this case. The locks point to the same hobt_id. – Aristides
I just added the deadlock graph image & table design as "EDIT 2". Being a Java developer and having limited understanding on DB side, I am puzzled by the key lock. When I do SELECT hobt_id, object_name(p.[object_id]), index_id FROM sys.partitions p WHERE hobt_id = 72057594048413696, it returns index 1 on this table – Fever
F
0

In case this can help anyone encountering similar issue.

The root cause is that, by default MSSQL JDBC driver sends String parameter to database server as Unicode. There is a parameter sendStringParameterAsUnicode [true|false] that can be passed as connection property. But by default this property is set to true (http://technet.microsoft.com/en-us/library/ms378988.aspx).

If sendStringParameterAsUnicode parameter is NOT passed in connection string(which by default is set to true), jdbc driver sends parameter which are defined as char as nchar, varchar as nvarchar and longvarchar as ntext.

For both correctness and optimal performance with CHAR, VARCHAR, and LONGVARCHAR JDBC data types, an application should set the sendStringParametersAsUnicode property to "false"

Fever answered 24/3, 2014 at 20:25 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.