我有一个死锁，它是由两个不同的进程id在同一个聚集索引上使用独占锁发生的。数据库处于rcsi模式。我有两个问题。
1）除了两个update语句重叠行之外，这可能是散列冲突造成的吗？
2）在下面的跟踪XML文件中，查询上有参数。我应该使用sql profiler跟踪哪些事件来获取参数值？
感谢你的帮助。谢谢
跟踪XML文件
<deadlock-list>
<deadlock victim="process8ad948">
<process-list>
<process id="process8ad948" taskpriority="0" logused="164520" waitresource="KEY: 7:72057601661599744 (01c1926aca97)" waittime="2833" ownerId="7491036" transactionname="user_transaction" lasttranstarted="2012-10-08T13:16:03.067" XDES="0x6b561b950" lockMode="U" schedulerid="4" kpid="2476" status="suspended" spid="94" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-08T13:16:57.753" lastbatchcompleted="2012-10-08T13:16:57.750" clientapp="PeopleSoft" hostname="STGAAMY-PPSAPP" hostpid="31772" loginname="HRTRNADM" isolationlevel="read committed (2)" xactid="7491036" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="440" sqlhandle="0x020000007b8741293b8e4d6542633962bc48489d3946a5d1">
UPDATE PS_GP_NEW_RTO_WRK SET ORIG_CAL_RUN_ID = (SELECT DISTINCT C.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL_RUN_ID=@P3 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO_WRK.EMPL_RCD AND C.CAL_ID = PS_GP_NEW_RTO_WRK.CAL_ID AND C.GP_PAYGROUP = PS_GP_NEW_RTO_WRK.GP_PAYGROUP AND B.CAL_ID= C.CAL_ID AND B.GP_PAYGROUP= C.GP_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL_RCD=C.EMPL_RCD AND B.ORIG_CAL_RUN_ID=C.ORIG_CAL_RUN_ID AND C.SEL_ACTION IN (N'R',N'Z') AND B.SEL_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL_RUN_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS_GP_GRP_LIST_RUN WHERE RUN_CNTL_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P10 AND @P11 AND C.CAL_RUN_ID=@P12 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 nvarchar(508),@P2 nvarchar(508),@P3 nvarchar(508),@P4 nvarchar(508),@P5 nvarchar(508),@P6 nvarchar(508),@P7 nvarchar(508),@P8 nvarchar(508),@P9 nvarchar(508),@P10 nvarchar(508),@P11 nvarchar(508),@P12 nvarchar(508))UPDATE PS_GP_NEW_RTO_WRK SET ORIG_CAL_RUN_ID = (SELECT DISTINCT C.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL_RUN_ID=@P3 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO_WRK.EMPL_RCD AND C.CAL_ID = PS_GP_NEW_RTO_WRK.CAL_ID AND C.GP_PAYGROUP = PS_GP_NEW_RTO_WRK.GP_PAYGROUP AND B.CAL_ID= C.CAL_ID AND B.GP_PAYGROUP= C.GP_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL_RCD=C.EMPL_RCD AND B.ORIG_CAL_RUN_ID=C.ORIG_CAL_RUN_ID AND C.SEL_ACTION IN (N'R',N'Z') AND B.SEL_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL_RUN_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS_GP_GRP_LIST_RUN WHERE RUN_CNTL_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG_CAL_RUN_ID FRO </inputbuf>
</process>
<process id="process5ff948" taskpriority="0" logused="2793096" waitresource="KEY: 7:72057601661599744 (9e1bb6c02ace)" waittime="1659" ownerId="7509883" transactionname="user_transaction" lasttranstarted="2012-10-08T13:16:50.867" XDES="0x6b6beb950" lockMode="U" schedulerid="1" kpid="2472" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-08T13:16:59.400" lastbatchcompleted="2012-10-08T13:16:59.400" clientapp="PeopleSoft" hostname="STGAAMY-PPSAPP" hostpid="32912" loginname="HRTRNADM" isolationlevel="read committed (2)" xactid="7509883" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="440" sqlhandle="0x020000007b8741293b8e4d6542633962bc48489d3946a5d1">
UPDATE PS_GP_NEW_RTO_WRK SET ORIG_CAL_RUN_ID = (SELECT DISTINCT C.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL_RUN_ID=@P3 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO_WRK.EMPL_RCD AND C.CAL_ID = PS_GP_NEW_RTO_WRK.CAL_ID AND C.GP_PAYGROUP = PS_GP_NEW_RTO_WRK.GP_PAYGROUP AND B.CAL_ID= C.CAL_ID AND B.GP_PAYGROUP= C.GP_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL_RCD=C.EMPL_RCD AND B.ORIG_CAL_RUN_ID=C.ORIG_CAL_RUN_ID AND C.SEL_ACTION IN (N'R',N'Z') AND B.SEL_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL_RUN_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS_GP_GRP_LIST_RUN WHERE RUN_CNTL_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P10 AND @P11 AND C.CAL_RUN_ID=@P12 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P1 nvarchar(508),@P2 nvarchar(508),@P3 nvarchar(508),@P4 nvarchar(508),@P5 nvarchar(508),@P6 nvarchar(508),@P7 nvarchar(508),@P8 nvarchar(508),@P9 nvarchar(508),@P10 nvarchar(508),@P11 nvarchar(508),@P12 nvarchar(508))UPDATE PS_GP_NEW_RTO_WRK SET ORIG_CAL_RUN_ID = (SELECT DISTINCT C.ORIG_CAL_RUN_ID FROM PS_GP_PYE_PRC_STAT B ,PS_GP_OLD_RTO_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL_RUN_ID=@P3 AND C.EMPLID = PS_GP_NEW_RTO_WRK.EMPLID AND C.CAL_RUN_ID = PS_GP_NEW_RTO_WRK.CAL_RUN_ID AND C.EMPL_RCD = PS_GP_NEW_RTO_WRK.EMPL_RCD AND C.CAL_ID = PS_GP_NEW_RTO_WRK.CAL_ID AND C.GP_PAYGROUP = PS_GP_NEW_RTO_WRK.GP_PAYGROUP AND B.CAL_ID= C.CAL_ID AND B.GP_PAYGROUP= C.GP_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL_RCD=C.EMPL_RCD AND B.ORIG_CAL_RUN_ID=C.ORIG_CAL_RUN_ID AND C.SEL_ACTION IN (N'R',N'Z') AND B.SEL_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL_RUN_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS_GP_GRP_LIST_RUN WHERE RUN_CNTL_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG_CAL_RUN_ID FRO </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057601661599744" dbid="7" objectname="HRTRN91.dbo.PS_GP_NEW_RTO_WRK" indexname="PS_GP_NEW_RTO_WRK" id="lock4991e6c00" mode="X" associatedObjectId="72057601661599744">
<owner-list>
<owner id="process5ff948" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process8ad948" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057601661599744" dbid="7" objectname="HRTRN91.dbo.PS_GP_NEW_RTO_WRK" indexname="PS_GP_NEW_RTO_WRK" id="lockdf24e800" mode="X" associatedObjectId="72057601661599744">
<owner-list>
<owner id="process8ad948" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5ff948" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
表格结构
CREATE TABLE [dbo].[PS_GP_NEW_RTO_WRK](
[EMPLID] [nvarchar](11) NOT NULL,
[CAL_RUN_ID] [nvarchar](18) NOT NULL,
[EMPL_RCD] [smallint] NOT NULL,
[GP_PAYGROUP] [nvarchar](10) NOT NULL,
[CAL_ID] [nvarchar](18) NOT NULL,
[ORIG_CAL_RUN_ID] [nvarchar](18) NOT NULL,
[RSLT_VER_NUM] [smallint] NOT NULL,
[RSLT_REV_NUM] [smallint] NOT NULL,
[PRC_ORD_TS] [dbo].[PSDATETIME] NULL,
[SEL_STAT] [nvarchar](1) NOT NULL,
[SEL_RSN] [nvarchar](2) NOT NULL,
[RTO_PRC_ID] [nvarchar](10) NOT NULL,
[PYMT_DT] [dbo].[PSDATE] NULL,
[PRC_BGN_DT] [dbo].[PSDATE] NULL,
[PRC_END_DT] [dbo].[PSDATE] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PS_GP_NEW_RTO_WRK] SET (LOCK_ESCALATION = ENABLE)
GO
索引结构
CREATE UNIQUE CLUSTERED INDEX [PS_GP_NEW_RTO_WRK] ON [dbo].[PS_GP_NEW_RTO_WRK]
(
[EMPLID] ASC,
[CAL_RUN_ID] ASC,
[EMPL_RCD] ASC,
[GP_PAYGROUP] ASC,
[CAL_ID] ASC,
[ORIG_CAL_RUN_ID] ASC
)
WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
最佳答案
1）在开发/测试服务器上还原备份并运行此查询：
SELECT *
FROM (
SELECT [EMPLID],[CAL_RUN_ID],[EMPL_RCD],[GP_PAYGROUP],[CAL_ID],[ORIG_CAL_RUN_ID], %%lockres%% AS KeyLockID
FROM [dbo].[PS_GP_NEW_RTO_WRK]
) x
WHERE x.KeyLockID IN ('(01c1926aca97)','(9e1bb6c02ace)')
(01c1926aca97)和(9e1bb6c02ace)值是“锁id”（请参阅/deadlock list/deadlock/process list/process/@waitresource values:KEY: 7:72057601661599744 (01c1926aca97)，KEY: 7:72057601661599744 (9e1bb6c02ace)）。如果你得到3行或更多，那么答案是肯定的。
2）此外，您可以检查差异计划：
SELECT qp.*, txt.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) txt
WHERE txt.text LIKE N'UPDATE PS\_GP\_NEW\_RTO\_WRK SET ORIG\_CAL\_RUN\_ID = (SELECT DISTINCT C.ORIG\_CAL\_RUN\_ID FROM PS\_GP\_PYE\_PRC\_STAT B ,PS\_GP\_OLD\_RTO\_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL\_RUN\_ID=@P3 AND C.EMPLID = PS\_GP\_NEW\_RTO\_WRK.EMPLID AND C.CAL\_RUN\_ID = PS\_GP\_NEW\_RTO\_WRK.CAL\_RUN\_ID AND C.EMPL\_RCD = PS\_GP\_NEW\_RTO\_WRK.EMPL\_RCD AND C.CAL\_ID = PS\_GP\_NEW\_RTO\_WRK.CAL\_ID AND C.GP\_PAYGROUP = PS\_GP\_NEW\_RTO\_WRK.GP\_PAYGROUP AND B.CAL\_ID= C.CAL\_ID AND B.GP\_PAYGROUP= C.GP\_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL\_RCD=C.EMPL\_RCD AND B.ORIG\_CAL\_RUN\_ID=C.ORIG\_CAL\_RUN\_ID AND C.SEL\_ACTION IN (N'R',N'Z') AND B.SEL\_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL\_RUN\_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS\_GP\_GRP\_LIST\_RUN WHERE RUN\_CNTL\_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG\_CAL\_RUN\_ID FROM PS\_GP\_PYE\_PRC\_STAT B ,PS\_GP\_OLD\_RTO\_WRK C WHERE C.EMPLID BETWEEN @P10 AND @P11 AND C.CAL\_RUN\_ID=@P12 AND C.EMPLID = PS\_GP\_NEW\_RTO\_WRK.EMPLID AND C.CAL\_RUN\_ID = PS\_GP\_NEW\_RTO\_WRK.CAL\_RUN\_ID AND C.EMPL\_RCD = PS\_GP\_NEW\_RTO%' ESCAPE N'\'
ORDER BY txt.text
SELECT qp.*, txt.*
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) txt
WHERE txt.text LIKE N'(@P1 nvarchar(508),@P2 nvarchar(508),@P3 nvarchar(508),@P4 nvarchar(508),@P5 nvarchar(508),@P6 nvarchar(508),@P7 nvarchar(508),@P8 nvarchar(508),@P9 nvarchar(508),@P10 nvarchar(508),@P11 nvarchar(508),@P12 nvarchar(508))UPDATE PS\_GP\_NEW\_RTO\_WRK SET ORIG\_CAL\_RUN\_ID = (SELECT DISTINCT C.ORIG\_CAL\_RUN\_ID FROM PS\_GP\_PYE\_PRC\_STAT B ,PS\_GP\_OLD\_RTO\_WRK C WHERE C.EMPLID BETWEEN @P1 AND @P2 AND C.CAL\_RUN\_ID=@P3 AND C.EMPLID = PS\_GP\_NEW\_RTO\_WRK.EMPLID AND C.CAL\_RUN\_ID = PS\_GP\_NEW\_RTO\_WRK.CAL\_RUN\_ID AND C.EMPL\_RCD = PS\_GP\_NEW\_RTO\_WRK.EMPL\_RCD AND C.CAL\_ID = PS\_GP\_NEW\_RTO\_WRK.CAL\_ID AND C.GP\_PAYGROUP = PS\_GP\_NEW\_RTO\_WRK.GP\_PAYGROUP AND B.CAL\_ID= C.CAL\_ID AND B.GP\_PAYGROUP= C.GP\_PAYGROUP AND B.EMPLID=C.EMPLID AND B.EMPL\_RCD=C.EMPL\_RCD AND B.ORIG\_CAL\_RUN\_ID=C.ORIG\_CAL\_RUN\_ID AND C.SEL\_ACTION IN (N'R',N'Z') AND B.SEL\_ACTION=N'A') WHERE EMPLID BETWEEN @P4 AND @P5 AND CAL\_RUN\_ID=@P6 AND (N'N'=@P7 OR EMPLID IN (SELECT EMPLID FROM PS\_GP\_GRP\_LIST\_RUN WHERE RUN\_CNTL\_ID=@P8 AND OPRID=@P9) ) AND EXISTS (SELECT B.ORIG\_CAL\_RUN\_ID FRO%' ESCAPE N'\'
ORDER BY txt.text
如果每个update或（@…）update语句（例如串行计划和并行计划）都有不同的计划，那么这个事实排除了初始假设（散列冲突）。
扩展注释：
t=spid94开始执行update语句（串行计划）
T+1=SPID95开始执行UPDATE语句（并行计划）
T+2=SPID94对行/键4执行X锁
T+3=spid95-thread1尝试对行/键4使用U形锁（已被spid94锁定）。
此外，spid95-thread2对行/键9使用x锁。
T+4=SPID94尝试对行/键9执行U锁（已被SPID95锁定）。
结论：spid94在行/键4上有一个x锁，请求在行/键9上有一个u锁。spid95在row/key 9上有一个x锁，在row/key 4上请求一个u锁。所以，我们陷入了僵局。
关于sql - 由于哈希冲突导致死锁？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12879282/