Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Profiler Deadlock Graph - What to do?

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2012-09-20 : 16:45:09
I seem to be doing a fairly simple insert into one of our tables. In my load tests (10 concurrent users) I'm getting 75% deadlocks on what appears to be an index on the table. I don't know how to improve the code and the insert seems very simple. There is a trigger on the table but when I disable the trigger the deadlocks still happen. Not sure how to proceed...


Deadlock graph					<deadlock-list>
 <deadlock victim="process1b2d9f288">
  <process-list>
   <process id="process1b2d9f288" taskpriority="0" logused="4960" waitresource="KEY: 9:72057594795655168 (ffffffffffff)" waittime="804" ownerId="23117561" transactionguid="0xae1197df815c1e46b746994eb51787f0" transactionname="user_transaction" lasttranstarted="2012-09-20T14:35:59.433" XDES="0x803f5950" lockMode="RangeI-N" schedulerid="1" kpid="3624" status="suspended" spid="92" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-20T14:35:59.850" lastbatchcompleted="2012-09-20T14:35:59.850" clientapp=".Net SqlClient Data Provider" hostname="WEB01" hostpid="6220" loginname="Blatant" isolationlevel="serializable (4)" xactid="23117561" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="204" stmtend="664" sqlhandle="0x020000008d05c4060864c7b109b4f20dcab717dad8db4558">
INSERT INTO [dbo].[LearningPathSubscription]([StudentsID], [LearningPathID], [Status], [Progress], [AutomaticAssignment], [DateAdded], [Completed], [ReferenceNum], [BulkID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 decimal(7,4),@p4 int,@p5 datetime,@p6 datetime,@p7 varchar(8000),@p8 int)INSERT INTO [dbo].[LearningPathSubscription]([StudentsID], [LearningPathID], [Status], [Progress], [AutomaticAssignment], [DateAdded], [Completed], [ReferenceNum], [BulkID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]    </inputbuf>
   </process>
   <process id="process1b423f708" taskpriority="0" logused="5612" waitresource="KEY: 9:72057594795655168 (ffffffffffff)" waittime="803" ownerId="23117603" transactionguid="0x4efdd569afad41409939296f63662005" transactionname="user_transaction" lasttranstarted="2012-09-20T14:35:59.543" XDES="0x196d12e80" lockMode="RangeI-N" schedulerid="2" kpid="2052" status="suspended" spid="79" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-09-20T14:35:59.850" lastbatchcompleted="2012-09-20T14:35:59.850" clientapp=".Net SqlClient Data Provider" hostname="WEB01" hostpid="6220" loginname="Blatant" isolationlevel="serializable (4)" xactid="23117603" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="204" stmtend="664" sqlhandle="0x020000008d05c4060864c7b109b4f20dcab717dad8db4558">
INSERT INTO [dbo].[LearningPathSubscription]([StudentsID], [LearningPathID], [Status], [Progress], [AutomaticAssignment], [DateAdded], [Completed], [ReferenceNum], [BulkID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 int,@p2 int,@p3 decimal(7,4),@p4 int,@p5 datetime,@p6 datetime,@p7 varchar(8000),@p8 int)INSERT INTO [dbo].[LearningPathSubscription]([StudentsID], [LearningPathID], [Status], [Progress], [AutomaticAssignment], [DateAdded], [Completed], [ReferenceNum], [BulkID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594795655168" dbid="9" objectname="absorb.dbo.LearningPathSubscription" indexname="_dta_index_LearningPathSubscription_5_861962147__K2_K1_K3" id="lockfd836f80" mode="RangeS-S" associatedObjectId="72057594795655168">
    <owner-list>
     <owner id="process1b423f708" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1b2d9f288" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594795655168" dbid="9" objectname="absorb.dbo.LearningPathSubscription" indexname="_dta_index_LearningPathSubscription_5_861962147__K2_K1_K3" id="lockfd836f80" mode="RangeS-S" associatedObjectId="72057594795655168">
    <owner-list>
     <owner id="process1b2d9f288" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1b423f708" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
					sa			31	2012-09-20 14:36:00.653

chadmat
The Chadinator

1974 Posts
Posted - 2012-09-21 : 12:38:00
What is the table definition, and what indexes exist?

-Chad
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2012-09-25 : 16:35:40
It turns out the was a transaction running associated with a LINQ query that wasn't obvious.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site