logo标签列表

多个并发事务情况下，针对select for update; then update的死锁情况罕见且难以捉摸。

sqlsql-serverdeadlockdatabase-deadlocks
7

数据库：MSSQL Server 2012；

隔离级别：READ_COMMITTED_SNAPSHOT。

现在我有一张名为“COV_HOLES_PERIODDATE”的表。它具有一个复合主键，同时也是聚集索引。此表上不存在其他索引。

有许多线程（通过Java）并发工作。每个线程将首先使用锁提示（updlock、rowlock）对不同的主键执行“select for update”，然后进行一些工作，然后更新该行所在的表。从Java方面保证每个线程操作的是此表中的不同行。

但是很少有一个难以捉摸的死锁，我无法持续地重现它。这种死锁偶尔发生。我认为永远不应该出现锁争用，因为每个事务只能锁定不同的行。死锁图显示在“键锁”上存在“冲突”。查询死锁图中的“Hobt id”是此表中的“索引”。

我错过了什么？谢谢！

每个事务的代码列表：请注意，每个事务都在不同的主键上操作。

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;
编辑3: 从我捕获的SQL Server Profiler执行计划中，如果我正确地阅读了它，由于隐式转换，“skill =”在“搜索谓词”中被更改为“技能范围”，如此图像所示。但是我不知道如何解释它仍然是一个“聚集索引搜索”，尽管这个图像中的“搜索谓词”显示了“Skill”列的范围？ http://i.stack.imgur.com/9vThc.png

用于预期sql的代码清单(但执行计划显示“技能”列中的隐式转换更改了“技能”部分) 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' ;

编辑2: 第一个链接是死锁的图片。第二个链接是表格及其索引 http://i.stack.imgur.com/8j6V9.png http://i.stack.imgur.com/JaFkl.png 编辑1: 死锁图的列表
  <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>
1
这是整个事务吗？触发器？索引视图（根据给定的死锁图似乎无关紧要，但谁知道呢）？你能在某个地方以XML格式发布图形吗？而且没有非聚集索引，对吧？你能为两个语句发布查询计划吗？这是一个不错的挑战。 - usr
每个事务操作一个不同的主键。即使锁哈希碰撞，也应该没有死锁。 - usr
1
为什么有些事务的 trancount="2"？也许您嵌套了事务，导致它们被扩展了？这可以解释为什么每个 tran 可以获取多个锁。换句话说，这是一个应用程序错误。在运行时添加此 IF @@TRANCOUNT <> 1 ROLLBACK 来断言此问题。使用此断言重新运行负载测试。 - usr
3
是否存在隐式转换？我知道存在这种情况，因为您正在传递一个 nvarchar(4000)，但是 PK 列不能有这么大的列。这可能会导致区间锁定被获取。将更新从 @P2 更改为 CONVERT(CORRECT_COLUMN_TYPE_HERE, @P2) 以确保正确的列类型。检查执行计划以确保 CI 搜索仅使用相等谓词而不是范围。我们需要一个单例搜索。 - usr
1
谢谢。我认为“转换不匹配”确实有些道理。它解释了范围锁定。我将使用SQL Profiler来捕获从我的Java负载测试程序中捕获的执行计划。关于断言“TRANACOUNT”，这可能并不容易，因为所有的SQL都是由Java和Hibernate生成的。但我确实需要进一步检查。 - riceball
显示剩余5条评论
3个回答
3
调查表明，评论中提到的问题的答案如下：
您传递的参数与相应类型中的列不匹配。这会导致隐式转换和聚集索引上的范围查找。 "查找"意味着将扫描索引的子集。这并不一定意味着单例查找。
索引的第3列的范围查找意味着查询从锁定角度完全忽略了第4列上的过滤器。这可能导致多行被锁定。并不一定是范围锁，因为这些锁不在READ COMMITTED下进行。如果您还设置了锁提示HOLDLOCK（或SERIALIZABLE），则会采取范围锁。
其他尝试的事情：

这是整个事务吗？触发器？索引视图（根据给定的死锁图形不应该有关系，但谁知道）？您可以在XML中的某个位置发布图形吗？没有NC索引，对吧？您能同时发布两个语句的查询计划吗？

.

为什么一些事务的trancount =“2”？也许您错位了事务，以便它们被扩展？这就解释了为什么每个tran可以获取多个锁。IOW是应用程序错误。在运行时添加IF @@ TRANCOUNT <> 1 ROLLBACK来断言这一点。使用此断言重新运行负载测试。

.

是否存在隐式转换？我知道有，因为您传递了nvarchar（4000），但PK列不能具有如此大的列。这可能导致采取范围锁。将更新从@P2更改为CONVERT（CORRECT_COLUMN_TYPE_HERE，@P2）以确保。检查执行计划以确保CI查找仅使用相等谓词。不是范围。

我将这个答案添加到问题中，以便未来的访问者可以学习如何调查此类问题。
1
非常感谢您详细的解释，真的解开了这个谜团。还要感谢您教育我们，“索引搜索”并不一定意味着单例。非常感谢！ - riceball
0

如果有人遇到类似的问题，这可能会有所帮助。

根本原因是，默认情况下MSSQL JDBC驱动程序将String参数作为Unicode发送到数据库服务器。可以传递一个名为sendStringParameterAsUnicode [true|false]的参数作为连接属性。但默认情况下，此属性设置为true（http://technet.microsoft.com/en-us/library/ms378988.aspx）。

如果在连接字符串中未传递sendStringParameterAsUnicode参数（默认设置为true），jdbc驱动程序将char定义的参数发送为nchar，varchar定义的参数发送为nvarchar，longvarchar定义的参数发送为ntext。

为了确保CHAR、VARCHAR和LONGVARCHAR JDBC数据类型的正确性和最佳性能，应用程序应将sendStringParametersAsUnicode属性设置为“false”。

0

ROWLOCK 永远不能完全保证只锁定您操作的特定行。这甚至在 MSDN 上声明：

获取行级锁的锁提示 ROWLOCK、UPDLOCK 和 XLOCK 可能会在索引键上放置锁，而不是实际的数据行。

由于您无法百分之百地依赖它，因此可能会发生罕见的死锁。在您的情况下，您可以看到死锁发生在指向此情况的关键字上。

只有一个索引 - CI。每个事务应该只有一个行锁。我不知道在给定的事务中是否会采取页面锁甚至更高级别的锁。OP应该检查死锁图以确保这不是问题所在。 - usr
waitresource="KEY: 6:72057594048413696 (8b00f1e21b7f)" 看起来它在这种情况下锁定了一个关键范围。锁定指向相同的 hobt_id。 - Szymon
我刚刚把死锁图像和表格设计作为"EDIT 2"添加了进来。作为一名Java开发者，我对数据库方面的理解有限，我对关键锁感到困惑。当我执行SELECT hobt_id、object_name(p.[object_id])、index_id FROM sys.partitions p WHERE hobt_id = 72057594048413696时，它返回此表上的索引1。 - riceball
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接