Deadlock
-
Hi,
We've just had an insert/update deadlock on one of our databases. The deadlock graph shows that it's the result of a key lock on the same index. Both processes appear to have an 'X' lock on the same key; but one's blocking an 'S' lock request, and the other's blocking a 'U' mode request.
How can 2 processes both have an 'X' lock on the same key at the same time? Or am I misinterpretting this?
https://www.sqlservercentral.com/Forums/Uploads/image-unavailable.png
The text is:
<deadlock-list><deadlock victim="process13c8AA88">
<process-list>
<process id="process13c8AA88" taskpriority="0" logused="308" waitresource="KEY: 5:72057650155880448 (a2d88dda5e35)" waittime="4590" ownerId="666054973" transactionname="user_transaction" lasttranstarted="2012-10-10T12:24:48.773" XDES="0x27f659d950" lockMode="S" schedulerid="14" kpid="13776" status="suspended" spid="174" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-10T12:24:48.780" lastbatchcompleted="2012-10-10T12:24:48.780" clientapp=".Net SqlClient Data Provider" hostname="CLIENTSERVER" hostpid="5916" loginname="DOMAIN\ACCOUNT" isolationlevel="read committed (2)" xactid="666054973" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x020000003b8b343427af440432ae51b11a198d336d464254">
INSERT "dbo"."T_OW_CONDON" ("ConditionName","Value","ErrorMessage","APPLICATION_System_Inserted","APPLICATION_System_Updated","APPLICATION_System_ChangedBy","APPLICATION_SYSTEM_LASTMODIFIED")
SELECT LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60),S."Value",LEFT(CONVERT(NVARCHAR(MAX),S."ErrorMessage"),250),'20121010 12:24:48','20121010 12:24:48','DOMAIN\ACCOUNT','20121010 12:24:48'
FROM (
SELECT
CASE COUNT(*)
WHEN 0 THEN 0
ELSE 1
END AS Value,
'Do Exception Messages Exist' AS ConditionName,
'No Exception Messages Exist' AS ErrorMessage
FROM "dbo"."T_EXCEPTION_MSG_CHECK" (NOLOCK)
) S
LEFT JOIN "dbo"."T_WORKFLOW_CONDITION" T ON
CONVERT(NVARCHAR(MAX),T."ConditionName")=LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60)
WHERE
T."ConditionName" IS NULL </frame>
</executionStack>
<inputbuf>
INSERT "dbo"."T_OW_CONDON" ("ConditionName","Value","ErrorMessage","APPLICATION_System_Inserted","APPLICATION_System_Updated","APPLICATION_System_ChangedBy","APPLICATION_SYSTEM_LASTMODIFIED")
SELECT LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60),S."Value",LEFT(CONVERT(NVARCHAR(MAX),S."ErrorMessage"),250),'20121010 12:24:48','20121010 12:24:48','DOMAIN\ACCOUNT','20121010 12:24:48'
FROM (
SELECT
CASE COUNT(*)
WHEN 0 THEN 0
ELSE 1
END AS Value,
'Do Exception Messages Exist' AS ConditionName,
'No Exception Messages Exist' AS ErrorMessage
FROM "dbo"."T_EXCEPTION_MSG_CHECK" (NOLOCK)
) S
LEFT JOIN "dbo"."T_OW_CONON" T ON
CONVERT(NVARCHAR(MAX),T."ConditionName")=LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60)
WHERE
T."ConditionName" IS NULL
</inputbuf>
</process>
<process id="process13c76748" taskpriority="0" logused="316" waitresource="KEY: 5:72057650155880448 (b672de8cdfa0)" waittime="4593" ownerId="666054959" transactionname="user_transaction" lasttranstarted="2012-10-10T12:24:48.770" XDES="0x1462893950" lockMode="U" schedulerid="12" kpid="9856" status="suspended" spid="107" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-10T12:24:48.773" lastbatchcompleted="2012-10-10T12:24:48.770" clientapp=".Net SqlClient Data Provider" hostname="CLIENTSERVER" hostpid="5520" loginname="DOMAIN\ACCOUNT" isolationlevel="read committed (2)" xactid="666054959" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x020000004e1e56241b39dc7eb5de3a2b45aef028e55637a0">
UPDATE T SET
T."Value"=S."Value",T."ErrorMessage"=LEFT(CONVERT(NVARCHAR(MAX),S."ErrorMsg"),250),T."APPLICATION_System_Inserted"='20121010 12:24:48',T."APPLICATION_System_Updated"='20121010 12:24:48',T."APPLICATION_System_ChangedBy"='DOMAIN\ACCOUNT',T."APPLICATION_SYSTEM_LASTMODIFIED"='20121010 12:24:48'
FROM
"dbo"."T_OW_CONDON" T
INNER JOIN (
SELECT 'AA BO Money Asset Integrity Okay' As ConditionName,
'AA BO Money Asset Integrity Problem' As ErrorMsg,
"Is Record Valid?" as Value
FROM "APPLICATION"."DI_ITGAABOEQA_TPROCESSTO_RSLT" (NOLOCK)
) S ON
CONVERT(NVARCHAR(MAX),T."ConditionName")=LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60)
</frame>
</executionStack>
<inputbuf>
UPDATE T SET
T."Value"=S."Value",T."ErrorMessage"=LEFT(CONVERT(NVARCHAR(MAX),S."ErrorMsg"),250),T."APPLICATION_System_Inserted"='20121010 12:24:48',T."APPLICATION_System_Updated"='20121010 12:24:48',T."APPLICATION_System_ChangedBy"='DOMAIN\ACCOUNT',T."APPLICATION_SYSTEM_LASTMODIFIED"='20121010 12:24:48'
FROM
"dbo"."T_OW_CONDON" T
INNER JOIN (
SELECT 'AA BO Money Asset Integrity Okay' As ConditionName,
'AA BO Money Asset Integrity Problem' As ErrorMsg,
"Is Record Valid?" as Value
FROM "APPLICATION"."DI_ITGAABOEQA_TPROCESSTO_RSLT" (NOLOCK)
) S ON
CONVERT(NVARCHAR(MAX),T."ConditionName")=LEFT(CONVERT(NVARCHAR(MAX),S."ConditionName"),60)
</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057650155880448" dbid="5" objectname="Company_APPLICATION.dbo.T_WORKFLOW_CONDITION" indexname="PK__T_WORKFLOW_CONDI__6F1576F7" id="lock1422810b80" mode="X" associatedObjectId="72057650155880448">
<owner-list>
<owner id="process13c76748" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process13c8AA88" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057650155880448" dbid="5" objectname="Company_APPLICATION.dbo.T_WORKFLOW_CONDITION" indexname="PK__T_WORKFLOW_CONDI__6F1576F7" id="lock265fd65700" mode="X" associatedObjectId="72057650155880448">
<owner-list>
<owner id="process13c8AA88" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process13c76748" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Thanks,
Andrew
-
They're not on the same key. Two different keys in the same table. What tells you that is the id in the keylock. It's the hash of the key values and you'll notice it's different.
<keylock hobtid="72057650155880448" dbid="5" objectname="Company_APPLICATION.dbo.T_WORKFLOW_CONDITION" indexname="PK__T_WORKFLOW_CONDI__6F1576F7" id="lock1422810b80" mode="X" associatedObjectId="72057650155880448"><keylock hobtid="72057650155880448" dbid="5" objectname="Company_APPLICATION.dbo.T_WORKFLOW_CONDITION" indexname="PK__T_WORKFLOW_CONDI__6F1576F7" id="lock265fd65700" mode="X" associatedObjectId="72057650155880448">
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply