title: SQL Server Deadlock on UPDATE
body: <p>i have a problem with a deadlock that occurs in my application.
  The DB is an SQL server 2005 and the deadlock occur when 2 threads try to update the same table.
  I don't understand the situation and i hope someone can help me.
  here is the deadlock graph:</p>
  
  <pre><code>&lt;deadlock-list&gt; 
     &lt;deadlock victim="process3a0ac58"&gt; 
        &lt;process-list&gt;
           &lt;process id="process3a0ac58" taskpriority="0" logused="5048" waitresource="KEY: 9:72057594078035968 (e100ae2e5d7f)" waittime="4750" ownerId="22329947" transactionname="user_transaction" lasttranstarted="2012-07-20T08:53:33.440" XDES="0x24b429210" lockMode="U" schedulerid="1" kpid="1428" status="suspended" spid="57" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-07-20T08:53:34.753" lastbatchcompleted="2012-07-20T08:53:34.753" clientapp=".Net SqlClient Data Provider" hostname="VMDBSRVCRISPI" hostpid="4012" loginname="sa" isolationlevel="read uncommitted (1)" xactid="22329947" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; 
               &lt;executionStack&gt; 
                   &lt;frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x0200000008adf4202a2e77131e147fe8c50b173a5f8d5302"&gt; UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 &lt;/frame&gt; 
                   &lt;frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt; unknown &lt;/frame&gt;
               &lt;/executionStack&gt; 
               &lt;inputbuf&gt; (@p0 int,@p1 int)UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 &lt;/inputbuf&gt; 
           &lt;/process&gt; 
           &lt;process id="process3a28da8" taskpriority="0" logused="8720" waitresource="KEY: 9:72057594078035968 (d0006ab1ca37)" waittime="2734" ownerId="22329913" transactionname="user_transaction" lasttranstarted="2012-07-20T08:53:33.067" XDES="0x28dd4aa40" lockMode="U" schedulerid="4" kpid="3732" status="suspended" spid="58" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-07-20T08:53:36.770" lastbatchcompleted="2012-07-20T08:53:36.737" clientapp=".Net SqlClient Data Provider" hostname="VMDBSRVCRISPI" hostpid="4012" loginname="sa" isolationlevel="read uncommitted (1)" xactid="22329913" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"&gt; 
              &lt;executionStack&gt;
                 &lt;frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x0200000008adf4202a2e77131e147fe8c50b173a5f8d5302"&gt; UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 &lt;/frame&gt; 
                 &lt;frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt; unknown &lt;/frame&gt; 
              &lt;/executionStack&gt; 
              &lt;inputbuf&gt; (@p0 int,@p1 int)UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 &lt;/inputbuf&gt; 
           &lt;/process&gt; 
        &lt;/process-list&gt; 
        &lt;resource-list&gt; 
          &lt;keylock hobtid="72057594078035968" dbid="9" objectname="SDN.Napoli.dbo.FreeAvailability" indexname="PK__FreeAvailability__3939548A" id="lock4bdb180" mode="X" associatedObjectId="72057594078035968"&gt;
              &lt;owner-list&gt; 
                 &lt;owner id="process3a28da8" mode="X"/&gt; 
              &lt;/owner-list&gt; 
              &lt;waiter-list&gt; 
                 &lt;waiter id="process3a0ac58" mode="U" requestType="wait"/&gt; 
              &lt;/waiter-list&gt; 
          &lt;/keylock&gt; 
          &lt;keylock hobtid="72057594078035968" dbid="9" objectname="SDN.Napoli.dbo.FreeAvailability" indexname="PK__FreeAvailability__3939548A" id="lock4c25680" mode="X" associatedObjectId="72057594078035968"&gt; 
             &lt;owner-list&gt; 
                &lt;owner id="process3a0ac58" mode="X"/&gt; 
             &lt;/owner-list&gt; 
             &lt;waiter-list&gt; 
                &lt;waiter id="process3a28da8" mode="U" requestType="wait"/&gt; 
             &lt;/waiter-list&gt; 
          &lt;/keylock&gt; 
        &lt;/resource-list&gt; 
     &lt;/deadlock&gt; 
  &lt;/deadlock-list&gt;
  </code></pre>
  
  <p>It seems that deadlock occur on the primary key, but how it is possible and how can i resolve this problem?</p>
  
  <p>Thanks in advance</p>
  
  <p>EDIT:</p>
  
  <p>This is the table structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[FreeAvailability](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NULL,
      [StartTime] [datetime] NULL,
      [EndTime] [datetime] NULL,
      [BookOnlyIfRequired] [bit] NULL,
      [Free48HsBefore] [bit] NULL,
      [Private] [bit] NULL,
      [F48HBGroup] [bit] NULL,
      [Resource_id] [int] NULL,
      [Sedi_id] [int] NULL,
      [Skill_id] [int] NULL,
      [BOIRGroup_id] [int] NULL,
      [Private48HBGroup] [bit] NULL,
      [Free24HsBefore] [bit] NULL,
      [Free72HsBefore] [bit] NULL,
      [F24HBGroup] [bit] NULL,
      [F72HBGroup] [bit] NULL,
      [Private24HBGroup] [bit] NULL,
      [Private72HBGroup] [bit] 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]
  
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK4D396931200F9F6C] FOREIGN KEY([Skill_id])
  REFERENCES [dbo].[Skill] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK4D396931200F9F6C]
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK4D3969313F693A26] FOREIGN KEY([BOIRGroup_id])
  REFERENCES [dbo].[BOIRGroup] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK4D3969313F693A26]
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK4D396931C92BB494] FOREIGN KEY([Resource_id])
  REFERENCES [dbo].[Resource] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK4D396931C92BB494]
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK556546F95E61B626] FOREIGN KEY([BOIRGroup_id])
  REFERENCES [dbo].[BOIRGroup] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK556546F95E61B626]
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK556546F95ECA95DC] FOREIGN KEY([Skill_id])
  REFERENCES [dbo].[Skill] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK556546F95ECA95DC]
  GO
  ALTER TABLE [dbo].[FreeAvailability]  WITH CHECK ADD  CONSTRAINT [FK556546F9E6E3AAC4] FOREIGN KEY([Resource_id])
  REFERENCES [dbo].[Resource] ([Id])
  GO
  ALTER TABLE [dbo].[FreeAvailability] CHECK CONSTRAINT [FK556546F9E6E3AAC4]
  </code></pre>
  
  <p>I can't say the values passed by 2 threads cause i don't log that :(</p>