CODEWITHSUNDEEP
sqlsql-serverdeadlock
ced
ced

Reputation: 81

SQL Server Deadlock on UPDATE

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:

<deadlock-list> 
   <deadlock victim="process3a0ac58"> 
      <process-list>
         <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"> 
             <executionStack> 
                 <frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x0200000008adf4202a2e77131e147fe8c50b173a5f8d5302"> UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 </frame> 
                 <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame>
             </executionStack> 
             <inputbuf> (@p0 int,@p1 int)UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 </inputbuf> 
         </process> 
         <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"> 
            <executionStack>
               <frame procname="adhoc" line="1" stmtstart="34" sqlhandle="0x0200000008adf4202a2e77131e147fe8c50b173a5f8d5302"> UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 </frame> 
               <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame> 
            </executionStack> 
            <inputbuf> (@p0 int,@p1 int)UPDATE [FreeAvailability] SET Resource_id = null WHERE Resource_id = @p0 AND Id = @p1 </inputbuf> 
         </process> 
      </process-list> 
      <resource-list> 
        <keylock hobtid="72057594078035968" dbid="9" objectname="SDN.Napoli.dbo.FreeAvailability" indexname="PK__FreeAvailability__3939548A" id="lock4bdb180" mode="X" associatedObjectId="72057594078035968">
            <owner-list> 
               <owner id="process3a28da8" mode="X"/> 
            </owner-list> 
            <waiter-list> 
               <waiter id="process3a0ac58" mode="U" requestType="wait"/> 
            </waiter-list> 
        </keylock> 
        <keylock hobtid="72057594078035968" dbid="9" objectname="SDN.Napoli.dbo.FreeAvailability" indexname="PK__FreeAvailability__3939548A" id="lock4c25680" mode="X" associatedObjectId="72057594078035968"> 
           <owner-list> 
              <owner id="process3a0ac58" mode="X"/> 
           </owner-list> 
           <waiter-list> 
              <waiter id="process3a28da8" mode="U" requestType="wait"/> 
           </waiter-list> 
        </keylock> 
      </resource-list> 
   </deadlock> 
</deadlock-list>

It seems that deadlock occur on the primary key, but how it is possible and how can i resolve this problem?

Thanks in advance

EDIT:

This is the table structure:

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]

I can't say the values passed by 2 threads cause i don't log that :(

Upvotes: 4

Views: 7034

Answers (1)

Roji P Thomas
Roji P Thomas

Reputation: 540

To identify the rows involved in this deadlock, run the query

SELECT id, %%lockres%% as LockResource FROM dbo.FreeAvailability WHERE %%lockres%% IN(‘(e100ae2e5d7f)’,'(d0006ab1ca37)’)

In this deadlock, Process process3a0ac58 is holding an X lock on row corresponding to resource (e100ae2e5d7f) and waiting for a U lock (for the read phase of the UPDATE) on the row corresponding to resource (d0006ab1ca37).

Meanwhile process process3a28da8 is holding an X lock on row corresponding to resource (d0006ab1ca37) and waiting for a U lock on (e100ae2e5d7f).

The deadlock trace wont tell you what statements acquired the lock. My guess is that you are performing multiple UPDATEs in the same transaction. Also in this case the UPDATEs are performed in the inverse order.

Two independent UPDATE statements using key level locks (due to the WHERE clause on PK) will NOT deadlock with each other. So this should be a case of classic cyclic deadlock.

Upvotes: 4

Related Questions