Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am experiencing a deadlock in SQL Server, but I do not understand why this is a deadlock. It looks like the two resources are actually the same. Meaning that they each have the lock and are asking for the same lock again and this is flagged by SQL as a deadlock.

The table:

CREATE TABLE [dbo].[Device](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DeviceType] [nvarchar](255) NOT NULL,
[ControllerIdentifier] [nvarchar](255) NULL,
[Created] [datetimeoffset](7) NULL,
[LastModified] [datetimeoffset](7) NULL,
[DeviceId] [nvarchar](255) NULL,
[TypeDescription] [nvarchar](255) NULL,
[IpAddress] [nvarchar](255) NULL,
[Number] [bigint] NULL,
[SerialNumber] [bigint] NULL,
[SoftwareVersionApplication] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[StateUpdated] [datetimeoffset](7) NULL,
[MainType] [nvarchar](255) NULL,
[SubType] [nvarchar](255) NULL,
[Firmware] [nvarchar](255) NULL,
[ConfigurationUpdated] [datetimeoffset](7) NULL,
[ConsumptionType] [nvarchar](255) NULL,
[VendorId] [nvarchar](255) NULL,
[Owner] [nvarchar](255) NULL,
[SupportsAutoCollection] [bit] NULL,
[RepeaterStatus] [int] NULL,
[SoftwareVersion] [int] NULL,
[EnergyConsumption] [int] NULL,
[NetworkId] [int] NULL,
[AmbientTemperature] [int] NULL,
[InstallationText] [nvarchar](255) NULL,
[RepeaterListCapacity] [int] NULL,
[RepeaterListRevision] [int] NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The deadlock (Note, that the updates are not updating the same row):

<deadlock-list>
  <deadlock victim="process403ddc8">
    <process-list>
      <process id="process403ddc8" taskpriority="0" logused="4744" waitresource="KEY: 7:72057594038845440 (7f25ce0760b0)" waittime="1944" ownerId="66977" transactionname="user_transaction" lasttranstarted="2013-03-19T15:42:22.500" XDES="0x81603950" lockMode="X" schedulerid="4" kpid="2668" status="suspended" spid="54" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-19T15:42:23.100" lastbatchcompleted="2013-03-19T15:42:23.100" clientapp=".Net SqlClient Data Provider"  hostpid="2880" isolationlevel="read committed (2)" xactid="66977" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
         <frame procname="adhoc" line="1" stmtstart="68" sqlhandle="0x0200000028bdf306b502658c1a2f98fca4a8a97bff26be80">
UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1     </frame>
        <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
   <inputbuf>
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1    </inputbuf>
 </process>
 <process id="process3daf948" taskpriority="0" logused="7268" waitresource="KEY: 7:72057594038845440 (94675634c2ef)" waittime="1933" ownerId="66980" transactionname="user_transaction" lasttranstarted="2013-03-19T15:42:22.507" XDES="0x84138e80" lockMode="X" schedulerid="1" kpid="2848" status="suspended" spid="56" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-19T15:42:23.110" lastbatchcompleted="2013-03-19T15:42:23.110" clientapp=".Net SqlClient Data Provider"  hostpid="2880" isolationlevel="read committed (2)" xactid="66980" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
 <executionStack>
  <frame procname="adhoc" line="1" stmtstart="68" sqlhandle="0x0200000028bdf306b502658c1a2f98fca4a8a97bff26be80">
 UPDATE [Device] SET LastModified = @p0 WHERE Id = @p1     </frame>
 <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
</executionStack>
<inputbuf>
(@p0 datetimeoffset(7),@p1 bigint)UPDATE [Device] SET LastModified = @p0 WHERE Id =   @p1    </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594038845440" dbid="7" objectname="UtiliDriver.dbo.Device" indexname="PK__Device__3214EC0703317E3D" id="lock823f4a00" mode="X" associatedObjectId="72057594038845440">
<owner-list>
 <owner id="process3daf948" mode="X"/>
</owner-list>
<waiter-list>
 <waiter id="process403ddc8" mode="X" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594038845440" dbid="7" objectname="UtiliDriver.dbo.Device" indexname="PK__Device__3214EC0703317E3D" id="lock8015d980" mode="X" associatedObjectId="72057594038845440">
 <owner-list>
 <owner id="process403ddc8" mode="X"/>
 </owner-list>
 <waiter-list>
  <waiter id="process3daf948" mode="X" requestType="wait"/>
  </waiter-list>
  </keylock>
 </resource-list>
 </deadlock>
</deadlock-list>
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

The code below will give you the two rows which were involved in the deadlock, which is snipped from The Curious Case of the Dubious Deadlock and the Not So Logical Lock

SELECT *, %%lockres%%
FROM UtiliDriver.dbo.Device WITH (NOLOCK)
WHERE %%lockres%% IN('(7f25ce0760b0)','(94675634c2ef)')

Good backgrounder on %%lockres%% from @RemusRusanu in Lockres collision probability marker.

share|improve this answer
    
Thanks for the link and sql statement. It meant that I could re-validate the application logic and find the error – One locks id 5 and then 6. The other locks 6 and then 5. It is the same code so I will have to do a little more digging. At least now, I know what I am looking for. Can you explain why they are using Key locks? I was expecting RID locks? –  Thomas Ljungberg Kristensen Mar 20 '13 at 12:28
    
A RID would be used if the table were a heap. On a table with a clustered index, the clustered index key identifies the row (there is no RID) so is used instead. –  Mark Storey-Smith Mar 20 '13 at 12:41
    
Thanks for help! –  Thomas Ljungberg Kristensen Mar 20 '13 at 12:43
    
@Thomas does your code do things like if (select count) > 0 then update else insert? –  Aaron Bertrand Mar 20 '13 at 14:04
    
Nope. Mark provided enough help to identify the exact locking rows. This allowed us to go back an re-verify the code. For some reason (that we are working on) the same code will "walk and update" the id ascending and id descending. This was given us the deadlock. –  Thomas Ljungberg Kristensen Mar 21 '13 at 7:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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