Databases
StackOverflow2013
Postsdbo
POSQL Server Deadlock on UPDATE
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Deadlock on UPDATE
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USced
    1. USced
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Deadlock on UPDATE
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server Deadlock on UPDATE
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPlease show us your table structure! What columns (and their datatypes) do you have? What kind of indexes do you have on that table? What values of `Resource_Id` and `ID` were your two threads updating?
      singulars
      1. POSQL Server Deadlock on UPDATE
      1. USmarc_s
    2. COThis will be a good starting point http://sqlindian.com/2012/07/06/sql-server-deadlocks-and-live-lockscommon-patterns/
      singulars
      1. POSQL Server Deadlock on UPDATE
      1. USMadhivanan
    3. COare these updates part of another transactions?means do you keep some kind of locks on some of the rows on the same table before update is started?Also,what kind of non clustered indexes do you have on the table?
      singulars
      1. POSQL Server Deadlock on UPDATE
      1. USGulli Meel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload